Re: Stuffing six separate columns into a single array?

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/