Postgres错误：重复的键值违反了唯一约束 [英] Postgres ERROR:duplicate key value violates unique constraint

问题描述

表的定义是

create table users (
serial_no integer PRIMARY KEY DEFAULT nextval('serial'),
uid bigint NOT NULL,
username varchar(32),
name text,
CONSTRAINT production UNIQUE(uid)
);

我使用了此查询

INSERT INTO users (uid) values(123) ;

它表示重复键值违反了唯一约束。因此，我用Google搜索它，发现了这个链接

It says duplicate key value violates unique constraint. So I googled it and found this link

所以我尝试了

INSERT INTO users (uid) values(123) 
where 1 in (select 1 from users where uid = 123) ;

它表示 WHERE处或附近的语法错误。

It says yntax error at or near "WHERE".

如何在where子句中使用插入语句，以便当我使用php运行同一查询时不会返回错误

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

列uid是唯一的

推荐答案

INSERT语句不支持WHERE子句。

The INSERT statement doesn't support a WHERE clause. Run this.

create table test (
  n integer primary key
);

insert into test values (1);
insert into test values (2) where true;

由于WHERE子句，这将给您带来语法错误。

That will give you a syntax error because of the WHERE clause.

SELECT语句可以具有WHERE子句。这会将2插入测试表一次。根据需要运行它多次；它不会引发错误。 （但是最多只能插入一行。）

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.)

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

这篇关于Postgres错误：重复的键值违反了唯一约束的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！