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;

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.