I have this query that attempts to add rows to the balances
table if no corresponding row exists in the totals
table. The query is run in a transaction using the default isolation level on PostgreSQL.
INSERT INTO balances (account_id, currency, amount)
SELECT t.account_id, t.currency, 0
FROM balances AS b
RIGHT OUTER JOIN totals USING (account_id, currency) AS t
WHERE b.id IS NULL
I have a UNIQUE
constraint on balances (accountId, currency)
. I'm worried that I will get into a race condition situation that will lead to duplicate key errors if multiple sessions execute this query concurrently. I've seen many questions on that subject, but they all seem to involve either subqueries, multiple queries or pgSQL functions.
Since I'm not using any of those in my query, is it free from race conditions? If it isn't how can I fix it?