CODEWITHSUNDEEP
sqlpostgresqlsql-insertupsert
Em Ae
Em Ae

Reputation: 8724

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

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.

Upvotes: 1

Views: 4088

Answers (5)

Leonardo Freitas
Leonardo Freitas

Reputation: 130

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;

Upvotes: 1

Erwin Brandstetter
Erwin Brandstetter

Reputation: 658242

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:

Upvotes: 1

Othman Dahbi-Skali
Othman Dahbi-Skali

Reputation: 632

Use this query

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

Upvotes: -1

Gordon Linoff
Gordon Linoff

Reputation: 1270653

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.

Upvotes: 3

I wrestled a bear once.
I wrestled a bear once.

Reputation: 23389

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

Upvotes: 0

Related Questions