INSERT into a table from SELECT only if value doesn't exist

Question

I want to insert table1.id into table2.t1col only if table1.id doesn't exist in table2.t1col, yet.

I think I have to use :

insert into table2 name (t1col) value (select id from table1)

but I want to add only if that id doesn't exist in table2 already.

Answer 1

A unique/index constraint guarantees the uniqueness of values. So, it is recommended.

Unfortunately, though, a constraint violation causes the entire insert to fail. So, you can do:

insert into table2(t1col) 
    select id
    from table1 t1
    where not exists (select 1 from table2 t2 where t2.t1col = t1.id);

You should also have a unique index/constraint to prevent problems in the future.

Answer 2

You can use a Unique Index for preventing duplicated rows.

But if you want to insert rows filtering it to not insert duplicated rows, you can do this.

INSERT INTO table2 (idcol)
SELECT id FROM table1
EXCEPT
SELECT idcol FROM table2;

Answer 3

If you have a UNIQUE or PRIMARY KEY constraint on table2.t1col, like you most probably should, there is a more elegant solution for Postgres 9.5 (currently beta, to be released real soon now). Use the new UPSERT implementation INSERT ... ON CONFLICT DO NOTING. Quoting the manual:

The optional ON CONFLICT clause specifies an alternative action to raising a unique violation or exclusion constraint violation error. For each individual row proposed for insertion, either the insertion proceeds, or, if an arbiter constraint or index specified by conflict_target is violated, the alternative conflict_action is taken. ON CONFLICT DO NOTHING simply avoids inserting a row as its alternative action.

Bold emphasis mine.

So you can simply:

INSERT INTO table2(t1col)
SELECT id FROM table1
ON CONFLICT DO NOTHING;

If table1.id is not defined unique, make it unique:

INSERT INTO table2(t1col)
SELECT DISTINCT id FROM table1
ON CONFLICT DO NOTHING;

---

For Postgres 9.4 you can find a overview of techniques here:

• Select rows which are not present in other table.

Answer 4

Create a unique index on that column and be done with it. No need to check.

CREATE UNIQUE INDEX name ON table (column [, ...]);

http://www.postgresql.org/docs/9.4/static/indexes-unique.html

Answer 5

Use this query

INSERT INTO table2 name (t1col) value 
(
    SELECT t1.id FROM table1 t1, table2 t2
    WHERE t1.id <> t2.id
)