2018-05-10 9:01 GMT+02:00 Ben Hood <[email protected]>: > > > On 9 May 2018, at 08:33, Lukas Eder <[email protected]> wrote: > > > > ... But before you move on, please make sure you properly understand > PostgreSQL's implementation of TIMESTAMP WITH TIME ZONE. In 10 years of > doing jOOQ, I've never encountered anyone (ab)using domains to patch time > zones on their data types. It feels like the wrong approach. I'm sure > PostgreSQL gets this right. > > > Many thanks for taking the time share this wisdom, which goes beyond the > scope of JOOQ - very much appreciated. > > For note, in my own (potentially deluded) mind, the objective of the > domain was to specialize the built in TIMESTAMP WITH TIME ZONE type to make > it impossible to insert a non-UTC timestamp into the database, either from > an application or from a proc. > > In my mind, this use of this domain is a way of re-using a check > constraint across the entire schema. This check constraint applies to all > columns of the domain type. I’ve also created a custom range type to extend > from this domain, which inherits the check. > > I am not asserting that the Postgres TIMESTAMP WITH TIME ZONE gets > anything wrong. I am just observing that this data type allows clients to > insert timestamps with arbitrary zone offsets. This is the behavior I want > to prevent. > > Note that I am ignorant of how Postgres would store this under the covers > - for all I know Postgres could be storing the timestamp in UTC in addition > to the offset. > > So I will take your advice and seek clarification from the Postgres > mailing list before moving on.
Very interesting, thanks for explaining, I'd be very curious to learn if your approach will be judged viable on that Postgres mailing list. Indeed, domains are a kind of "shared check constraint", and as such, extremely useful. But I would not have thought them to be able to validate client time zone offsets. If they're able to do that, all the better and I would stand corrected. Do note that eventually, jOOQ will have to provide better support for domains from PostgreSQL. It's just not been a priority (yet). -- You received this message because you are subscribed to the Google Groups "jOOQ User Group" group. To unsubscribe from this group and stop receiving emails from it, send an email to [email protected]. For more options, visit https://groups.google.com/d/optout.
