From: Tom Lane <tgl@sss.pgh.pa.us>
To: dougt@mugc.cc.monash.edu.au
Cc: jks@p1.selectacast.net
Cc: gary.stainburn@ringways.co.uk
Cc: pgsql-interfaces@postgreSQL.org
Subject: Re: [INTERFACES] locking on database updates
Date: Mon, 06 Dec 1999 23:25:52 -0500
Message-ID: <21885.944540752@sss.pgh.pa.us> (raw)
In-Reply-To: <199912070358.OAA26398@mugca.cc.monash.edu.au>
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
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-interfaces@postgresql.org
Cc: tgl@sss.pgh.pa.us, dougt@mugc.cc.monash.edu.au, jks@p1.selectacast.net, gary.stainburn@ringways.co.uk
Subject: Re: [INTERFACES] locking on database updates
In-Reply-To: <21885.944540752@sss.pgh.pa.us>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox