Geek asks and answers

WHERE NOT EXISTS in PostgreSQL gives syntax error

When I try to use a sentence WHERE NOT EXISTSto prevent the addition of a row with a duplicate value in the column age, I get an error syntax error at or near "WHERE".

Why did he throw a syntax error? I am using 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...
+5
source share
4 answers

Do instead:

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

WHERE field NOT EXISTS PLPGSQL. , , WHERE field NOT IN, .

0

, v9.1, 4 , PostgreSQL v9.5 - INSERT ON CONFLICT … DO NOTHING:

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

It is worth noting that this requires an appropriate restriction configured on the target table, but in most cases, I assume that you will get it anyway. Otherwise, you will receive:

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

More articles:


All Articles