I have my Postgres database which I used hibernate sequence to generate ids. But the sequence was common for all tables. But now my plan is to have a separate sequence for each table. Bow the what I would like to use a command to pack the ids continuous for a table, but without breaking any of the references.
Asked
Active
Viewed 121 times
0
-
Why would you need to "pack" id fields of the tables? – Ihor Romanchenko Nov 19 '13 at 10:13
-
the old database contains ids generated with skips. Which i dont want to happen any more. And since the new ids pack them selfs nicely i would like to change old ids as well. – dinesh707 Nov 19 '13 at 10:26
-
If you are using standard sequences to generate ids you will still have gaps in them. – Ihor Romanchenko Nov 19 '13 at 13:53
1 Answers
0
You'll have to make sure that all of your FOREIGN KEY references have ON UPDATE CASCADE (to add it: How to add "on delete cascade" constraints? ), then you can use:
UPDATE mytable AS m
SET id = n.rank
FROM (SELECT id, rank() OVER (ORDER BY id) FROM mytable) AS n
WHERE m.id = n.id
Which will give you sequential IDs with the same order as the original ids:
> SELECT * FROM mytable ORDER BY id;
id | name
1 | Alex
5 | David
10 | Jane
> UPDATE …;
…
> SELECT * FROM mytable ORDER BY id;
id | name
1 | Alex
2 | David
3 | Jane
Community
- 1
- 1
David Wolever
- 148,955
- 89
- 346
- 502
-
1There might be some tables, that are referencing other `id` fields without a proper `FOREIGN KEY`. – Ihor Romanchenko Nov 19 '13 at 10:14
-
There's no general solution to that, unfortunately — the only thing that can be done there is considering each column that contains an ad-hoc reference to another column and decide how to fix each one up on a case-by-case basis. – David Wolever Nov 19 '13 at 19:10
-
You may need to update your sequences after doing a mass update on your keys http://stackoverflow.com/questions/244243/how-to-reset-postgres-primary-key-sequence-when-it-falls-out-of-sync/13308052 – nclu Dec 14 '15 at 18:53