Re: Domain based on TIMEZONE WITH TIME ZONE

On 10 May 2018, at 15:33, Tom Lane <tgl@sss.pgh.pa.us> wrote:

Ben Hood <ben@relops.com> writes:

So the question is not how does the timestamp get stored, rather, is it an anti-pattern to use Postgres as a linter for apps that forget to use UTC exclusively?

Well, using a domain to enforce additional constraints on a field's value
is certainly not an anti-pattern in itself.  But you have to realize that
the processing consists of first creating a value of the base type and
then applying the constraint expressions of the domain to it.  This means
you cannot check any details that are lost in the input conversion,
because you don't have access to the original input string, only the
stored value.

As others have explained, Postgres' TIMESTAMP WITH TIME ZONE type doesn't
preserve the input's timezone specification (if any) but forcibly rotates
to UTC and stores just a scalar UTC value.  So you can't use a domain to
check anything about whether the input had a timezone field and if so what
it was.

(This behavior is nonstandard --- the SQL spec evidently expects the
timezone to be stored explicitly in some fashion --- but I don't foresee
us changing it; we've accumulated too much backwards-compatibility
baggage now.)

If you're sufficiently intent on having checking of that sort, you could
invent your own datatype with your own input function, and then make it
binary-compatible with timestamptz so that you don't need to provide much
else besides the I/O functions.  varchar(n) has the same sort of
relationship with text, so there's precedent …