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
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
idin the table.