Thread: Restricted interval data type

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:"

I have tried this:

aimdevelprocess=# CREATE TABLE rli_foo (x       interval hour to second (3));
       CREATE TABLE
       aimdevelprocess=# \d rli_foo
                                    Tabelle »public.rli_foo«
        Spalte |            Typ             | Sortierfolge | NULL       erlaubt? | Vorgabewert 

--------+----------------------------+--------------+---------------+-------------
        x      | interval hour to second(3) |                    |               | 
       
       aimdevelprocess=# INSERT INTO rli_foo(x) VALUES('1 year 2 days       12:13:14.1516');
       INSERT 0 1
       aimdevelprocess=# SELECT * FROM rli_foo ;
                    x              
       ----------------------------
        1 year 2 days 12:13:14.152
       (1 Zeile)
       
     

This is surprising to me. I had expected postgres to reject the '1 year 2 days 12:13:14.1516' literal as it does not comply with the restriction "hour to second".

On the other hand precision seems to work (.1516 gets rounded to .152).

There's not much information on restricted intervals to be found so it seems I've stumbled across some exotic part of postgresql (or SQL in general?).

Best regards,

Robert

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