8

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...
Nyxynyx
  • 61,411
  • 155
  • 482
  • 830
  • If you want to prevent duplicate values in a column, it would be better to add a `unique constraint`to that column. (ALTER TABLE live.users ADD CONSTRAINT age_unique UNIQUE(age) ) – Terje D. Apr 05 '13 at 18:40

4 Answers4

33

Do instead:

INSERT INTO live.users ("website", "age") 
SELECT 'abc', 123
WHERE NOT EXISTS (SELECT age FROM live.users WHERE age = 123);
Daniel Vérité
  • 58,074
  • 15
  • 129
  • 156
4
INSERT INTO live.users ("website", "age") 
select 'abc', '123'
WHERE NOT EXISTS (SELECT age FROM live.users WHERE age = 123);
Clodoaldo Neto
  • 118,695
  • 26
  • 233
  • 260
1

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
msciwoj
  • 772
  • 7
  • 23
0

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.

Jeroen Vannevel
  • 43,651
  • 22
  • 107
  • 170
TheodoreC
  • 1