Upsert using CTE in postgres

user_4923949 0
Viewed 523

I need to upsert my pairs table using a with ins statement from python. I am gettin the error: psycopg2.errors.UndefinedTable: missing FROM-clause entry for table "ins" LINE 1: ... pairs_dup_key DO UPDATE SET exchange_pair_symbol=ins.exchan...

This is the sql code that I am running

WITH ins (a, b, c) AS
        (VALUES ('sdf',2,3))
        INSERT INTO pairs
        (foo_id,v,w)
        SELECT foo.id, ins.b, ins.c
        FROM
        ins
        LEFT JOIN foo ON foo.name=ins.a
        ON CONFLICT ON CONSTRAINT pairs_key DO UPDATE SET v=ins.b, w=ins.c;

What am I doing wrong?

sql-insert upsert postgresql sql
1 Answers

You're missing the EXCLUDED variable in your upsert.

Keep in mind that, after the conflict, the values of ins.a and ins.b no longer point to their origin, but to the first attempt to insert them into table pairs in the columns v and w. So, if you want to access these values, you must look into EXCLUDED, not to go back to the columns from the previous SELECT statement. Also, you do not need a CTE for that. A simple FROM (VALUES...) would suffice, e.g.

INSERT INTO pairs (foo_id,v,w) 
SELECT foo.id, ins.b, ins.c 
FROM (VALUES ('sdf',2,3)) ins (a,b,c)
LEFT JOIN foo ON foo.name=ins.a
ON CONFLICT (foo_id) DO UPDATE SET v=EXCLUDED.v, w=EXCLUDED.w;

However, if you must stick to the CTE try this..

WITH ins (a, b, c) AS
  (VALUES ('sdf',2,3)
)
INSERT INTO pairs (foo_id,v,w) 
SELECT foo.id, ins.b, ins.c 
FROM ins LEFT JOIN foo ON foo.name=ins.a
ON CONFLICT (foo_id) DO UPDATE SET v=EXCLUDED.v, w=EXCLUDED.w;
user_22753880
Related