I have a table that records requests to an external API for data enrichment. I want to ensure that there is only one row for a given entity with status = 'Pending' (meaning a process is doing the work.) With a unique constraint, I have that covered.

Now I want to pull out the pending request WITH FOR UPDATE to ensure that only one process is using it at a time, in case two async jobs try to work on the same request.

I can INSERT […] ON CONFLICT DO NOTHING for the upsert. Annoyingly, this returns nothing if there is an existing row so I can’t rely on RETURNING, but I can then SELECT […] WITH FOR UPDATE NOWAIT to ensure that only on process can hold it.

Can I do this in one statement?

Essentially I would like to do this:

INSERT […] ON CONFLICT DO NOTHING RETURNING * WITH FOR UPDATE NOWAIT