Postgres upsert: name of the excluded column

Question

I have two Postgres tables:

Table 1

CREATE TABLE tmp.pm_update_20230101 (
    fid varchar NULL,
    fecha date NULL,
    p float4 NULL
);

Table 2

CREATE TABLE aemet.pmes (
    indic varchar NOT NULL,
    fecha date NOT NULL,
    prec float4 NULL,
    CONSTRAINT pmes_pkey PRIMARY KEY (indic, fecha)
);

I want to insert or update rows in table 1 with some rows from table 2, but in the following statement I don't know if in excluded.COLUMN I should write excluded.p or excluded.prec

insert into aemet.pmes (indic , fecha, prec) 
    select t.fid , t.fecha , t.p
    from tmp.pm_update_20230101 t
    where p is not null
on conflict on constraint pmes_pkey
do update set prec = excluded. COLUMN ;

you need to use the column name as defined in the target table. — , Mar 21 '23 at 13:39

score 0 · Answer 1 · answered Mar 21 '23 at 13:51

Thanks for your answer.

Previously I was wrong with the meaning of the column to which excluded was referring because I used two columns with the same column name

Now I have realized the name of the column excluded refers to with the following example

create table a (
id varchar primary key,
xa float);

create table b (
id varchar primary key,
xb float);

insert into a
values ('1', 1), ('2', 2), ('3', 3);

insert into b
values ('1', 10), ('2', 20), ('3', 30), ('4', 100);

select * from a order by id;

id	xa
1	1
2	2
3	3

select * from b order by id;

id	xb
1	10
2	20
3	30
4	100

insert into a 
select * from b 
on conflict (id)
do update set xa=excluded.xa;

select * from a order by id;

id	xa
1	10
2	20
3	30
4	100

Postgres upsert: name of the excluded column

1 Answers1