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


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.


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!


Last posts

  1. Easy Webmapping with django-leaflet & django-geojson

    tags: djangowebmappingleafletgeojson

  2. Detect value changes between lines with PostgreSQL

    tags: postgresql

  3. An efficient GIT workflow for mid/long term projects

    tags: git

  4. GIT tip : A simple .gitconfig file

    tags: git

  5. Combine LDAP and classical authentication in django

    tags: djangoldap