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

On Sat, 8 Apr 2006 23:27:07 -0700
James Long <james_mapson@umpquanet.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)