2

I want to do something like this:

SELECT * FROM TABLE where *condition*
... TEST for a row being returned
IF NOT
    INSERT the row

In other words I only want to insert a row into the table if it is not already there. My concern is that while I am testing the result set the row could in fact be inserted by another process. I won't know this and two rows will get inserted. I don't want this to happen.

I thought of wrapping the two statements in a transaction but I suspect this isn't the answer; I don't know if transactions have an effect similar to locking the table while they are being executed thus preventing any other inserts between my SELECT and INSERT? This is the question.

I suspect the best way to do it is simply to set a unique key across the columns in the row and so prevent duplicate inserts. But I still wonder if my idea about using transactions is at all valid or wildly off?

Erwin Brandstetter
  • 605,456
  • 145
  • 1,078
  • 1,228

1 Answers1

6

The database will ensure atomicity. But this will not help you, because atomicity does not mean what you think. It merely says, that the DB will either perform a set of operations either completely or not at all.

This is the reason, why DB folks always talk about ACID which means:

  • Atomicity
  • Consistency
  • Isolation
  • Durability

You should read the linked Wikipedia article and also the chapter Transaction Isolation in the PostgreSQL manual, which also explains all that in the context of PostgreSQL.

Oh, you would basically need ACI in your case. And if you don't want to use an unique index, you would have to do a table lock onto the complete table before inserting. So the index is the simplest and most robust solution.

A.H.
  • 63,967
  • 15
  • 92
  • 126