Re: Handling of tz-aware literals in non-tz-aware fields

On Tue, Aug 20, 2013 at 2:23 PM, Adrian Klaver <adrian.klaver@gmail.com> wrote:
> 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 as timestamp with time zone,
> give it the correct explicit type:
>
> TIMESTAMP WITH TIME ZONE '2004-10-19 10:23:54+02'
>
> In a literal that has been determined to be timestamp without time zone,
> PostgreSQL will silently ignore any time zone indication. That is, the
> resulting value is derived from the date/time fields in the input value, and
> is not adjusted for time zone.

Perfect. Well, probably less than perfect but perfectly documented.

A psycopg user was surprised because a Python datetime with time zone
is "erroneously treated correctly" in a query from Python (i.e. the
tzinfo is not discarded but taken in consideration)
<http://psycopg.lighthouseapp.com/projects/62710/tickets/176>. This is
because psycopg implicitly adds a timestamptz cast.

The only thing that bothers me is that in a future psycopg
implementation we may drop the cast, only relying on postgres
behaviour, and as a consequence passing a tz-aware object to a
non-tz-aware field (which is an operation that smells like
underspecified anyway) may change result. Such version would be
not-backward-compatible for other reasons, so it doesn't bother me
excessively.

-- Daniele