Content-Type: text/plain;
	charset=utf-8
Mime-Version: 1.0 (Mac OS X Mail 16.0 \(3774.500.171.1.1\))
Subject: Re: Updating 457 rows in a table
From: Alban Hertroys <haramrae@gmail.com>
In-Reply-To: <7e82178c-1249-b592-bfea-5152563ceb4c@appl-ecosys.com>
Date: Mon, 20 May 2024 11:30:29 +0200
Cc: pgsql-general <pgsql-general@lists.postgresql.org>
Content-Transfer-Encoding: quoted-printable
Message-Id: <7F273F19-168F-4C66-85C5-B4BA8395E7A6@gmail.com>
References: <164b3681-7a42-1e5-6a7b-80196ed33ded@appl-ecosys.com>
 <1E14886A-BBCB-40E4-8231-1159D12BD261@thebuild.com>
 <98639d1a-dddf-38e3-49b3-63632136a0f@appl-ecosys.com>
 <372F0968-BD78-45EB-AE70-015DBF923EBC@thebuild.com>
 <7e82178c-1249-b592-bfea-5152563ceb4c@appl-ecosys.com>
To: Rich Shepard <rshepard@appl-ecosys.com>
Archived-At: <https://www.postgresql.org/message-id/7F273F19-168F-4C66-85C5-B4BA8395E7A6%40gmail.com>
Precedence: bulk


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

That aside, while you=E2=80=99re 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:
=3D> BEGIN;
=3D> UPDATE people SET active=3Dtrue WHERE email is not null;
(497 rows affected)

If that does indeed read =E2=80=9C497 rows affected=E2=80=9D:
=3D> COMMIT;

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

Don=E2=80=99t 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=E2=80=99=
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.