[lug] (OT) postgresql

[Bryan Field-Elliot]

Best practice in PostgreSQL is to use a sequence, select it's "next
value" FIRST, and then use this value for your primary key inserts and
your foreign key inserts, etc.

So if I have the following:

create sequence my_sequencer;
create table authors (id int primary key, name text);
create table books (id int primary key, name text, id_author int);

To insert a new author and two books,

[ pseudocode follows ]

new_author_id = "select nextval('my_sequencer')"
insert into authors (id, name) values (new_author_id, 'john doe')
insert into books (id, name, id_author) values (select
nextval('my_sequencer'), 'cooking with rocks', new_author_id);
insert into books (id, name, id_author) values (select
nextval('my_sequencer'), 'cooking with shrubbery', new_author_id);

The key points being, since you're going to need your "new author id"
more than once, then just get it up front and apply it to all queries
where it's needed. In the case of the "new book" inserts, you don't
really need to retrieve the newly-assigned primary key, so I don't
bother retrieving it, I just let PostgreSQL create one and insert it
into the right column.

I also combined my sequencers into one ("my_sequencer") even though it's
used on multiple tables. This is fine but some people like to have
separate sequencers for each table.

Lastly, this is totally atomic, thread-safe, multi-user-safe, etc,
either with, or without, a transaction boundary. It's the best way to do
it.

Alternatives, such as letting the database assign the new primary key
and then trying to "get it back later", are prone to errors as others
have mentioned.

Bryan


Best practice in PostgreSQL is not to use any kind of "auto incrementing
default value" and fetching it after each insert. Rather, 
-------------- next part --------------
An HTML attachment was scrubbed...
URL: <http://lists.lug.boulder.co.us/pipermail/lug/attachments/20021111/78df1b42/attachment.html>