On Mon, Oct 04, 2004 at 10:56:20PM -0500, C. Bensend wrote:
>
> I have a table with the following columns:
>
> dns1_ptr | inet | default '0.0.0.0'::inet
> dns2_ptr | inet | default '0.0.0.0'::inet
> dns3_ptr | inet | default '0.0.0.0'::inet
> dns4_ptr | inet | default '0.0.0.0'::inet
> dns5_ptr | inet | default '0.0.0.0'::inet
> dns6_ptr | inet | default '0.0.0.0'::inet
>
> (yes, I know, I didn't know any better)
>
> It is being replaced by:
>
> dns_ptr | inet[] | default ...etc
>
> (hopefully this is more intelligent)
How does dns_ptr relate to other data? Depending on what you're
doing, other ways of organizing your tables might also make sense.
Here's an example:
CREATE TABLE hosts ( id SERIAL PRIMARY KEY, hostname VARCHAR(255) NOT NULL UNIQUE
);
CREATE TABLE dns_servers ( id SERIAL PRIMARY KEY, ipaddr INET NOT NULL UNIQUE
);
CREATE TABLE host_dns ( hostid INTEGER REFERENCES hosts, dnsid INTEGER REFERENCES dns_servers, UNIQUE(hostid,
dnsid)
);
> Now, as I migrate the data from the old table to the new, is there
> any way to just do the typical 'INSERT INTO blah SELECT a,b,c FROM blah2'
> type of thing? ie,
>
> INSERT INTO new_table ( dns_ptr ) SELECT dns1_ptr, dns2_ptr .. FROM
> old_table;
If none of the dnsX_ptr values can be NULL, then try this:
INSERT INTO new_table (dns_ptr) SELECT ARRAY[dns1_ptr, dns2_ptr, dns3_ptr, dns4_ptr, dns5_ptr, dns6_ptr] FROM
old_table;
--
Michael Fuhr
http://www.fuhr.org/~mfuhr/
