Date: Tue, 5 Oct 2004 01:04:04 -0600
From: Michael Fuhr <mike@fuhr.org>
To: "C. Bensend" <benny@bennyvision.com>
Cc: pgsql-sql@postgresql.org
Subject: Re: Stuffing six separate columns into a single array?
Message-ID: <20041005070404.GA44500@winnie.fuhr.org>
References: <64789.63.227.74.41.1096948602.squirrel@63.227.74.41>
Mime-Version: 1.0
Content-Type: text/plain; charset=us-ascii
Content-Disposition: inline
In-Reply-To: <64789.63.227.74.41.1096948602.squirrel@63.227.74.41>

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/