Is it possible to condense the primary key/serial?

Question

I have a database where everything is linked with foreign keys, so the Postgres knows exactly how the database is laid out.

Let's say I have Table1 and Table2.

Table1 has 3 fields: RID, table2_rid, data.

So table1.table2_rid references table2.RID and this is expressed with a foreign key. In both the RID field is the primary key and is a serial type.

What I would like to know is how to "condense" the primary keys? Say you add 5 records and deleted record number 3. Your primary keys would look like

Well, how do I get to update everywhere so that the primary key (and corresponding foreign keys) can be condensed into

Is it important to keep everything condensed? If you have 1000 records in your database and you remove record 1 then you have at least 2 extra updates to run. — thetaiko, Feb 19 '10 at 19:08
I'm talking more about something you do on a table that has a lot of inserts/deletes per day and doing this as like a monthly maintenance task or something. — Earlz, Feb 19 '10 at 19:09
Putting words in scare quotes does not clarify the idiosyncratic meaning that you don't make clear by actually saying what you mean. Please clarify via edits, not comments. Use enough words, sentences & references to parts of examples to clearly & fully say what you mean. [ask] [Help] [mre] — philipxy, Aug 22 '22 at 13:25

score 2 · Accepted Answer · answered Feb 19 '10 at 19:15

2

It's best if a primary key never changes: Renumbering them is a PITA.

If you need an ID for humans, one that has no gaps, A. Elein Mustain shows how to create a gapless sequence.

answered Feb 19 '10 at 19:15

Wayne Conrad

103,207
26
155
191

Well I'm thinking ahead cause we have a table or two that very commonly has transactions rolled back(which increment the primary key's value due to how `serial` works) and has lots of inserts and updates and I would just like to know if in a year or two we begin to face a roll-over problem with 32 bit, if there will be a way to combat it. – Earlz Feb 19 '10 at 19:20
2

2^31 is one insert per second for *68 years*. But if 32 bits might not be enough, then use serial8/bigserial instead of plain serial and you'll be able to do 1000 inserts per second for 0.1 terrayears. – Wayne Conrad Feb 19 '10 at 20:06

score 1 · Answer 2 · edited May 23 '17 at 12:18

You probably don't want to do this generally, as gapless sequences are problematic for performance.

If you want to do it as a cleanup step later you can use the rank() window function to achieve the desired effect.

CREATE TABLE table1 (id integer primary key);
INSERT INTO table1 values (1),(2),(4),(5);

CREATE TABLE table2 (
  id serial primary key,
  rid integer references table1(id) ON UPDATE CASCADE
);

insert into table2 (rid) values (1),(1),(4),(4),(4),(5);



UPDATE table1 
  SET id = gapless_id
FROM (
  SELECT *, row_number() OVER () FROM table1
) AS x(old_id, gapless_id)
WHERE id = x.old_id;

Result:

regress=# select * from table1 ;
 id 
----
  1
  2
  3
  4
(4 rows)

If your FK's aren't ON UPDATE CASCADE you can ALTER TABLE to make them so. This will be quite slow though, especially if there are no indexes on the foreign keys. A faster approach is to do the change in two passes:

Begin a transaction
LOCK TABLE table1;
Add a new_id column to table1 and populate it with the new IDs using row_number() as shown above
Drop foreign key constraints referring to table1(id)
Update all foreign keys to refer to the values in new_id
drop id in table1
rename the new_id column of table1 to id
re-create foreign key constraints
commit

score 1 · Answer 3 · answered Feb 19 '10 at 19:12

Look into on update cascade and on delete cascade.

create table table_1 (
    id integer
        primary key,
    name char(30)
);
create table table_2 (
    id integer
        primary key,
    table_1_id integer
        references table_1
        on update cascade
        on delete cascade,
    detail char(30)
);

score 1 · Answer 4 · answered Feb 19 '10 at 20:44

1

Except for very rare scenarios, gaps in the PK sequence are just right, to intent to get rid of them is a bad idea.

answered Feb 19 '10 at 20:44

leonbloy

73,180
20
142
190

Is it possible to condense the primary key/serial?

4 Answers4

Linked

Related