public inbox for [email protected]  

help / color / mirror / Atom feed

From: Alban Hertroys <[email protected]>
To: Rich Shepard <[email protected]>
Cc: pgsql-general <[email protected]>
Subject: Re: Updating 457 rows in a table
Date: Mon, 20 May 2024 11:30:29 +0200
Message-ID: <[email protected]> (raw)
In-Reply-To: <[email protected]>
References: <[email protected]>
	<[email protected]>
	<[email protected]>
	<[email protected]>
	<[email protected]>


> On 19 May 2024, at 20:37, Rich Shepard <[email protected]> wrote:
> 
> On Sun, 19 May 2024, Christophe Pettus wrote:
> 
>> Of course, you can probably also shorten the query to:
>> 
>> UPDATE people SET active=true WHERE ...
>> 
>> Where ... is the predicate you would have used in the SELECT id WHERE ...
> 
> Ah, yes. Hadn't thought of that. The statement would be
> UPDATE people SET active=true WHERE email is not null;

That aside, while you’re not absolutely 100% definitely sure that an UPDATE or DELETE statement is going to do exactly what you intended, and for good measure if you are, wrapping such statements in a transaction allows you to ROLLBACK to get back to the state that you started from.

So:
=> BEGIN;
=> UPDATE people SET active=true WHERE email is not null;
(497 rows affected)

If that does indeed read “497 rows affected”:
=> COMMIT;

But if that doesn’t read 497, instead of COMMITting the transaction, you now have the opportunity to investigate what other rows changed that shouldn’t have and how to change your predicates - and then simply type:
=> ROLLBACK;

Don’t forget to start a new transaction again for the next attempt.

In PostgreSQL this also works for almost all DDL statements (CREATE TABLE, DROP TABLE, TRUNCATE TABLE, etc.), which is one of the features about this database that I really appreciate - some big names don’t have that.

Regards,

Alban Hertroys
--
If you can't see the forest for the trees,
cut the trees and you'll find there is no forest.

view thread (6+ messages)  latest in thread

reply

Reply instructions:

You may reply publicly to this message via plain-text email
using any one of the following methods:

* Reply to all the recipients using the --to and --cc options:
  reply via email

  To: [email protected]
  Cc: [email protected], [email protected], [email protected]
  Subject: Re: Updating 457 rows in a table
  In-Reply-To: <[email protected]>

* Save the following mbox file, import it into your mail client,
  and reply-to-all from there: mbox

This inbox is served by agora; see mirroring instructions
for how to clone and mirror all data and code used for this inbox