I discovered this technique back in my Oracle days but it dropped out of my toolbox when I switched to PostgreSQL. Recently I had reason to revisit it, so I thought I should share it (trivial though it is). PostgreSQL makes it easy to generate unique ids. Just declare the column as SERIAL (or IDENTITY GENERATED in newer versions). One problem with this approach is that the ids are only unique within the table, not across the database. If you accidentally use an id from one table to access another table, chances are that a row with that id will actually exist - it just won't be the row you wanted to access. So the solution is to use a single sequence for all the id columns. Instead of create table t1( id serial primary key, data text ); create table t2( id serial primary key, t1 int references t1, data text ); you write create sequence global_seq; create table t1( id int primary key default nextval('global_seq'), data text ); create table t2( id int primary key default nextval('global_seq'), t1 int references t1, data text ); Then you insert data just like you normally would with INSERT ... RETURNING id. The difference is just that if you accidentally do an UPDATE or delete wit an id from another table, it won't do anything. Possible drawbacks: * The ids will grow faster, and they will be large even on small tables. It may be a bit irritating if you have a table with just 5 rows and the ids are 5, 6, 7, 12654, 345953. * Bottleneck? Using a single sequence was said to be a performance bottleneck in Oracle. I didn't notice a performance difference then and I doubt it would be one in PostgreSQL, but if in doubt, measure! * Doesn't work with IDENTIY - those columns always use implicit sequences. * currval() is pretty useless with a global sequence. But I basically never use that anyway. hp -- _ | Peter J. Holzer | Story must make more sense than reality. |_|_) | | | | | hjp@xxxxxx | -- Charles Stross, "Creative writing __/ | http://www.hjp.at/ | challenge!"
Attachment:
signature.asc
Description: PGP signature