Reset a PostgreSQL sequence and update column values

by Florent Lebreton (fle)
in quick tips

tagged postgresql

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.

  1. 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');
  1. Restart sequence to 1
ALTER SEQUENCE property_gallery_id_seq RESTART WITH 1;
  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!

__fle__

Comments !