0

When I use the following query :

cur.execute("""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;""")

I keep getting this error :

psycopg2.errors.UndefinedTable: missing FROM-clause entry for table "tmp_table"
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("""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;""")

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 ?

1
  • Does TABLE is a table name??? 1st query - a) remove parentesis which wraps SELECT query - they converts rowset into one complex table-type value; b) in SET you must operate with EXCLUDED, not with source table. 2nd query - parenthesis only.
    – Akina
    Aug 7, 2020 at 8:33

1 Answer 1

1

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;

This site is temporarily in read-only mode and not accepting new answers.

Not the answer you're looking for? Browse other questions tagged .