Which SQL pattern is faster to avoid inserting duplicate rows?

Question

I know two ways to paste without duplication. The first uses a sentence WHERE NOT EXISTS:

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)

another performs 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 that one is faster than the other, or does it depend on tables? Is there any other way that is better than both?

Answer 1

I would recommend defining a UNIQUE constraint for the column (s) in which you must be unique (col1 and col2 in this case), and then just type INSERT. Handle exceptions if necessary.

---

, , PostgreSQL - , , . , .

:

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

Answer 2

, EXISTS ! :

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;

EXISTS 50 , NOT EXISTS.

EXCEPT.

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

EXCEPT 3 , NOT EXISTS.