How to specify information from the table being inserted on PostgreSQL upsert?

I am trying to insert values from a temporary table (NewThings) with columns Thing and Counts into my main table (MyThings) with columns Thing and Count. If a thing does not exist in the main table I want to insert a new row, otherwise I want to add to the counter. However, I am struggling with the correct syntax on the update statement. This is what I would like to do:

INSERT INTO MyThings SELECT Thing, Counts FROM NewThings
        ON CONFLICT (Thing)
            DO UPDATE SET Count = MyThings.Count + NewThings.Counts;

But I only get this to work:

INSERT INTO MyThings SELECT Thing, Counts FROM NewThings
        ON CONFLICT (Thing)
            DO UPDATE SET Count = MyThings.Count + 1;

How can I correctly add the counts value from the NewThings table?

Answer

You have to use EXCLUDED instead of NewThings

INSERT INTO MyThings 
SELECT Thing, Counts FROM NewThings
ON CONFLICT (Thing)
DO UPDATE SET Count = Count + EXCLUDED.Count;

Advertisement

Answer