Prevent serial column in postgresql from generating duplicate value.

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

--

--

Get the Medium app

A button that says 'Download on the App Store', and if clicked it will lead you to the iOS App store
A button that says 'Get it on, Google Play', and if clicked it will lead you to the Google Play store