INSERT .. ON CONFLICT DO UPDATE SET error When I use the following query :
cur.execute(&quot;&quot;&quot;INSERT INTO TABLE
(SELECT ID_tmp,First_seen_tmp,Last_seen_tmp,Duration_tmp
FROM tmp_table)
ON CONFLICT (ID)
DO
UPDATE SET Last_seen=tmp_table.Last_seen_tmp,Duration=tmp_table.Last_seen_tmp-First_seen;&quot;&quot;&quot;)

I keep getting this error :
psycopg2.errors.UndefinedTable: missing FROM-clause entry for table &quot;tmp_table&quot;
LINE X: UPDATE SET Last_seen=tmp_table.Last_seen_tmp,Duration=tmp...

And even if I try to use the special table EXCLUDED that references to the possible inserted values with the following code :
cur.execute(&quot;&quot;&quot;INSERT INTO TABLE
(SELECT ID_tmp,First_seen_tmp,Last_seen_tmp,Duration_tmp
FROM tmp_table)
ON CONFLICT (ID)
DO
UPDATE SET Last_seen=EXCLUDED.Last_seen_tmp,Duration=EXCLUDED.Last_seen_tmp-First_seen;&quot;&quot;&quot;)

I get another error :
psycopg2.errors.UndefinedColumn: column excluded.last_seen_tmp does not exist
LINE X: UPDATE SET Last_seen=EXCLUDED.Last_seen_tmp,Duration=EXCLUDE...

Is it impossible to update the values of the original table with values from the tmp table with an INSERT .. ON CONFLICT CLAUSE ?
The excluded record needs to reference columns from the target table, not the source (e.g. a VALUES clause would not have any column names anyway).
It's also good coding practice to always specify the target columns in the INSERT statement.
INSERT INTO the_table (id, first_seen, last_seen, duration)
SELECT ID_tmp, First_seen_tmp, Last_seen_tmp, Duration_tmp
FROM tmp_table
ON CONFLICT (ID)
DO UPDATE 
   SET last_seen = EXCLUDED.last_seen,
       duration = EXCLUDED.last_seen - the_table.first_seen;