Thread: duplicate key value violates unique constraint"chinese_price_infos_pkey"

duplicate key value violates unique constraint"chinese_price_infos_pkey"

From

Arup Rakshit

Date:

06 May 2019, 14:05:00

Every time I try to insert I get the error:

docking_dev=# INSERT INTO "chinese_price_infos" ("item_code", "price_cents", "unit", "description", "company_id",
"created_at","updated_at") VALUES ('01GS10001', 6000, 'Lift', 'Shore Crane Rental',
'9ae3f8b8-8f3f-491c-918a-efd8f5100a5e','2019-05-06 10:49:03.894725', '2019-05-06 10:49:03.894725'); 
ERROR:  duplicate key value violates unique constraint "chinese_price_infos_pkey"
DETAIL:  Key (id)=(71165) already exists.
docking_dev=# INSERT INTO "chinese_price_infos" ("item_code", "price_cents", "unit", "description", "company_id",
"created_at","updated_at") VALUES ('01GS10001', 6000, 'Lift', 'Shore Crane Rental',
'9ae3f8b8-8f3f-491c-918a-efd8f5100a5e','2019-05-06 10:49:03.894725', '2019-05-06 10:49:03.894725'); 
ERROR:  duplicate key value violates unique constraint "chinese_price_infos_pkey"
DETAIL:  Key (id)=(71166) already exists.

Then I found:

docking_dev=# SELECT MAX(id) FROM chinese_price_infos;
  max
--------
 128520
(1 row)

docking_dev=# SELECT nextval('chinese_price_infos_id_seq');
 nextval
---------
   71164
(1 row)

Not sure how it is out of sync. How can I fix this permanently. I ran vacuum analyze verbose; still same error.

Thanks,

Arup Rakshit
ar@zeit.io

Re: duplicate key value violates unique constraint"chinese_price_infos_pkey"

From

Ray O'Donnell

Date:

06 May 2019, 14:08:03

On 06/05/2019 12:05, Arup Rakshit wrote:
> Every time I try to insert I get the error:
> 
> docking_dev=# INSERT INTO "chinese_price_infos" ("item_code",
> "price_cents", "unit", "description", "company_id", "created_at",
> "updated_at") VALUES ('01GS10001', 6000, 'Lift', 'Shore Crane
> Rental', '9ae3f8b8-8f3f-491c-918a-efd8f5100a5e', '2019-05-06
> 10:49:03.894725', '2019-05-06 10:49:03.894725'); ERROR:  duplicate
> key value violates unique constraint "chinese_price_infos_pkey" 
> DETAIL:  Key (id)=(71165) already exists. docking_dev=# INSERT INTO
> "chinese_price_infos" ("item_code", "price_cents", "unit",
> "description", "company_id", "created_at", "updated_at") VALUES
> ('01GS10001', 6000, 'Lift', 'Shore Crane Rental',
> '9ae3f8b8-8f3f-491c-918a-efd8f5100a5e', '2019-05-06 10:49:03.894725',
> '2019-05-06 10:49:03.894725'); ERROR:  duplicate key value violates
> unique constraint "chinese_price_infos_pkey" DETAIL:  Key
> (id)=(71166) already exists.
> 
> Then I found:
> 
> docking_dev=# SELECT MAX(id) FROM chinese_price_infos; max -------- 
> 128520 (1 row)
> 
> docking_dev=# SELECT nextval('chinese_price_infos_id_seq'); nextval 
> --------- 71164 (1 row)
> 
> Not sure how it is out of sync. How can I fix this permanently. I ran
> vacuum analyze verbose; still same error.


You can fix it by using setval() to set the sequence manually to 
something higher than the highest current id value in the table. 
However, it sounds as if something in the application code may be 
causing problems.... For example, is something generating id values 
without reference to the sequence?

Ray.



-- 
Raymond O'Donnell // Galway // Ireland
ray@rodonnell.ie

Re: duplicate key value violates unique constraint"chinese_price_infos_pkey"

From

Arup Rakshit

Date:

06 May 2019, 14:10:50

Hi,

Thanks for your reply. It is automatic, my app don’t creates ID, it delegates it to the DB. I am using Ruby on Rails
app,where we use Postgresql.  

docking_dev=# \d chinese_price_infos;
                                         Table "public.chinese_price_infos"
   Column    |            Type             | Collation | Nullable |                     Default
-------------+-----------------------------+-----------+----------+-------------------------------------------------
 id          | integer                     |           | not null | nextval('chinese_price_infos_id_seq'::regclass)
 created_at  | timestamp without time zone |           |          |
 updated_at  | timestamp without time zone |           |          |
 item_code   | character varying(255)      |           |          |
 description | character varying(255)      |           |          |
 unit        | character varying(255)      |           |          |
 price_cents | integer                     |           |          |
 uuid        | uuid                        |           |          | uuid_generate_v4()
 company_id  | uuid                        |           |          |
Indexes:
    "chinese_price_infos_pkey" PRIMARY KEY, btree (id)
    "index_chinese_price_infos_on_company_id" btree (company_id)




Thanks,

Arup Rakshit
ar@zeit.io



> On 06-May-2019, at 4:38 PM, Ray O'Donnell <ray@rodonnell.ie> wrote:
>
> On 06/05/2019 12:05, Arup Rakshit wrote:
>> Every time I try to insert I get the error:
>> docking_dev=# INSERT INTO "chinese_price_infos" ("item_code",
>> "price_cents", "unit", "description", "company_id", "created_at",
>> "updated_at") VALUES ('01GS10001', 6000, 'Lift', 'Shore Crane
>> Rental', '9ae3f8b8-8f3f-491c-918a-efd8f5100a5e', '2019-05-06
>> 10:49:03.894725', '2019-05-06 10:49:03.894725'); ERROR:  duplicate
>> key value violates unique constraint "chinese_price_infos_pkey" DETAIL:  Key (id)=(71165) already exists.
docking_dev=#INSERT INTO 
>> "chinese_price_infos" ("item_code", "price_cents", "unit",
>> "description", "company_id", "created_at", "updated_at") VALUES
>> ('01GS10001', 6000, 'Lift', 'Shore Crane Rental',
>> '9ae3f8b8-8f3f-491c-918a-efd8f5100a5e', '2019-05-06 10:49:03.894725',
>> '2019-05-06 10:49:03.894725'); ERROR:  duplicate key value violates
>> unique constraint "chinese_price_infos_pkey" DETAIL:  Key
>> (id)=(71166) already exists.
>> Then I found:
>> docking_dev=# SELECT MAX(id) FROM chinese_price_infos; max -------- 128520 (1 row)
>> docking_dev=# SELECT nextval('chinese_price_infos_id_seq'); nextval --------- 71164 (1 row)
>> Not sure how it is out of sync. How can I fix this permanently. I ran
>> vacuum analyze verbose; still same error.
>
>
> You can fix it by using setval() to set the sequence manually to something higher than the highest current id value
inthe table. However, it sounds as if something in the application code may be causing problems.... For example, is
somethinggenerating id values without reference to the sequence? 
>
> Ray.
>
>
>
> --
> Raymond O'Donnell // Galway // Ireland
> ray@rodonnell.ie

Re: duplicate key value violates unique constraint"chinese_price_infos_pkey"

From

Ray O'Donnell

Date:

06 May 2019, 15:19:41

On 06/05/2019 12:10, Arup Rakshit wrote:
> Hi,
> 
> Thanks for your reply. It is automatic, my app don’t creates ID, it
> delegates it to the DB. I am using Ruby on Rails app, where we use
> Postgresql.

Well, I'm only throwing out wild guesses, but another possibility is 
that rows were loaded manually into that table which had different 
values for the id column; this would lead to what you see.

Anyway, I'd fix the sequence value first with setval(), and then observe 
for a while; if you're still getting id collisions, then something is 
definitely inserting id values independently of the sequence. Remember, 
the sequence is only used as a default for that column; if you insert a 
value into the column, the sequence isn't used and so its current value 
doesn't change.

Ray.

-- 
Raymond O'Donnell // Galway // Ireland
ray@rodonnell.ie

Re: duplicate key value violates unique constraint "chinese_price_infos_pkey"

From

Michael Nolan

Date:

06 May 2019, 18:13:33

On Mon, May 6, 2019 at 6:05 AM Arup Rakshit <ar@zeit.io> wrote:

SELECT MAX(id) FROM chinese_price_infos; max -------- 128520(1 row)

SELECT nextval('chinese_price_infos_id_seq'); nextval --------- 71164(1 row)

Not sure how it is out of sync. How can I fix this permanently. I ran vacuum analyze verbose; still same error

Vacuuming the table doesn't change the fact that your sequence isn't generating unique keys. Without knowing the full history of the table, I can only speculate as to how keys got inserted that are beyond the sequence's current vlue, but most likely you will need to use the sequence manipulation function setval to set the sequence to at least one greater than the current maximum value.

Mike Nolan

Re: duplicate key value violates unique constraint"chinese_price_infos_pkey"

From

Frank Alberto Rodriguez

Date:

07 May 2019, 19:54:57

You can fix the problem with this query:

SELECT setval('chinese_price_infos_id_seq', sq.val) from ( SELECT MAX(id) as val FROM chinese_price_infos ) sq;

But you have to search in your application because in some point the app are inserting the id column instead of leave this task to the DB.

If you are using some entity framework and the app fill the ID field in a new entity in some point of the workflow, then when you save the entity, the framework automatically saves the ID in the DB without checking if the ID already exist.

Sheers

On Mon, 2019-05-06 at 16:40 +0530, Arup Rakshit wrote:

Hi,

Thanks for your reply. It is automatic, my app don’t creates ID, it delegates it to the DB. I am using Ruby on Rails app, where we use Postgresql.

docking_dev=# \d chinese_price_infos;

                                         Table "public.chinese_price_infos"

   Column    |            Type             | Collation | Nullable |                     Default

-------------+-----------------------------+-----------+----------+-------------------------------------------------

 id          | integer                     |           | not null | nextval('chinese_price_infos_id_seq'::regclass)

 created_at  | timestamp without time zone |           |          |

 updated_at  | timestamp without time zone |           |          |

 item_code   | character varying(255)      |           |          |

 description | character varying(255)      |           |          |

 unit        | character varying(255)      |           |          |

 price_cents | integer                     |           |          |

 uuid        | uuid                        |           |          | uuid_generate_v4()

 company_id  | uuid                        |           |          |

Indexes:

    "chinese_price_infos_pkey" PRIMARY KEY, btree (id)

    "index_chinese_price_infos_on_company_id" btree (company_id)

Thanks,

Arup Rakshit

ar@zeit.io

On 06-May-2019, at 4:38 PM, Ray O'Donnell <ray@rodonnell.ie> wrote:

On 06/05/2019 12:05, Arup Rakshit wrote:

Every time I try to insert I get the error:

docking_dev=# INSERT INTO "chinese_price_infos" ("item_code",

"price_cents", "unit", "description", "company_id", "created_at",

"updated_at") VALUES ('01GS10001', 6000, 'Lift', 'Shore Crane

Rental', '9ae3f8b8-8f3f-491c-918a-efd8f5100a5e', '2019-05-06

10:49:03.894725', '2019-05-06 10:49:03.894725'); ERROR:  duplicate

key value violates unique constraint "chinese_price_infos_pkey" DETAIL:  Key (id)=(71165) already exists. docking_dev=# INSERT INTO

"chinese_price_infos" ("item_code", "price_cents", "unit",

"description", "company_id", "created_at", "updated_at") VALUES

('01GS10001', 6000, 'Lift', 'Shore Crane Rental',

'9ae3f8b8-8f3f-491c-918a-efd8f5100a5e', '2019-05-06 10:49:03.894725',

'2019-05-06 10:49:03.894725'); ERROR:  duplicate key value violates

unique constraint "chinese_price_infos_pkey" DETAIL:  Key

(id)=(71166) already exists.

Then I found:

docking_dev=# SELECT MAX(id) FROM chinese_price_infos; max -------- 128520 (1 row)

docking_dev=# SELECT nextval('chinese_price_infos_id_seq'); nextval --------- 71164 (1 row)

Not sure how it is out of sync. How can I fix this permanently. I ran

vacuum analyze verbose; still same error.

You can fix it by using setval() to set the sequence manually to something higher than the highest current id value in the table. However, it sounds as if something in the application code may be causing problems.... For example, is something generating id values without reference to the sequence?

Ray.

--

Raymond O'Donnell // Galway // Ireland

ray@rodonnell.ie