Why can't I use WHERE NOT EXISTS with INSERT?

Question

I want to insert a tag named "foo", unless it already exists. So I constructed the following query:

INSERT INTO "tag" ("name") VALUES ('foo') 
WHERE NOT EXISTS (SELECT 1 FROM "tag" WHERE ("tag"."name" = 'foo'));

But this will fail with the following error:

ERROR:  syntax error at or near "WHERE"
LINE 1: INSERT INTO "tag" ("name") VALUES ('foo') WHERE NOT EXISTS (...
                                                  ^

I don't understand where the problem with that query is. Especially, since I can provide a subquery instead of VALUES and suddenly the query is perfectly fine:

INSERT INTO "tag" ("name") SELECT 'foo' AS name 
WHERE NOT EXISTS (SELECT 1 FROM "tag" WHERE ("tag"."name" = 'foo'));

This results in:

Query returned successfully: 0 rows affected, 11 ms execution time.

It's 0 rows, because the tag already exists.

possible duplicate of [MySQL Conditional Insert](http://stackoverflow.com/questions/913841/mysql-conditional-insert) — Basile Starynkevitch, Sep 08 '15 at 11:19
@BasileStarynkevitch: MySQL behaves differently with regard to concurrency issues. — Erwin Brandstetter, Sep 08 '15 at 14:32

score 4 · Accepted Answer · edited May 23 '17 at 12:06

4

You can, you just need to use the INSERT INTO ... SELECT ... form.

INSERT INTO "tag" ("name")
SELECT 'foo'
WHERE NOT EXISTS (SELECT 1 FROM "tag" WHERE ("tag"."name" = 'foo'));

However, it doesn't do what you want. At least not under concurrent workloads. You can still get unique violations or duplicate inserts.

See:

edited May 23 '17 at 12:06

Community

1
1

answered Sep 08 '15 at 11:19

Craig Ringer

307,061
76
688
778

Why is the syntax with `VALUES` not valid, but the one involving the subquery is? – Oliver Salzburg Sep 08 '15 at 11:22
1

@OliverSalzburg `VALUES` doesn't have a `WHERE` clause. It's a simple list of one or more tuples. The syntax doesn't allow for anything else. So, basically, it's not valid because it's specified not to be valid. See http://www.postgresql.org/docs/current/static/sql-values.html – Craig Ringer Sep 08 '15 at 11:23
Ah! I didn't realize that the `WHERE` belongs to `VALUES`. Thanks for the help :) – Oliver Salzburg Sep 08 '15 at 11:24
1

@OliverSalzburg: no. The `where` does **not** belong to `values` it belongs to the `SELECT` – Sep 08 '15 at 11:27
@a_horse_with_no_name: What I was trying to say is, when PostgreSQL reports the error, it indicates to me, that `WHERE` is not a valid keyword in the given context. I assumed that the context is the complete `SELECT` statement, when PostgreSQL is trying to tell me that the context is the `VALUES` part of the statement. I hope that explanation is clear enough. Rest assured that I have understood the problem well enough now ;) – Oliver Salzburg Sep 08 '15 at 11:36
"it doesn't do what you want. At least not under concurrent workloads" do you happen to have a reference for that please? – RedYeti Jul 23 '20 at 14:18
1

@RedYeti The PostgreSQL manual. Read the chapters on MVCC, locking and concurrency. In short nothing stops two concurrent txns evaluating the subquery, neither finding a row, then both inserting a new row. Read the documentation on INSERT ... ON CONFLICT DO NOTHING for the correct way – Craig Ringer Jul 24 '20 at 00:00

Why can't I use WHERE NOT EXISTS with INSERT?

1 Answers1