Reputation: 63727
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"
.
Why did it throw a syntax error? I'm 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...
Upvotes: 8
Views: 25819
Reputation: 772
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:
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
Upvotes: 1
Reputation: 1
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.
Upvotes: 0
Reputation: 125574
INSERT INTO live.users ("website", "age")
select 'abc', '123'
WHERE NOT EXISTS (SELECT age FROM live.users WHERE age = 123);
Upvotes: 4
Reputation: 61726
Do instead:
INSERT INTO live.users ("website", "age")
SELECT 'abc', 123
WHERE NOT EXISTS (SELECT age FROM live.users WHERE age = 123);
Upvotes: 33