postgresql - error 23505 duplicate key value violates unique constraint 'foo_column_key' (not primary key)

Question

i new in postgresql and when i test my code for insert, i get following error message

error exception('ERROR', '23505', 'duplicate key value violates unique constraint "foo_column_key"');

i had tried use connection.commit() or connection.rollback() right after cursor.execute() code.

question:

how i can fix this error without re-create table like command "select setval('foo_id_seq', 1)"? before i try used "reindex table foo;" from postgres but not work and run commit or rollback from psql not work too(maybe difference connection id). also search google for reset all transaction data or use search key my title above, but not find any solution.

anyone can help me or show me the direction to solve this?

thank you.

EDIT:

sorry, maybe this give clearly my question:

create table foo(
  foo_id serial unique not null primary key,
  foo_column character(35) unique not null
);

I insert data with this sql command from my programming code:

insert into foo(foo_column) values('[email protected]');

at first I check data in table by "select * from foo;", but there is no data insert. again i re-run code by refresh page(cgi application) and i got that message, and then i check again in table by select * from foo; but nothing inserted. this is my first time use insert that is transaction, before i use mysql with no transaction at all.

I tried to find solution but always found the solution is for column serial/bigserial primary key and i curious so i ask here. is there any method for solve this error without re-create table?

hope this give you more clearly about my question and thanks.

Answer 1

Obviously, from the message, you are trying to insert a value in a column that already exists there. If you have a sequencer on the field (like with a serial column type), it is likely out of sync with the table because you have done an insert and supplied a value (rather than letting the "default" mechanism handle it by getting the nextval from the sequencer). If this is the case, reset the sequencer with a setval statement to the max value of the field (google how to do that - lots of posts on that topic). Or just keeping try to do inserts until one of the magically works! ;)