How to do an upsert when updating a table from another table postgres?

Question

I have table A that contains about 10 millions rows and Table B which contains some updated information for some rows in table A and also contains new rows that don't exist in table A.

I want to update table A using table B and at the same time insert rows that have no match in tableA.

I found many answers like the solution below but it seems like they all miss the inserting part that I'm looking for.

UPDATE A 
SET code = B.code
FROM B
WHERE A.id = B.id

If the data is in two tables, just run two queries, an update and an insert. — Gordon Linoff
– Gordon Linoff, Commented Mar 26, 2019 at 21:47

Gordon Linoff · Accepted Answer · 2019-03-26 21:48:33Z

1

Use two queries:

update a
    set code = b.code
    from b
    where a.id = b.id;

insert into a (id, code)
    select id, code
    from b
    where not exists (select 1 from a where a.id = b.id);

You can also use on conflict

insert into a (id, code)
    select b.id, b.code
    on conflict on constraint a_id
    do update set code = b.code;

answered Mar 26, 2019 at 21:48

Gordon Linoff

1.3m62 gold badges705 silver badges857 bronze badges

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

Collectives™ on Stack Overflow

How to do an upsert when updating a table from another table postgres?

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related