Re: [INTERFACES] locking on database updates

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