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.

David S · Accepted Answer · 2014-06-24 17:47:31Z

11

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! ;)

answered Jun 24, 2014 at 17:47

David S

14.1k13 gold badges60 silver badges97 bronze badges

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

2 Comments

Fábio Roberto Teodoro Over a year ago

ALTER SEQUENCE seq RESTART WITH (SELECT (max(id) + 1) FROM table);

Fábio Roberto Teodoro Over a year ago

Relevant docs: ALTER SEQUENCE, max(id)

Bert-Jan Stroop · Accepted Answer · 2024-02-28 06:57:39Z

Who is reading this in 2024. Here is a solution that works for

PostgreSQL 15.4, compiled by Visual C++ build 1914, 64-bit

The solution provided by Fábio Roberto Teodoro is no longer working these days. Because ALTER SEQUENCE does not "work" with subqueries anymore. You can't run this query . PostgreSQL will throw an exception, so you need to use setvalue Current answer was found here - https://stackoverflow.com/a/2022824/22463780

I am using PostgreSQL as DB for ASP.NET project, so the names of columns are not in postgreSQL syntax style.

You need to get the sequences of Table that you want to alter. The PostgreSQL query that returns all table sequences in your DB solution was found here - https://stackoverflow.com/a/63027265/22463780 Here is a query to get list of sequences.

SELECT t.oid::regclass AS table_name,
       a.attname AS column_name,
       s.relname AS sequence_name
FROM pg_class AS t
   JOIN pg_attribute AS a
      ON a.attrelid = t.oid
   JOIN pg_depend AS d
      ON d.refobjid = t.oid
         AND d.refobjsubid = a.attnum
   JOIN pg_class AS s
      ON s.oid = d.objid
WHERE d.classid = 'pg_catalog.pg_class'::regclass
  AND d.refclassid = 'pg_catalog.pg_class'::regclass
  AND d.deptype IN ('i', 'a')
  AND t.relkind IN ('r', 'P')
  AND s.relkind = 'S';

You need to run a query to set the current sequence value to the current highest id. This script was found here - https://stackoverflow.com/a/15162544/22463780

/* query for "Attachments" table to alter sequence of Id column */
/* after launching query in step 1 I found what for "Attachments" table I need to use sequence with name "Attachments_Id_seq" */
/* notice the single quotes around the double quotes*/
/* "Id" column is PK of table */

SELECT SETVAL('"public"."Attachments_Id_seq"'::regclass, (SELECT MAX("Id") FROM "Attachments"));

According to this - https://gist.github.com/henriquemenezes/962829815e8e7875f5f4376133b2f209?permalink_comment_id=2619919#gistcomment-2619919 which references this PostgreSQLManuals

When new record will be inserted, assuming that query above set sequence to start with id = 666, then the new record will be inserted with Id = 667.

Collectives™ on Stack Overflow

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

2 Answers 2

2 Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

2 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related