postgresql: copy missing records from one table to another

Question

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?

Will where a.id not in (select id from public_b.budget_items) work? — BillRobertson42
– BillRobertson42, Commented May 27, 2016 at 16:43
@Bill No. There are additional constraints in the table in addition to the primary key (id) like uc_budget_items_parent_null. I have to take those constraints into account in the query. — EatEmAll
– EatEmAll, Commented May 27, 2016 at 17:49

Y.B. · Accepted Answer · 2016-05-27 16:40:02Z

1

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

answered May 27, 2016 at 16:40

Y.B.

3,58618 silver badges24 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Vadzim · Accepted Answer · 2023-01-02 21:30:15Z

1

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.

answered Jan 2, 2023 at 21:30

Vadzim

26.4k14 gold badges153 silver badges160 bronze badges

Collectives™ on Stack Overflow

postgresql: copy missing records from one table to another

2 Answers 2

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related