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