0

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?

sc28
  • 1,163
  • 4
  • 26
  • 48
  • 1
    https://www.postgresql.org/docs/current/static/sql-insert.html#SQL-ON-CONFLICT –  Nov 10 '17 at 16:29
  • Just a suggestion, first delete the record based on matching key and then insert. – Gaurav Nov 10 '17 at 16:31

0 Answers0