Postgres UPSERT reuse column values from INSERT on UPDATE

Question

I have a basic upsert query that works ok:

insert into table (id, data) values (1, '<data_string>') 
    on conflict (id) do update set data='<data_string>';

The only problem is I need to fly lots of these queries over the network so I wonder if there is a way to cut the traffic in half by not having <data_string> listed twice in the query?

Answer 1

Yes, there is the special table EXCLUDED for the purpose:

INSERT INTO tbl (id, data)
VALUES (1, '<data_string>') 
ON CONFLICT (id) DO UPDATE SET data = EXCLUDED.data;

Like the manual explains:

Note that the special excluded table is used to reference values originally proposed for insertion:

Works for multi-row INSERTs as well:

INSERT INTO tbl (id, data)
VALUES (1, '<data_string1>') 
     , (2, '<data_string2>') 
     , (3, '<data_string3>') 
ON CONFLICT (id) DO UPDATE
SET data = EXCLUDED.data;