Re: Restricted interval data type - Mailing list pgsql-bugs

Robert Lichtenberger <r.lichtenberger@synedra.com> writes:
> The documentation at
> https://www.postgresql.org/docs/11/datatype-datetime.html says:
> "The |interval| type has an additional option, which is to restrict the
> set of stored fields by writing one of these phrases:"
> [ but it doesn't restrict high-order fields ]

There's an offhand statement about this down in 8.5.4 "Interval Input":

    When writing an interval constant with a fields specification,
    ... field values “to the right” of the least significant field allowed
    by the fields specification are silently discarded. For example,
    writing INTERVAL '1 day 2:03:04' HOUR TO MINUTE results in dropping
    the seconds field, but not the day field.

There's more info about the rationale for that in the source code:

         * Our interpretation of intervals with a limited set of fields is
         * that fields to the right of the last one specified are zeroed out,
         * but those to the left of it remain valid.  Thus for example there
         * is no operational difference between INTERVAL YEAR TO MONTH and
         * INTERVAL MONTH.  In some cases we could meaningfully enforce that
         * higher-order fields are zero; for example INTERVAL DAY could reject
         * nonzero "month" field.  However that seems a bit pointless when we
         * can't do it consistently.  (We cannot enforce a range limit on the
         * highest expected field, since we do not have any equivalent of
         * SQL's <interval leading field precision>.)  If we ever decide to
         * revisit this, interval_support will likely require adjusting.
         *
         * Note: before PG 8.4 we interpreted a limited set of fields as
         * actually causing a "modulo" operation on a given value, potentially
         * losing high-order as well as low-order information.  But there is
         * no support for such behavior in the standard, and it seems fairly
         * undesirable on data consistency grounds anyway.  Now we only
         * perform truncation or rounding of low-order fields.

Possibly someday somebody will get excited enough about this to
try to make it more SQL-compliant, but I can't really see any
point in that personally.  I can see the point of rounding off
an interval to seconds, or minutes, or whatever you think the
minimum useful resolution is for your application.  But what's
the point of throwing errors for (or worse, silently dropping)
values that are "too large"?  Note that in our implementation,
limiting the range would not save any storage space, so that
possible argument doesn't apply.

Another issue is that given our internal representation of
months, days, and seconds, it's not terribly clear what a limited
field list maps to anyhow.  For instance, if we have INTERVAL DAY
TO SECOND, we probably ought to reject nonzero months, but should
we complain if the day field exceeds 30?

If you do have a range constraint for some reason, you'd probably
be better off enforcing it as a CHECK constraint, which'd allow
a much more precise specification of what you want than a
SQL-style field restriction could manage.

            regards, tom lane