0

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.

  • 2
    I am wondering if you use OID in a rather unorthodox sense here. See postgresql.org/docs/9.3/static/… and postgresql.org/docs/9.3/static/sql-createtable.html for details. – dezso Nov 25 '14 at 14:49
  • You're right. I've added a warning on the question, but feel free to suggest a better term. – Skippy le Grand Gourou Nov 25 '14 at 14:58
  • "Surrogate" ids. – ypercubeᵀᴹ Nov 25 '14 at 15:08
  • And what do you mean with "no SQL constraints or whatever"? You do have primary key constraints. Do you mean no foreign key, unique and check constraints? – ypercubeᵀᴹ Nov 25 '14 at 15:09
  • 2
    From these comments I think I can infer the answer to the first part of my question is "no, there's no automatic way", since surrogate keys are "manually" implemented and not assigned by the DB itself. The only remaining part of the question is thus "is it worth "leveling" surrogate keys (i.e. avoiding "holes") ? – Skippy le Grand Gourou Nov 25 '14 at 15:55
2

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:

| improve this answer | |
  • Thanks. Aside intellectual satisfation I was also concerned about performances issues but I understand from your answer I shouldn't — I guess it's a matter of indexes, no matter what the IDs ordering is ? I'll forget about gaps as you suggest. – Skippy le Grand Gourou Nov 26 '14 at 8:38
  • As for your proposed procedure, I wasn't aware of the REFERENCES keyword to begin with, so I don't use ON UPDATE CASCADE. I'll know better from now on. ;) – Skippy le Grand Gourou Nov 26 '14 at 8:39
  • 1
    @SkippyleGrandGourou: That's called a Foreign Key. Be sure to read the chapter in the manual. And I take it you know about the serial pseudo data type to generate surrogate keys? – Erwin Brandstetter Nov 26 '14 at 9:01
  • Yes, I already use the serial type. Thanks for pointing foreign keys, I'll do it more properly now. – Skippy le Grand Gourou Nov 27 '14 at 11:16

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.