Reset a PostgreSQL sequence and update column values
source link: https://fle.github.io/reset-a-postgresql-sequence-and-recompute-column-values.html
Go to the source link to view the article. You can view the picture content, updated content and better typesetting reading experience. If the link is broken, please click the button below to view the snapshot at that time.
Reset a PostgreSQL sequence and update column values
Yesterday, I understood that I had broken a sequence of an auto-increment column in my PostgreSQL database. This quick tip will show you how to reset the sequence, restart it to a specific value, and recreate all values of the column
Example
I have a table which stores estate properties. Each property is associated to list of pictures (gallery). A joint table does this association
db=> \d weather
Table « public.property_pictures »
Colonne | Type | Modificateurs
-------------+---------+-------------------------------------------------------------------
id | integer | non NULL default, nextval('property_gallery_id_seq'::regclass)
property_id | integer | non NULL
picture_id | integer | non NULL
My property_gallery_id_seq sequence is dizzy and generates conflict on the id values.
Solution
Here is a solution.
- Set temporary values intentionally far of existing values to avoid conflicts when we recompute the column:
UPDATE property_pictures SET id=10000+nextval('property_gallery_id_seq');
- Restart sequence to 1
ALTER SEQUENCE property_gallery_id_seq RESTART WITH 1;
- Rewrite all column values
UPDATE property_pictures SET id=nextval('property_gallery_id_seq');
Note: Obviously, be very careful if the column is used a foreign key ... Also, do not forget to search WHY your sequence has been broken anyway ...
This solution is strongly inspired by this stackoverflow post
I hope this trick can save you some time too!
Recommend
About Joyk
Aggregate valuable and interesting links.
Joyk means Joy of geeK