Search code examples
postgresqlrollback

How to rollback an update in PostgreSQL

While editing some records in my PostgreSQL database using sql in the terminal (in ubuntu lucid), I made a wrong update.

Instead of -

update mytable set start_time='13:06:00' where id=123;

I typed -

update mytable set start_time='13:06:00';

So, all records are now having the same start_time value.

Is there a way to undo this change? There are some 500+ records in the table, and I do not know what the start_time value for each record was

Is it lost forever?

Solution

  • I'm assuming it was a transaction that's already committed? If so, that's what "commit" means, you can't go back.

    Some data may be recoverable if you're lucky. Stop the database NOW.

    Here's an answer I wrote on the same topic earlier. I hope it's helpful.

    This might be too: Recoved deleted rows in postgresql .

    Unless the data is absolutely critical, just restore from backups, it'll be lots easier and less painful. If you didn't have backups, consider yourself soundly thwacked.