Oh I'll add to the pile: the Postgres ON CONFLICT/MERGE stuff doesn't capture the "select or insert if row doesn't exist" behavior some folks might want to use an upsert type thing for.
Why? You cannot access the row id or any values within the row as a return value unless the row changes. The top stackexchange answer goes into some detail about why you really don't want to simply make a dummy update in Postgres.
You can access these old row values in some scope for the no-update case, and this allows you to leverage CTEs instead of dummy updates. However… you can't use CTEs with prepared statements (and it's a non-obvious fail for the uninitiated). So yeah there are still lots of little sharp edges.
My experience was that this was not the case. Basically the CTE was materialized and the variables were resolved as part of PREPARE, so each invocation got the same values. I couldn't find the blurb I'd read previously but I did a little digging today and it looks like maybe NOT MATERIALIZED would fix the problem?
> "select or insert if row doesn't exist" behavior
I've hit this many, many times. It's such a common need, and yet I keep having to write it out as multiple queries to "select row" and if no result, do then insert query.
Why? You cannot access the row id or any values within the row as a return value unless the row changes. The top stackexchange answer goes into some detail about why you really don't want to simply make a dummy update in Postgres.
You can access these old row values in some scope for the no-update case, and this allows you to leverage CTEs instead of dummy updates. However… you can't use CTEs with prepared statements (and it's a non-obvious fail for the uninitiated). So yeah there are still lots of little sharp edges.