Unique constraints roll your transaction back. Put your `INSERT` in a nested transaction and you can fall back to `SELECT`, and keep going without throwing away previous work. In multi-table, write-heavy workloads, this insert-or-select path begs for nested transactions so you can lean on uniqueness constraints while keeping your transaction afloat. So far I'm not convinced to abandon that pattern by this article, but I'll keep an eye out for a happier path I guess.
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.
