Re: How to perform an identical insert?

Heiko Klein <Heiko(dot)Klein(at)met(dot)no> writes:
> have a look at this:
> http://www.ca.postgresql.org/docs/aw_pgsql_book/node75.html

> The postgres serial type is nothing more than a primary key with a
> default-value as nextval('..') from a sequence and a trigger.

> If you make this setup manually, you can load the data before attaching
> the trigger and it should work. (Though I haven't tried this.)

You don't even need to do it manually; there is nothing wrong at all
with inserting into a serial column. For example:

create table foo (key serial, other-stuff);

insert into foo values (1, ...);
insert into foo values (2, ...);
insert into foo values (3, ...);
...

As long as you don't try to insert any duplicate key values you won't
get any errors. When you're done inserting, you need to adjust the
serial column's sequence generator to pick up where you left off,
eg with
select setval('foo_key_seq', (select max(key) from foo));
and then you're good to go on normal operations where you don't specify
a key value during insertions.

This is essentially the same strategy that pg_dump uses to dump and
restore tables containing serial columns.

regards, tom lane