Missing FROM-clause entry for table "t2"

June 2, 2022

INSERT INTO patient_tbl(
patient_no, 
gender, 
nationality, 
dob, 
occupation) 
SELECT "t2".patient_no, 
t2.gender,
t2.nationality, 
t2.dob,
t2.occupation
FROM temp_patient_tbl t2 
ON CONFLICT (patient_no) DO UPDATE SET 
gender = t2.gender, 
nationality = t2.nationality, 
dob = t2.dob, 
occupation = t2.occupation;

And I got this error:

ERROR: missing FROM-clause entry for table "t2"
LINE 14: gender = t2.gender,
^

Have been trying and Googling very long but no luck.

>Solution :

As documented in the manual you need to use the keyword excluded to refer to the row values for which the insert was attempted:

The SET and WHERE clauses in ON CONFLICT DO UPDATE have access […] to rows proposed for insertion using the special excluded table.

INSERT INTO patient_tbl(patient_no, gender, nationality, dob, occupation) 
SELECT t2.patient_no, 
       t2.gender,
       t2.nationality, 
       t2.dob,
      t2.occupation
FROM temp_patient_tbl t2 
ON CONFLICT (patient_no) DO UPDATE SET 
  gender = EXCLUDED.gender, 
  nationality = EXCLUDED.nationality, 
  dob = EXCLUDED.dob, 
  occupation = EXCLUDED.occupation;