4

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?

8

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.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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