Is there a recommended way to find or create a record? (postgres)
I don't want to do an insert with
ON CONFLICT RETURNING since I have triggers which fire on row update and can't have them be called without an actual row change.7 Replies
Would
ON CONFLICT DO NOTHING RETURNING work?
I’m not super familiar with Postgres but I’d assume DO NOTHING would not fire update triggersit does, but it also doesn't return - it only returns if you insert or update values
Ah interesting. Sounds like you’ll need two queries, first insert and if it fails, get the existing row
(Insert before select avoids a race condition of concurrent inserts)
A quick search surfaces https://stackoverflow.com/questions/64802569/get-or-create-record-with-single-query which provides a way to do this in one query with a CTE
Stack Overflow
Get or Create record with single query
I have a table with column Id,column1,column2.
I wants to make sure only one record exists with column1 and column2 value (column1+column2 is unique).
If there is an already record exists with colu...
Why not just wrap this in a single transaction. Where you initiate transaction, if the record is not found, then continue within the same transaction and create the record?
I'd recommend this over triggers so you keep your logic primarily on the server
I think the concurrency behavior is actually worse doing the select first even with a transaction?
Not certain though
the triggers are a separate problem