PostgreSQL is running in autocommit mode, so every statement is running in its own transaction unless you explicitly start a transaction with BEGIN or START TRANSACTION.
The only way you can get your data back is by restoring from a backup.
Answer from Laurenz Albe on Stack Overflowbackup - Postgresql revert back database value - Database Administrators Stack Exchange
security - Is there some kind of "undo" or "rewind" feature in PostgreSQL? - Database Administrators Stack Exchange
sql - Restore deleted records in PostgreSQL - Stack Overflow
recovery - Recover PostgreSQL database data - Database Administrators Stack Exchange
What you are asking for doesn't exist. I think that if you regularly have to run interactive SQL statements manually in an important database, there is something wrong with your operational procedures.
The best we can offer is:
online backups with point-in-time recovery and an automated restore procedure
set autocommit to off in your favorite interactive client
This is somewhat dangerous, as long transactions threaten the health of your database. Make sure to set
idle_in_transaction_timeoutappropriately on the server.
From Comments:
This is usually referred as "point in time recovery". See a rather old article: https://pgdash.io/blog/postgres-incremental-backup-recovery.html. This procedure would not actually "rewind" but restore to a previous backup and then "replay" up to your wanted point in time. See also e-maj which offers something similar to what you want. Mentioned in this answer: selective undoing of commited transactions in PostgreSQL - users/993
May be this can help How to recover deleted records where Michael Fuhr says:
If you haven't VACUUMed the database then the deleted rows are probably still there. I don't know if any recovery tools exist, but to have any chance of recovering the data make a filesystem backup of $PGDATA as soon as possible, or at least make a backup of the table's file(s) (see below). They might not do you any good, but you'll probably need those files if you do find any recovery tools.
If you don't find any recovery tools and you really need to get the data back, then you might enjoy reading the chapter describing page file formats in the documentation ("Page Files" in PostgreSQL 7.x; "Database Physical Storage" in the upcoming 8.0).
You can find out which files hold a table's data by querying pg_database and pg_class.
In front of DML statements you need write "begin;" .In execution time you need to execute with DML statement along with begin .Then execute rollback statement
EXAMPLE:- step1:- BEGIN; DELETE FROM TABLE_NAME WHERE (CONDITION); setp2:- ROLLBACK;
I always do but I have seen plenty of developers and dbas don’t in production. It makes me cringe sometimes. If you are those people, can you tell me why? Are you worried about table locking?