PostgreSQL/Psycopg2 upsert syntax to update columns

Question

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?

Answer 1

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.