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?