Python psycopg2 insert and update on conflict - set data for existed id and based on condition

Question

I have a table with columns as below: (id, col1, col2, col3, col4). I need to insert new row to this table, with given data is a dict: data = {'id': 1, 'col1': 1, 'col2':1, 'col3':1, 'col4': 1} for example.

I want to use the query below to insert data into table if id not existed, or update the table if id existed:

query = 
'''
    INSERT INTO tablename (id, col1, col2, col3, col4)
    VALUES (%(id)s, %(col1)s, %(col2)s, %(col3)s, %(col4)s)
    ON CONFLICT (id) DO UPDATE SET
    col2 = col2 + 1;
'''
result = cur.execute(query, data)

I can pass data into the Insert block, but with the On Conflict block when do the updating, col2 = col2 + data['col2] it raised the error as column referance "col2" is ambiguous.

Another question is how to update col2 value based on condition from other columns, for example: col2 = col2 + 1 if col1 == data['col1] else col2 = col2

Please search the plethora of Python DB-API SO posts, online tutorials, blogs, docs of parameterization in queries. Start with [psycopg2 docs](https://www.psycopg.org/docs/usage.html) discussing parameters. — Parfait, Mar 29 '20 at 18:12
@Parfait Thank you so much for your comment. I have edited the question. I did not describe it precisely last time. — Windy764, Mar 29 '20 at 18:50
Please show your coded attempt and not simply tell it so readers can better follow along. It is unclear if you are using parameters. And post errors in verbatim (not your paraphrase). I will vote to re-open if your code cannot be resolved with duplicate proposals. — Parfait, Mar 29 '20 at 18:53
@Parfait thanks for your comment. I have edited the question — Windy764, Mar 29 '20 at 19:02

score 1 · Accepted Answer · answered Mar 29 '20 at 22:37

Consider reusing your dictionary items as an additional parameter and use the the table name as alias:

query = '''INSERT INTO tablename (id, col1, col2, col3, col4)
           VALUES (%(id)s, %(col1)s, %(col2)s, %(col3)s, %(col4)s)
           ON CONFLICT (id) DO 
           UPDATE SET col2 = tablename.col2 + %(col2)s;
        '''

result = cur.execute(query, data)

In fact, you can use the excluded alias:

...
UPDATE SET col2 = tablename.col2 + excluded.col2;

And for a conditional logic, use a CASE statement:

query = '''INSERT INTO tablename (id, col1, col2, col3, col4)
           VALUES (%(id)s, %(col1)s, %(col2)s, %(col3)s, %(col4)s)
           ON CONFLICT (id) DO 
           UPDATE SET col2 = CASE 
                                 WHEN tablename.col1 = %(col1)s
                                 THEN tablename.col2 + 1
                                 ELSE tablename.col2
                             END;
        '''

result = cur.execute(query, data)

And again, you can use the excluded alias:

...
CASE 
   WHEN tablename.col1 = excluded.col1
   THEN tablename.col2 + 1 
   ELSE tablename.col2 
END;

Thank you so much. One more question, Can I use the value of col2 after calculation above to assign on next value? For example, `col3 = col2/100` — Windy764, Mar 30 '20 at 03:12
Try adding another `SET` assignment line in `UPDATE` reuse `CASE` with new expression: `UPDATE SET col2 = ..., col3 = CASE...THEN (tablename.col2 + 1) / 100 ELSE (tablename.col2) /100 ...` — Parfait, Mar 30 '20 at 15:44

Python psycopg2 insert and update on conflict - set data for existed id and based on condition

1 Answers1