CODEWITHSUNDEEP
sqlpostgresqlpostgresql-9.2
Gandalf StormCrow
Gandalf StormCrow

Reputation: 26212

Copying data from one table to another

I'm updating data by selecting data from table and inserting into another. However there are some constraints on the other table and I get this :

DETAIL:  Key (entry_id)=(391) is duplicated.

I basically do this :

insert into table_tmp 
select * from table_one

How can I skip insert when this key entry duplicate occurs?

Update I can't save this schema info on SQL fiddle but here it is :

CREATE TABLE table1
    ("entry_id" int, "text" varchar(255))
;

INSERT INTO table1
    ("entry_id", "text")
VALUES
    (1, 'one'),
    (2, 'two'),
    (3, 'test'),
    (3, 'test'),
    (12, 'three'),
    (13, 'four')
;



CREATE TABLE table2
    ("entry_id" int, "text" varchar(255))
;

Create unique index entry_id_idxs
on table2 (entry_id)
where text='test';

INSERT INTO table2
    ("entry_id", "text")
VALUES
    (1, 'one'),
    (2, 'two'),
    (3, 'test'),
    (3, 'test'),
    (12, 'three'),
    (13, 'four')
;

Error that I get if I try to build the schema

Upvotes: 2

Views: 176

Answers (2)

Bohemian
Bohemian

Reputation: 425328

Insert using join that returns unmatched rows:

INSERT INTO table2
SELECT DISTINCT t1.*
FROM table1 t1
LEFT JOIN table2 t2 ON t2.entry_id = t1.entry_id
WHERE t2.entry_id IS NULL

Upvotes: 2

mvp
mvp

Reputation: 116417

Use this query - SQLFiddle Demo:

INSERT INTO table2 
SELECT t1.* FROM table1 t1
WHERE NOT EXISTS (
    SELECT entry_id
    FROM table2 t2
    WHERE t2.entry_id = t1.entry_id
)

Upvotes: 2

Related Questions