PostgreSQL中没有的EXISTS会出现语法错误

When trying to use the WHERE NOT EXISTS clause to prevent adding a row with a duplicate value in the column age, I get the error syntax error at or near "WHERE".

当尝试使用WHERE NOT EXISTS子句来防止在列时代中添加具有重复值的行时,我在“WHERE”处或附近得到错误语法错误。

Why did it throw a syntax error? I'm using Postgresql 9.1.

为什么会抛出语法错误?我正在使用Postgresql 9.1。

SQL

INSERT INTO live.users ("website", "age") 
values ('abc', '123')
WHERE NOT EXISTS (SELECT age FROM live.users WHERE age = 123);

Error

ERROR:  syntax error at or near "WHERE"
LINE 6: WHERE NOT EXISTS (SELECT age FROM live.users W...

4 个解决方案

#1

Do instead:

INSERT INTO live.users ("website", "age") 
SELECT 'abc', 123
WHERE NOT EXISTS (SELECT age FROM live.users WHERE age = 123);

#2

INSERT INTO live.users ("website", "age") 
select 'abc', '123'
WHERE NOT EXISTS (SELECT age FROM live.users WHERE age = 123);

#3

I encountered some issues in using WHERE field NOT EXISTS in PLPGSQL. Instead what worked well was WHERE field NOT IN, I received no function errors after using that.

我在PLPGSQL中使用WHERE字段NOT EXISTS时遇到了一些问题。取而代之的是WHERE字段NOT IN,我使用它后没有收到任何功能错误。

#4

I see you asked for v9.1 but it's been 4 yrs since and now, starting from PostgreSQL v9.5 - INSERT gives you ON CONFLICT … DO NOTHING option:

我看到你要求v9.1,但它已经4年了,现在,从PostgreSQL v9.5开始 - INSERT让你开启冲突......没有任何选项:

INSERT INTO live.users("website", "age") VALUES('abc', '123') ON CONFLICT ("age") DO NOTHING

Worth noting this requires respective constraint set up on the target table - but in most cases, I imagine you would have it anyway. Otherwise you'll get:

值得注意的是,这需要在目标表上设置相应的约束 - 但在大多数情况下,我想你无论如何都会拥有它。否则你会得到:

ERROR:  there is no unique or exclusion constraint matching the ON CONFLICT specification

#1