From: | george young <gry(at)ll(dot)mit(dot)edu> |
---|---|
To: | pgsql-novice(at)postgresql(dot)org |
Subject: | Re: How to append records into a file which has serial |
Date: | 2006-04-11 14:42:47 |
Message-ID: | 20060411104247.5975adad.gry@ll.mit.edu |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-novice |
On Sat, 8 Apr 2006 23:27:07 -0700
James Long <james_mapson(at)umpquanet(dot)com> threw this fish to the penguins:
> > This worked for me:
> >
> > newschm3=> create table foo(x text,y text,a serial);
>
> I think it just dawned on me -- I have to put the serial ID at
> the end of the table structure, don't I? So that the fields
> correspond one-to-one, until the temp runs out of columns
> where the master table has the SERIAL field.
Here's a somewhat cleaner method (and faster if that is an issue):
newschm3=> create temp table bar as select * from foo where y='red';
SELECT
-- Now find the proper name of the sequence underlying column 'a':
newschm3=> \d+ foo
Table "public.foo"
Column | Type | Modifiers | Description
--------+---------+-------------------------------------------------+-------------
x | text | |
y | text | |
a | integer | not null default nextval('foo_a_seq'::regclass) |
-- It's *almost* always safe to assume serial column 'a' of table
-- 'foo' uses foo_a_seq but it's possible for it to be something else,
-- e.g. if the table or column has been renamed, or if the
-- table name+column name is very long.
newschm3=> update bar set a=nextval('foo_a_seq') from foo;
UPDATE 1
newschm3=> select * from foo;
x | y | a
------+-------+---
the | red | 1
rain | green | 2
in | blue | 3
the | red | 4
[see http://www.postgresql.org/docs/8.1/interactive/functions-sequence.html]
-- George Young
--
"Are the gods not just?" "Oh no, child.
What would become of us if they were?" (CSL)
From | Date | Subject | |
---|---|---|---|
Next Message | Chris Browne | 2006-04-11 16:27:04 | Re: PostgreSQL a slow DB? |
Previous Message | Tom Lane | 2006-04-11 14:09:43 | Re: Postgres Config/Tuning problem |