Copy a row column from one table to another in ON CONFLICT

Question

Using Postgres 9.6, I copy the row (selected columns) from one table to another. I am running:

INSERT INTO copy_on_write(id, images)
    SELECT id, images
    FROM listings
    WHERE id = 1 AND posted_by = foo
ON CONFLICT (id) DO UPDATE SET images = listings.images 

With conflict, because id already exists, I get missing FROM-clause entry for table "listings".

So I tried:

INSERT INTO copy_on_write(id, images)
    SELECT id, images
    FROM listings
    WHERE id = 1 AND posted_by = foo
ON CONFLICT (id) DO UPDATE SET images = images FROM listings

but then i get syntax error at or near "FROM"

How do I handle this ON CONFLICT (id)so that it updates the image column if the row id already exists?

Answer 1

Use excluded:

INSERT INTO copy_on_write(id, images) 
SELECT id, images 
FROM listings 
WHERE id = 1 AND posted_by = 'foo' 
ON CONFLICT (id) DO UPDATE SET images = excluded.images;

From the documentation:

conflict_action

(...) SET WHERE ON CONFLICT DO UPDATE ( ) , , excluded.