I'm using psycopg2 to insert data into a PostgreSQL table. I'd like to force the SQL query to overwrite/replace an existing row in case that row already exists (i.e. the new row has a primary key identical to an existing one).
So far I wrote a script that contains some inputs in a JS object, where col1 is the primary key:
myInputs = {'tablename': 'november', 'col1': 1, 'col2': 0.1, 'col3': 60}
The executed SQL (using psycopg2's cursor.execute) is the following:
"""INSERT INTO myschema.p{} (  col1,
                               col2,     
                               col3 
                                    ) 
             VALUES         (  {},
                               {},
                               {}   )""".format(
                                               myInputs ["tablename"],
                                               myInputs ["col1"],
                                               myInputs ["col2"],
                                               myInputs ["col3"]                                         
                           )
If I run the SQL command with a second set of inputs containing a similar primary key col1, e.g.
myInputs2 = {'tablename': 'november', 'col1': 1, 'col2': 10, 'col3': 20}
, I get the following error:
psycopg2.IntegrityError: duplicate key value violates unique constraint "pnovember_pkey"
DETAIL:  Key (col1)=(1) already exists.
What would be a proper way to handle this error and achieve this automatic row replacement? I've never used it, but would the best approach be to use the EXCEPTION clause described documentation?
