1

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.

Houari
  • 5,326
  • 3
  • 31
  • 54
green
  • 633
  • 1
  • 10
  • 26
  • Please add the table definition for the country table to your question. (country_id is a serial?) – joop Mar 04 '14 at 09:38
  • @joop ya the id is in serial format and it's just a simple table with ID and NAME, – green Mar 04 '14 at 09:38
  • Than probably your data import forgot to reset the serial (to the maximal occuring value in the table) – joop Mar 04 '14 at 09:40
  • @joop how should I overcome this issue..hmmmm – green Mar 04 '14 at 09:41
  • You have to reset your primary serial key to the current value+1 imported from your file: You can chieve this like this: http://stackoverflow.com/a/3819323/1216680 – Houari Mar 04 '14 at 09:44
  • And yes: this is probably a duplicate... – joop Mar 04 '14 at 09:44

1 Answers1

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)

joop
  • 4,330
  • 1
  • 15
  • 26