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.

CodePudding user response：

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;