From docs https://www.postgresql.org/docs/current/datatype-datetime.html#DATATYPE-DATETIME-INPUT-TIME-STAMPS
TIMESTAMP ‘2004-10-19 10:23:54+02’
is a timestamp with time zone
Question: Why select pg_typeof(TIMESTAMP '2004-10-19 10:23:54+02'); gives timestamp without time zone? Expecting timestamp with time zone as docs say.
Postgres version
SELECT version(); shows
PostgreSQL 14.14 (Debian 14.14-1.pgdg120+1) on x86_64-pc-linux-gnu, compiled by gcc (Debian 12.2.0-14) 12.2.0, 64-bit
>Solution :
As it says in that very documentation you link to:
PostgreSQL never examines the content of a literal string before determining its type, and therefore will treat both of the above as
timestamp without time zone. To ensure that a literal is treated astimestamp with time zone, give it the correct explicit type:TIMESTAMP WITH TIME ZONE '2004-10-19 10:23:54+02'