Insert only new keys

CREATE TEMP TABLE source AS SELECT * FROM (VALUES
  (1, 'a', 1), (1, 'a2', 1), (3, 'c', 3), (3, 'c', 3), (4, 'b', 2), (NULL, 'n', 0), (5, NULL, 5)) t(id, name, qty);
-- by key: existing keys are left alone (key 1 keeps its values); the source keeps one row for each key, and a NULL key is not a key
CREATE TEMP TABLE by_key AS SELECT * FROM (VALUES (1, 'a', 1), (2, 'b', 2)) t(id, name, qty);
MERGE INTO by_key t USING (SELECT id, name, qty FROM source WHERE id IS NOT NULL QUALIFY row_number() OVER (PARTITION BY id ORDER BY name) = 1) s ON t.id = s.id
  WHEN NOT MATCHED THEN INSERT VALUES (s.id, s.name, s.qty);
-- by content hash: a row is new when no target row has the same hash of its values; the key plays no part. The hash is SHA-256 of the row as JSON,
-- which does not change between DuckDB versions, so it can be stored; a NULL is 'null' in the JSON and an empty string is '""', so they differ
CREATE TEMP TABLE by_hash AS SELECT name, qty, sha256(to_json({'name': name, 'qty': qty})) AS row_hash FROM (VALUES ('a', 1), ('b', 2)) t(name, qty);
MERGE INTO by_hash t
USING (SELECT name, qty, sha256(to_json({'name': name, 'qty': qty})) AS row_hash FROM source QUALIFY row_number() OVER (PARTITION BY row_hash ORDER BY id) = 1) s ON t.row_hash = s.row_hash
  WHEN NOT MATCHED THEN INSERT VALUES (s.name, s.qty, s.row_hash);
SELECT * FROM by_key ORDER BY id;
SELECT name, qty FROM by_hash ORDER BY name NULLS LAST, qty;
SELECT CASE WHEN ((SELECT list((id, name, qty) ORDER BY id) = [(1, 'a', 1), (2, 'b', 2), (3, 'c', 3), (4, 'b', 2), (5, NULL, 5)] FROM by_key)
  AND (SELECT list((name, qty) ORDER BY name NULLS LAST, qty) IS NOT DISTINCT FROM [('a', 1), ('a2', 1), ('b', 2), ('c', 3), ('n', 0), (NULL, 5)] FROM by_hash)) THEN 'as expected' ELSE error('by key the target should gain keys 3 (once), 4 and 5 and keep key 1 as it was; by content hash it should gain a2, c, n and the NULL-name row, and not the repeat of a, or the row with key 4 whose values b and 2 are already there') END AS verdict;