Reassigning surrogate keys?
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