SQL WHERE NOT EXISTS in PostgreSQL 给出语法错误
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/15840922/
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
WHERE NOT EXISTS in PostgreSQL gives syntax error
提问by Nyxynyx
When trying to use the WHERE NOT EXISTSclause 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子句防止在列中添加具有重复值的行时，age出现错误syntax error at or near "WHERE"。
Why did it throw a syntax error? I'm using Postgresql 9.1.
为什么会抛出语法错误？我正在使用 Postgresql 9.1。
SQL
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...
回答by Daniel Vérité
Do instead:
改为：
INSERT INTO live.users ("website", "age")
SELECT 'abc', 123
WHERE NOT EXISTS (SELECT age FROM live.users WHERE age = 123);
回答by Clodoaldo Neto
INSERT INTO live.users ("website", "age")
select 'abc', '123'
WHERE NOT EXISTS (SELECT age FROM live.users WHERE age = 123);
回答by TheodoreC
I encountered some issues in using WHERE field NOT EXISTSin PLPGSQL. Instead what worked well was WHERE field NOT IN, I received no function errors after using that.
我WHERE field NOT EXISTS在 PLPGSQL中使用时遇到了一些问题。相反，效果很好的是WHERE field NOT IN，使用它后我没有收到任何功能错误。
回答by msciwoj
I see you asked for v9.1 but it's been 4 yrs since and now, starting from PostgreSQL v9.5 - INSERTgives you ON CONFLICT … DO NOTHINGoption:
我看到您要求使用 v9.1，但从PostgreSQL v9.5开始到现在已经 4 年了- INSERT为您提供了ON CONFLICT … DO NOTHING选项：
INSERT INTO live.users("website", "age") VALUES('abc', '123') ON CONFLICT ("age") DO NOTHING
Worth noting this requires respective constraintset 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
