Thread: Issue Report: Unique Constraint Violation Error in PostgreSQL

Issue Report: Unique Constraint Violation Error in PostgreSQL

From

"sing_hijo@outlook.com"

Date:

12 June, 11:55:23

Dear PostgreSQL Support Team,

I am writing to report an issue I encountered while working with the PostgreSQL database. Below are the details of the problem:

Problem Description:
I encountered a unique constraint violation error when attempting to insert data into a table.

Steps to Reproduce:

Created the table test_table:

CREATE TABLE test_table (    id bigserial PRIMARY KEY,    name varchar
);
ALTER TABLE test_table OWNER TO postgres;

Inserted data into the table:

INSERT INTO test_table (name) VALUES ('test1, no insert id');
INSERT INTO test_table VALUES (2, 'test2, insert id');
INSERT INTO test_table (name) VALUES ('test3, no insert id');

Result:
Error message: [23505] ERROR: duplicate key value violates unique constraint "test_table_pkey" Detail: Key (id)=(2) already exists.

Expected Result:
I expected the data to be inserted successfully without encountering a unique constraint violation.

Environment Information:

PostgreSQL Version:PostgreSQL 14.1 (Debian 14.1-1.pgdg110+1) on x86_64-pc-linux-gnu, compiled by gcc (Debian 10.2.1-6) 10.2.1 20210110, 64-bit
Platform Information: Ubuntu-24.04 Linux sin 5.15.146.1-microsoft-standard-WSL2 #1 SMP Thu Jan 11 04:09:03 UTC 2024 x86_64 x86_64 x86_64 GNU/Linux

Reporting Method:
I am reporting this issue to seek assistance in resolving the unique constraint violation error. Please advise on the appropriate steps to address this issue.

Thank you for your attention to this matter. I look forward to your guidance on resolving this issue.

Best regards,

sing_hijo@outlook.com

Re: Issue Report: Unique Constraint Violation Error in PostgreSQL

From

Muhammad Ikram

Date:

12 June, 12:59:05

Hi Sing_hijo,

Could you try this

SELECT setval(pg_get_serial_sequence('test_table', 'id'), (SELECT MAX(id) FROM test_table));

INSERT INTO test_table (name) VALUES ('test3, no insert id');

SELECT * FROM test_table;
setval
--------

(1 row)

INSERT 0 1
id | name
----+---------------------
3 | test3, no insert id
(1 row)

Regards,

Ikram

On Wed, Jun 12, 2024 at 2:33 PM sing_hijo@outlook.com <sing_hijo@outlook.com> wrote:

Dear PostgreSQL Support Team,
I am writing to report an issue I encountered while working with the PostgreSQL database. Below are the details of the problem:
Problem Description:
I encountered a unique constraint violation error when attempting to insert data into a table.
Steps to Reproduce:
Created the table test_table:
CREATE TABLE test_table (    id bigserial PRIMARY KEY,    name varchar
);
ALTER TABLE test_table OWNER TO postgres;
Inserted data into the table:
INSERT INTO test_table (name) VALUES ('test1, no insert id');
INSERT INTO test_table VALUES (2, 'test2, insert id');
INSERT INTO test_table (name) VALUES ('test3, no insert id');
Result:
Error message: [23505] ERROR: duplicate key value violates unique constraint "test_table_pkey" Detail: Key (id)=(2) already exists.
Expected Result:
I expected the data to be inserted successfully without encountering a unique constraint violation.
Environment Information:
PostgreSQL Version:PostgreSQL 14.1 (Debian 14.1-1.pgdg110+1) on x86_64-pc-linux-gnu, compiled by gcc (Debian 10.2.1-6) 10.2.1 20210110, 64-bit
Platform Information: Ubuntu-24.04 Linux sin 5.15.146.1-microsoft-standard-WSL2 #1 SMP Thu Jan 11 04:09:03 UTC 2024 x86_64 x86_64 x86_64 GNU/Linux
Reporting Method:
I am reporting this issue to seek assistance in resolving the unique constraint violation error. Please advise on the appropriate steps to address this issue.
Thank you for your attention to this matter. I look forward to your guidance on resolving this issue.
Best regards,
sing_hijo@outlook.com

Muhammad Ikram

Re: Issue Report: Unique Constraint Violation Error in PostgreSQL

From

Francisco Olarte

Date:

12 June, 14:56:02

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)
alreadyexists.
 

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.