Reassigning surrogate keys?

Question

I have a few tables with surrogate keys (SERIAL PRIMARY KEYs unique to each tables). These tables are used by other tables, with references to these keys. My usage of these is quite simple :

select * from table_a;
 oid | data
-----+------
  1  |  foo
  2  |  bar

select * from table_c;
 a_id | b_id |   misc
------+------+----------
  1   |   1  | whatever
  2   |   1  | blah

There are no SQL constraints or whatever.

I'm about to make some large modifications to my DB (e.g. addition and merging of tables), so I'm wondering if there is an easy or automatic way to reassign OIDs in related tables ? [Edit : I guess there is no reason such an automatic way should exist, since those keys are assigned manually and not by the DB itself.]

I don't like the idea of having "holes" in my surrogate keys so I'm thinking about "leveling" them (probably using temporary tables with corresponding new/old keys), but is it worth the trouble ?

I'm using PostgreSQL 9.3.5.

Answer 1

There is no definite answer to whether "it is worth the trouble". That's really a matter of taste and style and the total picture we don't have. If in doubt, just don't bother, that's typically the best course of action.

For small, simple tables where you rarely delete rows, it might be convenient for human users to remove gaps from primary keys. However, you should never rely on the absence of gaps if it is at all possible to update IDs or delete rows.

For bigger tables or with ongoing write activity, I wouldn't bother to begin with.

That said, here is a simple procedure, as long as you don't have massive concurrent access to the tables (in which case I also wouldn't bother):

If all your foreign keys are defined ON UPDATE CASCADE, the changes propagate to depending columns.

Do not use "oid" as identifier. This is traditionally used for OIDs and you don't want to cause confusion. Using a sane name for the PK column instead.

To avoid unique violations in intermediary states, you must make way for new IDs. Assuming all your IDs are positive integer numbers:

BEGIN;
LOCK tbl;

-- flip IDs to negative
UPDATE table_a SET table_a_id = table_a_id * -1;

-- assign new gap-less numbers
UPDATE table_a a
SET    table_a_id = u.rn
FROM  (
   SELECT table_a_id, row_number() OVER (ORDER BY table_a_id DESC) AS rn
   FROM   table_a
   ) u
WHERE a.table_a_id = u.table_a_id;

COMMIT;

The (often better) alternative is to just forget about those gaps and use a VIEW with row_number() where you really need gap-less numbers.

Related later answer with another alternative:

• Compacting a sequence in PostgreSQL