Prevent serial column in postgresql from generating duplicate value.

Himanshu Pratap
Feb 26, 2022

When you use liquibase’s instruction autoIncrement =”true”, for generation of primary key id, it generates serial column for PostgreSQL.

For serial column PostgreSQL will create a sequence with a name like tablename_colname_seq. Default column values will be assigned from this sequence. But when you explicitly insert a value into serial column, it doesn’t affect sequence generator, and its next value will not change. So it can generate a duplicate value.

To prevent this after you inserted explicit values you need to change the current value of a sequence generator either with ALTER SEQUENCE statement or with setval() function, e.g.:

ALTER SEQUENCE tablename_colname_seq RESTART WITH 52;
SELECT setval('tablename_colname_seq', (SELECT max(colname) FROM tablename));

Resources:

https://stackoverflow.com/questions/44744365/liquibase-postgresql-spring-jpa-id-auto-increment-issue

--

--