interval precision oddness
When you create a column with a plain "interval" column, the typmod is
set to -1 and the information schema reports this as 6, because that's
what the internal default value is (see _pg_datetime_precision
function). But when you create a column such as "interval year to
month"), the typmod is actually the bit encoding of "year to month" in
the higher 16 bits and 65535 in the lower 16 bits, and so the
information schema reports the precision as 65535, whereas the actual
behavior still corresponds to a precision of 6.
I guess this could be seen as a reporting issue. We could adjust
_pg_datetime_precision to map 65535 to 6, just like -1 is mapped to 6.
Or is there anything else wrong here?
Peter Eisentraut <peter_e@gmx.net> writes:
When you create a column with a plain "interval" column, the typmod is
set to -1 and the information schema reports this as 6, because that's
what the internal default value is (see _pg_datetime_precision
function). But when you create a column such as "interval year to
month"), the typmod is actually the bit encoding of "year to month" in
the higher 16 bits and 65535 in the lower 16 bits, and so the
information schema reports the precision as 65535, whereas the actual
behavior still corresponds to a precision of 6.
I guess this could be seen as a reporting issue. We could adjust
_pg_datetime_precision to map 65535 to 6, just like -1 is mapped to 6.
Or is there anything else wrong here?
No, it sounds like the information_schema function didn't get the memo
about what that meant. See INTERVAL_FULL_RANGE, INTERVAL_FULL_PRECISION
macros and usage thereof, esp. intervaltypmodout.
regards, tom lane