Re: Issue Report: Unique Constraint Violation Error in PostgreSQL

On Wed, 12 Jun 2024 at 11:33, sing_hijo@outlook.com
<sing_hijo@outlook.com> wrote:
...
> Problem Description:
> I encountered a unique constraint violation error when attempting to insert data into a table.

You forced a constraint violation, it is a misunderstanding, not a bug:

> CREATE TABLE test_table (
>     id bigserial PRIMARY KEY,
This means 1.- Column gets a default from a sequence + 2.- column is a
primary key.
>     name varchar
> );
...
> INSERT INTO test_table (name) VALUES ('test1, no insert id');
- This is getting the default value from the sequence ( normally 1 ).
> INSERT INTO test_table VALUES (2, 'test2, insert id');
- This inserts 2, the sequence is not consulted.
> INSERT INTO test_table (name) VALUES ('test3, no insert id');
- And this is asking to insert the default sequence value, which is 2
because you used 1 above.
> Result:
> Error message: [23505] ERROR: duplicate key value violates unique constraint "test_table_pkey" Detail: Key (id)=(2) already exists.

Yep, your second stament is the same as inserting an explicit 2. So
dupe key is expected behaviour. Bug will be not getting it.

> Expected Result:
> I expected the data to be inserted successfully without encountering a unique constraint violation.
Wrong expectation. Sequences do not magically notice you have stomped
over their values. Someone posted a soluction if you need to do
something like this ( it is usual in bulk loading, you load explicit
values, then calculate max used value and set it as last value for the
sequence ).

Francisco Olarte.