postgresql duplicate key violates unique constraint

by

This article explains that your sequence might be out of sync and that you have to manually bring it back in sync.

An excerpt from the article in case the URL changes:

If you get this message when trying to insert data into a PostgreSQL
database:

ERROR:  duplicate key violates unique constraint

That likely means that the primary key sequence in the table you’re
working with has somehow become out of sync, likely because of a mass
import process (or something along those lines). Call it a “bug by
design”, but it seems that you have to manually reset the a primary
key index after restoring from a dump file. At any rate, to see if
your values are out of sync, run these two commands:

SELECT MAX(the_primary_key) FROM the_table;   
SELECT nextval('the_primary_key_sequence');

If the first value is higher than the second value, your sequence is
out of sync. Back up your PG database (just in case), then run this command:

SELECT setval('the_primary_key_sequence', (SELECT MAX(the_primary_key) FROM the_table)+1);

That will set the sequence to the next available value that’s higher
than any existing primary key in the sequence.

More Related Contents:

  1. What to do with null values when modeling and normalizing?
  2. Postgres: INSERT if does not exist already
  3. postgres default timezone
  4. How to change PostgreSQL user password?
  5. How to get the count of current month Sunday’s in psql?
  6. quick random row selection in Postgres
  7. Restore a postgres backup file using the command line?
  8. Copy a table from one database to another in Postgres
  9. Can I automatically create a table in PostgreSQL from a csv file with headers?
  10. User defined variables in PostgreSQL
  11. PostgreSQL query very slow with limit 1
  12. How to add an auto-incrementing primary key to an existing table, in PostgreSQL?
  13. psql: FATAL: database “” does not exist
  14. Difference between timestamps with/without time zone in PostgreSQL
  15. Postgres FOR LOOP
  16. Storing Images in PostgreSQL
  17. Psql could not connect to server: No such file or directory, 5432 error?
  18. Strange behaviour in Postgresql
  19. Postgres: Distinct but only for one column
  20. In PostgreSQL, how to insert data with COPY command?
  21. Cursor based records in PostgreSQL
  22. Maximum characters in labels (table names, columns etc)
  23. Docker – Postgres and pgAdmin 4 : Connection refused
  24. Configuration parameter work_mem in PostgreSQL on Linux
  25. How to work with PGpoint for Geolocation using PostgreSQL?
  26. How to delete an enum type value in postgres?
  27. Why unsigned integer is not available in PostgreSQL?
  28. now() default values are all showing same timestamp
  29. What’s the difference between pg_table_size, pg_relation_size & pg_total_relation_size? (PostgreSQL)
  30. JPA and PostgreSQL with GenerationType.IDENTITY

Leave a Comment