I want to implement SCD-Type2, and keep track of historized data, I am using for this task DuckDB, but I found out that DuckDB does not support Merge Statement.
The idea I have is to have two separate query one for update, and another for insert.
My question is: What is the best practices should I follow in this case? I would appreciate any other ideas or inputs. Also I am not sure how should I handle the deleted row in this case
Even if looking a bit awkward, you can rely on insert … on conflict after having prepared a CTE with both operations (insert + update):
with
-- Stuff the desired target ID and columns values.
n as (select '+' op, 1 objid, 'first' val),
-- Do instanciate one '+' operation (= insert) and one '=' (= update) for each value to add.
-- The '=' will get the unique ID of the currently active row, in order to later trigger a conflict.
ops as
(
select op, case when op in ('+', '=') then nextval('id_seq') end id, * from n
union all
select '=', id, n.* from n join t on t.objid = n.objid and t.stopped is null
)
-- Now insert everything, and update on conflict (the '=').
insert into t (id, objid, val) select id, objid, val from ops where op in ('+', '=')
on conflict do update set stopped = get_current_timestamp();
Your n table of new values will accept the '-' operation too, for deletions.
You'll get your history (here object ID 1 got 3 successive values, and object 2 got briefly created then deleted):
| id | objid | started | stopped | val |
|---|---|---|---|---|
| 1 | 1 | 2025-05-16 22:18:34.821 | 2025-05-16 22:18:34.825 | first |
| 2 | 1 | 2025-05-16 22:18:34.825 | 2025-05-16 22:18:34.831 | second |
| 3 | 2 | 2025-05-16 22:18:34.825 | 2025-05-16 22:18:34.831 | temp |
| 4 | 1 | 2025-05-16 22:18:34.831 | NULL | third |
See the example generating this result in a fiddle.
update t set stopped = i.started from (insert into t … returning id, objid, started) i where t.objid = i.objid and t.id < i.id and t.stopped is nullwork?