postgresql - 错误 23505 重复键值违反了唯一约束“foo_column_key”（不是主键）
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/24390820/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
postgresql - error 23505 duplicate key value violates unique constraint 'foo_column_key' (not primary key)
提问by crazymob
i new in postgresql and when i test my code for insert, i get following error message
我是 postgresql 的新手，当我测试我的插入代码时，我收到以下错误消息
error exception('ERROR', '23505', 'duplicate key value violates unique constraint "foo_column_key"');
错误异常（'错误'，'23505'，'重复键值违反唯一约束“foo_column_key”'）；
i had tried use connection.commit() or connection.rollback() right after cursor.execute() code.
我曾尝试在 cursor.execute() 代码之后立即使用 connection.commit() 或 connection.rollback()。
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.
如何在不重新创建表的情况下修复此错误，例如命令“select setval('foo_id_seq', 1)”？在我尝试使用“reindex table foo;”之前 从 postgres 但不起作用，从 psql 运行提交或回滚也不起作用（可能连接 ID 不同）。也搜索谷歌重置所有交易数据或使用搜索键我上面的标题，但没有找到任何解决方案。
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:
我从我的编程代码中使用这个 sql 命令插入数据：
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.
起初我通过“select * from foo;”检查表中的数据，但没有数据插入。我再次通过刷新页面（cgi 应用程序）重新运行代码并收到该消息，然后我再次检查表中select * from foo;但没有插入任何内容。这是我第一次使用事务插入，在我使用 mysql 之前根本没有事务。
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.
希望这能让你更清楚地了解我的问题，谢谢。
回答by David S
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! ;)
显然，从消息中可以看出，您正试图在已存在的列中插入一个值。如果您在字段上有一个定序器（如串行列类型），它可能与表不同步，因为您已经完成了插入并提供了一个值（而不是让“默认”机制通过获取nextval 来自音序器）。如果是这种情况，请使用 setval 语句将定序器重置为该字段的最大值（谷歌如何做到这一点 - 关于该主题的大量帖子）。或者只是继续尝试插入，直到其中一个神奇地起作用！;)
