Wishing for UPSERT syntax sugar
(1) By ddevienne on 2020-10-23 16:19:29 [source]
Hi. With 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
( ?, ?, ...
, ?, ?, ...
, ?, ?, ...
)
But with 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
And if cnt
was not defaulted but part of the original insert, the cnt =
would implicitly remove it form the set *
,
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?