PostgreSQL: Which SQL pattern is faster to avoid inserting duplicate rows?

Question

I know of two ways to insert without duplication. The first is using a WHERE NOT EXISTS clause:

INSERT INTO table_name (col1, col2, col3)
SELECT %s, %s, %s
WHERE NOT EXISTS (
    SELECT * FROM table_name AS T
    WHERE T.col1 = %s
      AND T.col2 = %s)

the other is doing a LEFT JOIN:

INSERT INTO table_name (col1, col2, col3)
SELECT %s, %s, %s
FROM ( SELECT %s, %s, %s ) A
LEFT JOIN table_name B
ON  B.COL1 = %s
AND B.COL2 = %s
WHERE B.id IS NULL
LIMIT 1

Is there a general rule as to one being faster than the other, or does it depend on the tables? Is there a different way which is better than both?

Answer 1

I would recommend defining a UNIQUE constraint on the column(s) you need to be unique (col1 & col2 in this case), and then just do the INSERT. Handle exceptions as needed.

---

Re your comment about the exception demanding a rollback, the solution for PostgreSQL is to set a transaction savepoint before you try the insert that may cause an exception. If you get the exception, rollback to the savepoint.

See:

• http://www.postgresql.org/docs/current/static/sql-savepoint.html
• http://www.postgresql.org/docs/current/static/sql-rollback-to.html

Answer 2

I think using EXISTS is more efficient!You could do like this:

if exists(select 1 from table_name where col1 = %s and col2 = %s) then
  insert into table_name (col1, col2, col3)
  select %s, %s, %s;
end if;

under test,using EXISTS is about 50 times faster then using NOT EXISTS.

another method is using EXCEPT .

INSERT INTO table_name (col1, col2, col3)
SELECT %s, %s, %s
except
select col1, col2, col3 from table_name

under test,using EXCEPT is about 3 times faster then using NOT EXISTS.