Re: duplicate key value violates unique constraint "chinese_price_infos_pkey"

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(at)zeit(dot)io
>
>
>
> On 06-May-2019, at 4:38 PM, Ray O'Donnell <ray(at)rodonnell(dot)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(at)rodonnell(dot)ie
>
>
>
>