Re: Domain based on TIMEZONE WITH TIME ZONE

Sorry about the bug in the subject: the data type is TIMESTAMP WITH TIME ZONE, not TIMEZONE WITH TIME ZONE

> On 10 May 2018, at 09:03, Ben Hood <ben(at)relops(dot)com> wrote:
>
> Hi,
>
> I'm using a domain to specialize the built in TIMEZONE WITH TIME ZONE type. I want to sanity check this approach before continuing to use this.
>
> I want to prevent timestamps with non-UTC offsets from getting inserted into the database. Having a UTC-only database at the schema level means no app, proc, script or load operation can inadvertently supply a local time offset.
>
> The domain is defined as:
>
> CREATE DOMAIN UTC_TIMESTAMP AS TIMESTAMP WITH TIME ZONE CHECK (EXTRACT(TIMEZONE FROM VALUE) = 0);
> CREATE TYPE UTC_RANGE AS RANGE ( SUBTYPE = UTC_TIMESTAMP );
>
> My potentially misguided assumption is that I am effectively re-using a check constraint across the schema.
>
> Is this crazy?
>
> Am I missing the point of how Postgres stores TIMEZONE WITH TIME ZONE internally?
>
> Thanks in advance,
>
> Ben
>
>