PostgreSQL Watch out timestamp or now() function returns timestamp with timezone offset not compatible with pl/pgsql timestamp data type

Run the two commands below and you will see what I mean:

select now() AT TIME ZONE current_setting('timezone');
2023-09-30 09:07:31.251

select now();
2023-09-30 09:17:51.338 -0400

select current_timestamp;
select current_timestamp  AT TIME ZONE current_setting('timezone');

select localtimestamp;

So if you have a pl/pgsql procedure that accepts a TIMESTAMP data type, you can’t pass in just “now()” as pl/sql sees an incompatible datatype and it will tell you it doesn’t have a procedure with that signature – procedure does not exist. Strip out the TIMEZONE OFF SET -4 or whatever and voila your procedure signature now matches.

Both now() and CURRENT_TIMESTAMP return timezone offset from GMT – unless you use the AT TIME ZONE current_setting(‘timezone’); or something else to strip off the TZ offset from GMT.

It’s only pl/sql that is so picky, if you are just submitting an insert via PSQL or pgadmin or dbeaver whatever, the database is smart enough to strip off the offset… go figure.