MERGE INTO
The MERGE INTO statement is an alternative to INSERT INTO ... ON CONFLICT that doesn't need a primary key since it allows for a custom match condition. This is a very useful alternative for upserting use cases (INSERT + UPDATE) when the destination table does not have a primary key constraint.
Examples
First, let's create a simple table.
CREATE TABLE people (id INTEGER, name VARCHAR, salary FLOAT);
INSERT INTO people VALUES (1, 'John', 92_000.0), (2, 'Anna', 100_000.0);The simplest upsert would be to use a whole row in the USING clause.
This way, if there is a match,
the row can be updated to the new row without further instructions
(WHEN MATCHED THEN UPDATE), and when there is no match,
the row can be trivially inserted into the table
(WHEN NOT MATCHED THEN INSERT).
MERGE INTO people USING ( SELECT unnest([3, 1]) AS id, unnest(['Sarah', 'John']) AS name, unnest([95_000.0, 105_000.0]) AS salary ) AS upserts ON (upserts.id = people.id) WHEN MATCHED THEN UPDATE WHEN NOT MATCHED THEN INSERT;
FROM peopleORDER BY id; id | name | salary----+-------+-------- 1 | John | 105000 2 | Anna | 100000 3 | Sarah | 95000In the previous example we are updating the whole row if id matches. However, it is also a common pattern to receive a change set with some keys and the changed value. This is a good use for SET. If the match condition uses a column that has the same name in the source and destination, the keyword USING can be used in the match condition.
MERGE INTO people USING ( SELECT 1 AS id, 98_000.0 AS salary ) AS salary_updates USING (id) WHEN MATCHED THEN UPDATE SET salary = salary_updates.salary;
FROM peopleORDER BY id; id | name | salary----+-------+-------- 1 | John | 98000 2 | Anna | 100000 3 | Sarah | 95000Another common pattern is to receive a delete set of rows, which may only contain ids of rows to be deleted.
MERGE INTO people USING ( SELECT 1 AS id, ) AS deletes USING (id) WHEN MATCHED THEN DELETE;
FROM peopleORDER BY id; id | name | salary----+-------+-------- 2 | Anna | 100000 3 | Sarah | 95000MERGE INTO also supports more complex conditions, for example, for a given delete set we can decide to only remove rows that contain a salary bigger or equal than a certain amount.
MERGE INTO people USING ( SELECT unnest([3, 2]) AS id, ) AS deletes USING (id) WHEN MATCHED AND people.salary >= 100_000.0 THEN DELETE;
FROM peopleORDER BY id; id | name | salary----+-------+-------- 3 | Sarah | 95000If needed, SereneDB also supports multiple UPDATE and DELETE conditions. The RETURNING clause can be used to indicate which rows were affected by the MERGE statement.
-- Let's get John back in!INSERT INTO people VALUES (1, 'John', 105_000.0);
MERGE INTO people USING ( SELECT unnest([3, 1]) AS id, unnest([89_000.0, 70_000.0]) AS salary ) AS upserts USING (id) WHEN MATCHED AND people.salary < 100_000.0 THEN UPDATE SET salary = upserts.salary -- Second update or delete condition WHEN MATCHED AND people.salary > 100_000.0 THEN DELETE WHEN NOT MATCHED THEN INSERT BY NAME RETURNING merge_action, *; merge_action | id | name | salary--------------+----+-------+-------- UPDATE | 3 | Sarah | 89000 DELETE | 1 | John | 105000In some cases, you may want to perform a different action specifically if the source doesn't meet a condition. For example, if we expect that data that is not present on the source shouldn't be present in the target:
CREATE TABLE target AS SELECT unnest([1,2]) AS id;
MERGE INTO target USING (SELECT 1 AS id) source USING (id) WHEN MATCHED THEN UPDATE WHEN NOT MATCHED BY SOURCE THEN DELETE RETURNING merge_action, *; merge_action | id--------------+---- UPDATE | 1 DELETE | 2There is also the possibility of specifying WHEN NOT MATCHED BY TARGET. However, the behavior is, as you may expect, the same as WHEN NOT MATCHED since by default when specifying conditions, we look at the target.