If that's what you want, you can happily use Postgres' UPSERT implementation, and abandon subtransactions very easily.
https://www.postgresql.org/docs/current/sql-insert.html#SQL-...
back
1 comments
It's a good idea to revisit, but I think I need SELSERT or something. Are you saying the `DO NOTHING` clause can still result in a row being returned with `RETURNING *`? My impression is that it can't but I'd be excited to be wrong!
edit: Ah, I bet the implicit suggestion was to do `INSERT ... ON CONFLICT DO NOTHING RETURNING *` and if no row was returned, you hope to know/guess the conflict and do an extra `SELECT`. I'll think more on this, thanks.
> The SET and WHERE clauses in ON CONFLICT DO UPDATE have access to the existing row using the table's name (or an alias), and to rows proposed for insertion using the special excluded table.
Therefore you can do DO UPDATE SET table_name.column = Excluded.column WHERE table_name.column IS DISTINCT FROM Excluded.column RETURNING *
Am I right to interpret this as "do a non-changing UPDATE so that you get a returned row"? It's another great idea I hadn't considered, thank you. I'm worried this causes the row to get re-written on disk (an actual UPDATE, even though values aren't changing). That could be a moot concern, I'll dig more.
Yeah, that's a valid concern and perhaps you might find an answer here https://stackoverflow.com/questions/34708509/how-to-use-retu...