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?
Date: Tue, 5 Oct 2004 01:04:04 -0600
Message-ID: <20041005070404.GA44500@winnie.fuhr.org> (raw)
In-Reply-To: <64789.63.227.74.41.1096948602.squirrel@63.227.74.41>
References: <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/
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-sql@postgresql.org
Cc: mike@fuhr.org, benny@bennyvision.com
Subject: Re: Stuffing six separate columns into a single array?
In-Reply-To: <20041005070404.GA44500@winnie.fuhr.org>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox