Postgres duplicate key value violates unique constraint for autogenerated column



  • Inserting a row is throwing unique constraint violation on a auto-generated column when running with a bash script

    psql -Atx "$DB_CONNECTION_STRING" -c "INSERT INTO my_table(mt_version) VALUES ('0.1.11')"
    
    ERROR:  duplicate key value violates unique constraint "my_table_version_pkey"
    DETAIL:  Key (puck_firmware_version_id)=(66) already exists.
    

    When I run with dbeaver, it adds the new row and generates the id with no issue my_table(mt_version) values ('0.1.2')



  • Someone must have inserted a row with an explicit value for id, overriding the generated default value. That row caused a collision with the automatically generated value from your INSERT statement.

    This had nothing to do with the client in use, and it is a coincidence that you could trigger the error from psql, but not from DBeaver.

    The best way to prevent such a situation is to define generated primary keys with GENERATED ALWAYS AS IDENTITY, so that it becomes more difficult to override the generated value.

    As a solution for your present problem, either delete the conflicting manually inserted rows or reset the sequence to a value higher than any existing id in the table.


Log in to reply
 


Suggested Topics

  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2