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
