auto increment fails

Question

I have a table which is created in the code bellow:

CREATE TABLE "salesorderdetail" (
            "salesorderid" SERIAL NOT NULL ,
            "salesorderdetailid" SERIAL PRIMARY KEY,
            "orderqty" int NOT NULL,
            "productid" int NOT NULL,
            "unitprice" float NOT NULL,
            "unitpricediscount" float NOT NULL,
            "linetotal" float NOT NULL
        ) WITH (
          OIDS=FALSE
        );

Furthermore I have records imported from a csv file to the table. I want to insert a new record to the table and I keep getting this error

ERROR: duplicate key value violates unique constraint "salesorderdetail_pkey" DETAIL: Key (salesorderdetailid)=(2) already exists. ********** Error **********

ERROR: duplicate key value violates unique constraint "salesorderdetail_pkey" SQL state: 23505 Detail: Key (salesorderdetailid)=(2) already exists.

I tried to change the sequence of the table by executing the following query.

SELECT MAX("salesorderdetailid") FROM "salesorderdetail"; --Output is 75123
CREATE SEQUENCE user_id_seq;
ALTER SEQUENCE user_id_seq RESTART WITH 75124; --Hence I manually enter 75124

Then I tried the insert query again which is:

INSERT INTO "salesorderdetail" (orderqty,productid,unitprice,unitpricediscount,linetotal) values (1,1,8.00,0,8.00);

I still get this error! I also noticed that each time I'm trying to execute the insertion query, the duplicate key value automatically increases 3,4,5,6 etc.

Answer 1

(First, apologies for not commenting and instead answering - I do not have enough points yet to comment)

This is an interesting problem. I executed all of your code on my own sandbox instance of PostGres and had no issues with inserting any new data in the schema.

Where I did notice an issue is with this code block

SELECT MAX("salesorderdetailid") FROM "salesorderdetail"; --Output is 75123
CREATE SEQUENCE user_id_seq;
ALTER SEQUENCE user_id_seq RESTART WITH 75124; --Hence I manually enter 75124

In my own instance the sequence default name is salesorderdetail_salesorderdetailid_seq by default. Unless you changed it explicitly, I'm guessing that either something in the content of your CSV (seems less likely) or the naming of the sequence in your query above are the issue. Interestingly, PG returned no errors when I tried to restart this nonexistent sequence.

So - try this instead

alter sequence salesorderdetail_salesorderdetailid_seq RESTART with 75124;