Postgres Duplicate Key Error Code

Question

currently I'm using postgresql as my database and I import all the data into the table through MS Excel csv format. All things went out smoothly but the problem is whenever I want to insert additional data into the table for example using the following code:

insert into country (name)
values ('America');

it keeps pop up an error of

ERROR:  duplicate key value violates unique constraint "effect_pkey"
DETAIL:  Key (country_id)=(1) already exists.
********** Error **********

ERROR: duplicate key value violates unique constraint "effect_pkey"
SQL state: 23505
Detail: Key (country_id)=(1) already exists.

when I keep trying to insert the data, the error getting is similar just the country_id in the error message is increasing. then I can only insert the data when I surpass the existing ID in my table. I would like to know how can I solve this out. Thank you very much.

Please add the table definition for the country table to your question. (country_id is a serial?) — joop
– joop, Commented Mar 4, 2014 at 9:38
@joop ya the id is in serial format and it's just a simple table with ID and NAME, — green
– green, Commented Mar 4, 2014 at 9:38
Than probably your data import forgot to reset the serial (to the maximal occuring value in the table) — joop
– joop, Commented Mar 4, 2014 at 9:40
You have to reset your primary serial key to the current value+1 imported from your file: You can chieve this like this: stackoverflow.com/a/3819323/1216680 — Houari
– Houari, Commented Mar 4, 2014 at 9:44

joop · Accepted Answer · 2014-03-04 09:43:58Z

2

Something like:

SELECT setval('country_id_seq', (SELECT MAX(country_id) FROM country) );

(I don't know the exact names, since the OP did not give a table definition in his question)

answered Mar 4, 2014 at 9:43

joop

4,5431 gold badge18 silver badges26 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

starball · Accepted Answer · 2023-11-17 19:17:19Z

0

A better solution now might be to use GENERATED AS IDENTITY to allow PostgreSQL to assign the id while ensuring it's not ever assigned externally.

See for example https://www.postgresqltutorial.com/postgresql-tutorial/postgresql-identity-column/

edited Nov 17, 2023 at 19:17

starball♦

59.5k52 gold badges312 silver badges1k bronze badges

answered Nov 17, 2023 at 18:29

Luis

1,2531 gold badge12 silver badges12 bronze badges

Collectives™ on Stack Overflow

Postgres Duplicate Key Error Code

2 Answers 2

Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related