CODEWITHSUNDEEP
sqlpostgresql
EatEmAll
EatEmAll

Reputation: 87

postgresql: copy missing records from one table to another

I have two tables public_a.budget_items and public_b.budget_items with identical schemes and I'm trying to copy missing records from one of them to the other using this query:

INSERT INTO public_b.budget_items
SELECT a.*
FROM   public_a.budget_items a
WHERE  a.id NOT IN (SELECT b.id
                    FROM   public_b.budget_items b
                    WHERE  b.id = a.id
                            OR ( b.budget_id = a.budget_id
                                 AND b.NAME = a.NAME
                                 AND b.type = a.type
                                 AND b.asset = a.asset ))

but I'm getting this error:

ERROR: duplicate key value violates unique constraint "uc_budget_items_parent_null" SQL state: 23505 Detail: Key (budget_id, name, type, asset)=(3486, Octopus, 6, T) already exists

The constraint uc_budget_items_parent_null is defined as follows:

  CREATE UNIQUE INDEX uc_budget_items_parent_null ON budget_items USING btree (
budget_id, name, type, asset);

I thought that the condition (b.budget_id=a.budget_id and b.name=a.name and b.type = a.type and b.asset = a.asset) should help me avoid this problem, but it doesn't seem to work.

Clearly I'm missing something here. Any ideas?

Upvotes: 2

Views: 1353

Answers (2)

Vadzim
Vadzim

Reputation: 26180

Alternative solution applicable in case both tables have a primary key or unique index:

insert into target_table
select * from source_table
on conflict (id) do nothing;

See also https://www.postgresql.org/docs/current/sql-insert.html#SQL-ON-CONFLICT.

Upvotes: 1

Y.B.
Y.B.

Reputation: 3586

I think you've got too deep recursion here. Try this:

INSERT INTO public_b.budget_items
SELECT    a.*
FROM      public_a.budget_items a
LEFT JOIN public_b.budget_items b
       ON b.id = a.id
       OR ( b.budget_id = a.budget_id
            AND b.NAME  = a.NAME
            AND b.type  = a.type
            AND b.asset = a.asset )
WHERE b.id Is NULL

Upvotes: 1

Related Questions