Rollback DML statement in pgAdmin

Question

In pgAdmin, if I execute an insert query, I don't see any way to either commit or rollback the statement I just ran (I know it auto commits). I'm used to Oracle and SQL developer, where I could run a statement, and then rollback the last statement I ran with a press of a button. How would I achieve the same thing here?

Solution 1

Use transaction in the SQL window:

BEGIN;
DROP TABLE foo;
ROLLBACK; -- or COMMIT;

-- edit -- Another example:

BEGIN;
INSERT INTO foo(bar) VALUES ('baz') RETURNING bar; -- the results will be returned
SELECT * FROM other_table; -- some more result
UPDATE other_table SET var = 'bla' WHERE id = 1 RETURNING *; -- the results will be returned

-- and when you're done with all statements and have seen the results:
ROLLBACK; -- or COMMIT 

Solution 2

I also DEARLY prefer the Oracle way of putting everything in a transaction automatically, to help avoid catastrophic manual mistakes.

Having auto-commit enabled by default in an Enterprise product, IMO, is beyond vicious, and nothing but a COMPLETELY, UTTERLY INSANE design-choice :(

Anyways --- working with Postgres, one always needs to remember

BEGIN;

at the start of manual work or sql-scripts.

As a practical habit: then, when you would say: COMMIT; in Oracle, I use the line

END; BEGIN;

in Postgres which does the same thing, i.e commits the current transaction and immediately starts a new one.

---

When using JDBC or similar, to create a connection, always use some method, e.g. getPGConnection(), that includes:

...
Connection dbConn = DriverManager.getConnection(dbUrl, dbUser, dbPassword);
dbConn.setAutoCommit(false);
...

to make sure every connection has auto-commit disabled.

Solution 3

If you are using pgAdmin4, you can turn the auto commit and/or auto rollback on and off.

Go to the File drop down menu and select Preferences option. In the SQL editor tab -> Options you can see the options to turn auto commit/rollback on and off.

Auto commit/rollback option