Re: How to append records into a file which has serial

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)