Use extra columns in INSERT values list in ON CONFLICT UPDATE

I have an UPSERT query where I want to insert value y value in column b, but if it already exists I want to update it with value z.

INSERT INTO test (a,b)
select P.x,P.y
from (VALUES 
    ('123', 4, 5),
    ('345', 2, 2)  
) K(x,y,z)
ON CONFLICT (a) DO UPDATE
SET b = K.z;

How can I achieve this?

P.S: A simple value list (without select) did not work because we cannot have more columns in values list than we are inserting.

Answer

In the SET part, you can only reference columns of the target table and the corresponding “values” through the excluded record. Neither of them has a column named z

The only way I can think of, is to put the values into a CTE and access the column z through a sub-query:

with data (x,y,z) as (
  VALUES 
      (123, 4, 6),
      (345, 2, 3)  
)
INSERT INTO test (a,b)
select d1.x, d1.y
from data d1
ON CONFLICT (a) DO UPDATE
  SET b = (select d2.z from data d2 where d2.x = excluded.a);

The above assumes that a is the primary (or unique) key of the table.

Online example