Wishing for UPSERT syntax sugar
INSERT or REPLACE, a many column insert statement names the columns once
insert into tab ( nk1, nk2, ... // Natural Key columns , col1, col2, ... // "Normal" columns , colA, colB, ... // More "Normal" columns ) values ( ?, ?, ... , ?, ?, ... , ?, ?, ... )
UPSERT, which is superior IMO to the older
INSERT or REPLACE, you have to name the columns 3 times:
insert into tab ( nk1, nk2, ... , col1, col2, ... , colA, colB, ... ) values ( ?, ?, ... , ?, ?, ... , ?, ?, ... ) on conflict (nk1, nk2, ...) do update set col1 = excluded.col1 , col2 = excluded.col2 , ... , colA = excluded.colA , colB = excluded.colB , ...
Could there not be some kind of syntax sugar that automatically SETs all columns from the insert portion
which are not part of the
on conflict list? Perhaps something like:
... on conflict (nk1, nk2, ...) do update set *
Possibly followed by more assignment-expressions, assuming for example a
cnt (count) column defaulted to 1 on insert:
... on conflict (nk1, nk2, ...) do update set *, cnt = cnt + 1
cnt was not defaulted but part of the original insert, the
cnt = would implicitly remove it form the
similar to the columns in
on conflict being removed (their values are unchanged, no need to rewrite them).
Just thinking aloud. I just found the syntax on the heavy side with many columns. WDYT?