Using a single sequence for all tables - Mailing list pgsql-general

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@hjp.at         |    -- Charles Stross, "Creative writing
__/   | http://www.hjp.at/ |       challenge!"