Postgres: INSERT if does not exist already

Postgres 9.5 (released since 2016-01-07) offers an "upsert" command, also known as an ON CONFLICT clause to INSERT:

INSERT ... ON CONFLICT DO NOTHING/UPDATE

It solves many of the subtle problems you can run into when using concurrent operation, which some other answers propose.

How can I write an 'INSERT unless this row already exists' SQL statement?

There is a nice way of doing conditional INSERT in PostgreSQL:

INSERT INTO example_table    (id, name)SELECT 1, 'John'WHERE    NOT EXISTS (        SELECT id FROM example_table WHERE id = 1    );

CAVEAT This approach is not 100% reliable for concurrent write operations, though. There is a very tiny race condition between the SELECT in the NOT EXISTS anti-semi-join and the INSERT itself. It can fail under such conditions.

One approach would be to create a non-constrained (no unique indexes) table to insert all your data into and do a select distinct from that to do your insert into your hundred table.

So high level would be. I assume all three columns are distinct in my example so for step3 change the NOT EXITS join to only join on the unique columns in the hundred table.

1. Create temporary table. See docs here.

   CREATE TEMPORARY TABLE temp_data(name, name_slug, status);

2. INSERT Data into temp table.

   INSERT INTO temp_data(name, name_slug, status); 

3. Add any indexes to the temp table.

4. Do main table insert.

   INSERT INTO hundred(name, name_slug, status)     SELECT DISTINCT name, name_slug, status    FROM hundred    WHERE NOT EXISTS (        SELECT 'X'         FROM temp_data        WHERE             temp_data.name          = hundred.name            AND temp_data.name_slug = hundred.name_slug            AND temp_data.status    = status    );