Received: from sss.sss.pgh.pa.us (sss.pgh.pa.us [209.114.166.2])
	by hub.org (8.9.3/8.9.3) with ESMTP id XAA56904
	for <pgsql-interfaces@postgreSQL.org>;
	Mon, 6 Dec 1999 23:27:32 -0500 (EST)
	(envelope-from tgl@sss.pgh.pa.us)
Received: from sss.sss.pgh.pa.us (localhost [127.0.0.1])
	by sss.sss.pgh.pa.us (8.9.1/8.9.1) with ESMTP id XAA21887;
	Mon, 6 Dec 1999 23:25:52 -0500 (EST)
To: dougt@mugc.cc.monash.edu.au
cc: jks@p1.selectacast.net (Joseph Shraibman), gary.stainburn@ringways.co.uk,
	pgsql-interfaces@postgreSQL.org
Subject: Re: [INTERFACES] locking on database updates 
In-reply-to: Your message of Tue, 7 Dec 1999 14:58:43 +1100 (EST)
	<199912070358.OAA26398@mugca.cc.monash.edu.au>
Date: Mon, 06 Dec 1999 23:25:52 -0500
Message-ID: <21885.944540752@sss.pgh.pa.us>
From: Tom Lane <tgl@sss.pgh.pa.us>

Douglas Thomson <dougt@mugc.cc.monash.edu.au> writes:
> Joseph Shraibman <jks@p1.selectacast.net> writes:
>> Can you give an example of sql that creates a table with that?

A couple footnotes on Doug's fine example:

> I created my sequence using code like:
>     CREATE SEQUENCE name_map_seq START 1
> and then used it as the default in another table:
>     CREATE TABLE name_map (
>         id INT DEFAULT nextval('name_map_seq'),
>         name TEXT,
>         info TEXT
>     )
> I also added a unique index to avoid possible mistakes:
>     CREATE UNIQUE INDEX name_map_unq ON name_map (id)

Declaring a column as "SERIAL" is a handy shortcut for exactly these
declarations: a sequence, a default value of nextval('sequence'), and
a unique index on the column.  (Plus a NOT NULL constraint, which you
might perhaps not want.)  You can reach in and inspect/modify the
sequence object for a SERIAL column just as if you'd made the sequence
by hand.

> On the other hand, if I need to rebuild a table using the same id
> values as before, I can simply provide a value explicitly, and then
> the default is ignored:
>     INSERT INTO name_map (id, name, info) VALUES (24, 'name', 'info')

Right.  Dumping and restoring the table with COPY commands works the
same way.  In fact, if you dump the database with pg_dump, you'll find
that the resulting script not only restores all the values of the "id"
column via COPY, but also recreates the current state of the sequence
object.

			regards, tom lane