[rst]

Skip down to the bottom for a simple approach that just works, in sufficiently recent PG releases (MERGE). Back up just a bit for something that's nearly as good, and works with everything since Postgresql 10 (INSERT ... ON CONFLICT). Most of the post describes more complicated approaches that have subtle race conditions, and are worth looking at mainly to understand what the problems are.

[geekodour]

at one point i was using upserts for everything but then had a case where it was impossible to get it working with generated id.

wrote about it very roughly here https://blog.geekodour.org/posts/upserts/