It's not what it looks like. Selecting either timestamp or timestamptz gives you a string representation in the database's configured local time zone, which your backend's Postgres driver will convert to whatever date representation (like `Date` in NodeJS). But timestamp leaves the time zone out of the string. Your Postgres driver assumes UTC in that case, which is a problem if your database is set to PST.
So with a PST database...
select now() as timestamptz; -- 2022-11-09T14:41:12.110+08
select now() as timestamp; -- 2022-11-09T14:41:12.110
You're right, this shouldn't be a thing. It's advisable to always set your Postgres config to UTC locale to eliminate the root of this nonsense, but that's not the default. So if your database is on PST, at least timestamptz will output strings that tell your code the correct time zone.
This is poorly explained in the official docs, btw. I think the only thing saving a lot of users is how AWS, GCP, etc all set their Postgres instances to UTC by default.
So unless you are using the sql result verbatim as presentation layer, is there any big benefit of this? On the contrary it seems like yet another moving part one could mess up and accidentally convert time where it shouldn’t?