pg.ddx.io pgsql-sql@postgresql.org mailing list archive
help / color / mirror / Atom feedStuffing six separate columns into a single array?
3+ messages / 2 participants
[nested] [flat]
* Stuffing six separate columns into a single array?
@ 2004-10-05 03:56 C. Bensend <benny@bennyvision.com>
0 siblings, 1 reply; 3+ messages in thread
From: C. Bensend @ 2004-10-05 03:56 UTC (permalink / raw)
To: pgsql-sql
Hey folks,
I'm in the middle of a database design update, mostly to undo all of
the stupid things I did in version 0.01. :) God knows I made enough
of them...
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)
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;
I can't seem to get the CASTing right on the SELECT statement, but
perhaps it's not even possible. It would be nice to be able to do
this so I don't have to write yet another perl script. I thought a
'CAST( '{dns1_ptr, dns2_ptr}' AS inet[] )' would work, but it complained
about the input syntax on dns1_ptr. I'm learning as I go, so maybe
that's not even close to being valid.
Any help/advice would be greatly appreciated,
Benny
--
"Even if a man chops off your hand with a sword, you still have two nice,
sharp bones to stick in his eyes."
-- .sig on Slashdot
^ permalink raw reply [nested|flat] 3+ messages in thread
* Re: Stuffing six separate columns into a single array?
@ 2004-10-05 07:04 Michael Fuhr <mike@fuhr.org>
parent: C. Bensend <benny@bennyvision.com>
0 siblings, 1 reply; 3+ messages in thread
From: Michael Fuhr @ 2004-10-05 07:04 UTC (permalink / raw)
To: C. Bensend <benny@bennyvision.com>; +Cc: pgsql-sql
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/
^ permalink raw reply [nested|flat] 3+ messages in thread
* Re: Stuffing six separate columns into a single array?
@ 2004-10-05 13:26 C. Bensend <benny@bennyvision.com>
parent: Michael Fuhr <mike@fuhr.org>
0 siblings, 0 replies; 3+ messages in thread
From: C. Bensend @ 2004-10-05 13:26 UTC (permalink / raw)
To: pgsql-sql
> How does dns_ptr relate to other data? Depending on what you're
> doing, other ways of organizing your tables might also make sense.
These are actually DNS servers authoritive for a domain that is stored
in a VARCHAR() in the same table.
After sleeping on it, I think using an array is indeed not the right
choice. I think splitting the nameservers off into their own table
is probably smarter for what I want to do.
Thanks, Michael, for the mental boot to the head. :)
Benny
--
"Even if a man chops off your hand with a sword, you still have two nice,
sharp bones to stick in his eyes."
-- .sig on Slashdot
^ permalink raw reply [nested|flat] 3+ messages in thread
end of thread, other threads:[~2004-10-05 13:26 UTC | newest]
Thread overview: 3+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2004-10-05 03:56 Stuffing six separate columns into a single array? C. Bensend <benny@bennyvision.com>
2004-10-05 07:04 ` Michael Fuhr <mike@fuhr.org>
2004-10-05 13:26 ` C. Bensend <benny@bennyvision.com>
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