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.
In my case, it was because DMS had already populated some of the tables of the schema that I was migrating and failed midway due to another error. After fixing the error and restarting the task it restarted from the beginning and the migration ran into a collision with the previously identical tabes that were populated in the first run. Dropping the schema and restarting the task resolved the error
