Text to interval conversion can silently truncate data

Jack Christensen <jack@jackchristensen.com> writes:

jack=# select '1.51 years'::interval = '1.52 years'::interval;
?column?
----------
t
(1 row)

This is surprising. Once I looked at the C code for Interval it makes
more sense given that it cannot represent fractional years, months, or
days. Wouldn't it make more sense to raise an invalid input error than
to silently truncate data?

Well, "1.5 years" is perfectly valid (it means 18 months). So I don't
think rejecting fractional years altogether would be a good idea.

Really your complaint is not very different from complaining because
1.23456789::float4 = 1.234567891::float4. It's just a property of the
datatype that certain inputs aren't represented exactly. Another example
that stays within the interval datatype is

regression=# select '1.000001 seconds'::interval;
interval
-----------------
00:00:01.000001
(1 row)

regression=# select '1.0000001 seconds'::interval;
interval
----------
00:00:01
(1 row)

I doubt people would thank us for rejecting that second input altogether.

regards, tom lane

--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general