postgresql Postgres 错误：重复的键值违反了唯一约束
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/9243189/
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
Postgres ERROR:duplicate key value violates unique constraint
提问by Anubhav Agarwal
Table defintion is
表定义是
create table users (
serial_no integer PRIMARY KEY DEFAULT nextval('serial'),
uid bigint NOT NULL,
username varchar(32),
name text,
CONSTRAINT production UNIQUE(uid)
);
I used this query
我使用了这个查询
INSERT INTO users (uid) values(123) ;
It says duplicate key value violates unique constraint. So I googled it and found this link
它说重复的键值违反了唯一约束。所以我用谷歌搜索并找到了这个链接
So I tried
所以我试过了
INSERT INTO users (uid) values(123)
where 1 in (select 1 from users where uid = 123) ;
It says yntax error at or near "WHERE".
它说“WHERE”处或附近的语法错误。
How to use a statement of insert into using the where clause so that when I run the same query using php it does not return an error
如何使用 where 子句使用 insert into 语句，以便当我使用 php 运行相同的查询时，它不会返回错误
column uid is unique
列 uid 是唯一的
采纳答案by Mike Sherrill 'Cat Recall'
The INSERT statementdoesn't support a WHERE clause. Run this.
在INSERT语句不支持WHERE子句。运行这个。
create table test (
n integer primary key
);
insert into test values (1);
insert into test values (2) where true;
That will give you a syntax error because of the WHERE clause.
由于 WHERE 子句，这会给您带来语法错误。
SELECT statements can have a WHERE clause, though. This will insert 2 into the test table one time. Run it as many times as you want; it won't raise an error. (But it will insert only one row at the most.)
不过，SELECT 语句可以有一个 WHERE 子句。这将一次将 2 插入到测试表中。根据需要多次运行它；它不会引发错误。（但它最多只会插入一行。）
insert into test (n)
select 2 where 2 not in (select n from test where n = 2);
So your query, assuming you're trying to avoid raising an error on a duplicate key, should be something like this.
所以你的查询，假设你试图避免在重复键上引发错误，应该是这样的。
INSERT INTO users (uid)
SELECT 123 WHERE 123 not in (SELECT uid FROM users WHERE uid = 123) ;
