CODEWITHSUNDEEP
sqlpostgresqlpsycopg2
saifuddin778
saifuddin778

Reputation: 7287

POSTGRES - Multiple operations in single transaction using psycopg

I have a query which Id like to run in postgres.

It deletes, and then inserts (kind of implements an ugly way of upserting which is unfortunately needed in my scenario). 

cursor, connection = get_pgsql_connection(...)

query = '''
     delete from table where name = 'xyz';
     insert into table (name, value, place) values
     ('A', 1, 0),
     ('B', 4, 1),
     ('C', 6, 2);
'''
cursor.execute(query)
connection.commit()
connection.close()

The question is: if anything for any reason fails in this query, then the entire operation (i.e. delete + insert) is aborted? For instance, if delete fails, will the inserts be aborted? Or similarly when one of the insert fails, will the delete be also rolled back?

Thank you.

Upvotes: 1

Views: 1747

Answers (1)

AdamKG
AdamKG

Reputation: 14091

The entire transaction will be rolled back. You can add a select 1/0; statement between the delete and the insert, or after the insert, and then check via a separate connection (with psql is fine) that the data didn't change to confirm this.

(psycopg2 does have an autocommit option that performs a commit after every statement, but it's off by default and I've never seen it used IRL.)

Upvotes: 1

Related Questions