interval_scale not work as expected?
Hi.
(
SELECT interval(0) '1 day 01:23:45.6789'
union all
SELECT interval(1) '1 day 01:23:45.6789'
union all
SELECT interval(2) '1 day 01:23:45.6789'
union all
SELECT interval(3) '1 day 01:23:45.6789'
union all
SELECT interval(4) '1 day 01:23:45.6789'
)
EXCEPT all
(
SELECT pg_catalog.interval('1 day 01:23:45.6789'::interval,2147418112)
union all
SELECT pg_catalog.interval('1 day 01:23:45.6789'::interval,2147418113)
union all
SELECT pg_catalog.interval('1 day 01:23:45.6789'::interval,2147418114)
union all
SELECT pg_catalog.interval('1 day 01:23:45.6789'::interval,2147418115)
union all
SELECT pg_catalog.interval('1 day 01:23:45.6789'::interval,2147418116)
);
https://dbfiddle.uk/zT8OByj1
the above works even in postgres 9.6. I debugged, then found out these
magic values like 2147418112.
I thought:
SELECT pg_catalog.interval('1 day 01:23:45.6789'::interval, 0)
is same as
SELECT interval(0) '1 day 01:23:45.6789'
is this a bug in AdjustIntervalForTypmod?
jian he <jian.universality@gmail.com> writes:
I thought:
SELECT pg_catalog.interval('1 day 01:23:45.6789'::interval, 0)
is same as
SELECT interval(0) '1 day 01:23:45.6789'
[ shrug ] No, it isn't. Interval typmods have to carry a lot
more than just the fractional precision, because of all the
weird syntactic baggage that the SQL spec has for interval
types (i.e., YEAR TO MONTH and other options). timestamp.h
has (some of) the details about what gets packed into an
interval typmod.
Even with simpler types, there generally isn't a one-to-one
correlation between user-visible precision and the encoded
typmod.
regards, tom lane