Tadek’s Blog

It’s quite often the case that in a normalized database there is a one-to-many relation that are being populated in a following fashin: loop: - one insert to “one” relation - multiple inserts to “many” relations.

We want to use autoincremented types like SERIAL for IDs linking these two relations and avoid unnecessary queries.

create table one( one_id serial primary_key, something….); create table many(many-id serial primary key, one_id references one(one_id) default currval(’one_one_id_seq’), something…);

And inserting like these: insert into one(something) values (some_values); insert into many(something) values(some_values1); insert into many(something) values(some_values2); …

If there is no string one-one, many-many pattern, you can still get the last value of the sequence by calling select currval(’one_one_id_seq’); and stroing it somewhere.

This works well in Postgres, but other databases have similar capabilities. BTW: currval() is stored per session, so there is no threat that the sequences get screwed with concurrent access.

This entry was posted on Friday, October 7th, 2005 at 11:42 am and is filed under Databases, Hacking. You can follow any responses to this entry through the RSS 2.0 feed. You can leave a response, or trackback from your own site.