Insert If Not Exists

SQL++
1

I have this query:

INSERT INTO users (KEY, VALUE)
VALUES ("counter:numeric-id", 1000);

This does what I would like, which is insert the counter if it does not exist, and does not when the counter does already exist. However, if it does already exist, then it throws an error. I would much prefer if it didn’t do that. So, I would like to do some sort of “insert if not exists” statement.

INSERT INTO users (KEY, VALUE)
VALUES ("counter:numeric-id", 1000)
WHERE NOT EXISTS (SELECT * FROM users USE KEYS "counter:numeric-id");

However, INSERT statements do not support WHERE clauses. So, I tried doing Insert-Select:

INSERT INTO `users` u (KEY k, VALUE v)
    SELECT "counter:numeric-id" AS k, 1031 AS v
    FROM `users` uu
    WHERE NOT EXISTS (SELECT * FROM `users` uuu USE KEYS "counter:numeric-id");

But I get an error still:

[
  {
    "code": 4000,
    "msg": "No index available on keyspace users that matches your query. Use CREATE INDEX or CREATE PRIMARY INDEX to create an index, or check that your expected index is online.",
"query_from_user": "INSERT INTO `users` u (KEY k, VALUE v)\r\n    SELECT \"counter:numeric-id\" AS k, 1031 AS v\r\n    FROM `users` uu\r\n    WHERE NOT EXISTS (SELECT * FROM `users` uuu USE KEYS \"counter:numeric-id\")\r\nRETURNING *;"
  }
]

I feel like this shouldn’t be this complicated - insert if not exists is kind of data store 101. Is there no way to do this without throwing the error when it already exists? Full disclosure: I have a similar question regarding creating an Index if it doesn’t already exist, but that would be a different kind of query anyway.

2

At present IF EXIST or IF NOT EXIST on DDL’s are not supported.

For INSERT you can explore MERGE statement

https://docs.couchbase.com/server/current/n1ql/n1ql-language-reference/merge.html

3

Thank you. MERGE worked perfectly:

MERGE INTO `users` u
USING [1000] source
ON KEY "counter:numeric-id"
WHEN NOT MATCHED THEN INSERT source