PostgreSQL – Upsert Syntax to Update Columns with Psycopg2

insertpostgresqlpythonupsert

I want to have Psycopg2 update col1, col2 and col3 when there is a conflict of id.

In my Python code I have the insert SQL currently as:

insert_sql = '''INSERT INTO {t} (id,col1,col2,col3)
        VALUES (%s,%s,NULLIF(%s, 'nan'), NULLIF(%s, 'nan'))
        ON CONFLICT (id)
        DO NOTHING;'''

Basically instead of DO NOTHING I want to set:

(col1,col2,col3) = (%s,NULLIF(%s, 'nan'), NULLIF(%s, 'nan'))

Which ignores inserting ID and updates col1, col2, and col3.
The problem is using %s to pass in tuple variables in Python using Psycopg2:

cur.execute(insert_sql.format(t='my_table'),(int(id),new_col1,new_col2,new_col3))

What is the syntax used to refer to the %s corresponding to col1, col2, and col3 to update ON CONFLICT?

Best Answer

You can use the EXCLUDED keyword to access the values passed to INSERT. No need to pass them twice:

insert_sql = '''
   INSERT INTO {t} (id,col1, col2, col3)
        VALUES (%s, %s, NULLIF(%s, 'nan'), NULLIF(%s, 'nan'))
        ON CONFLICT (id)
        DO UPDATE SET
            (col1, col2, col3)
            = (EXCLUDED.col1, EXCLUDED.col2, EXCLUDED.col3) ;
'''

See examples of use in Postgres documentation about ON CONFLICT.

Related Question