Re: CHECK constraint fails when it's not supposed to

On Tue, Aug 4, 2009 at 5:49 PM, Richard Yen<dba(at)richyen(dot)com> wrote:
>
> The data reads:
>>
>> tii=# select date_start, date_end, term_length, '2009-09-03
>> 05:38:24.030331-07'::timestamptz - date_start AS new_term_length from
>> m_class where id = 2652020;
>>         date_start          |          date_end           | term_length |
>>     new_term_length
>>
>> -----------------------------+-----------------------------+-------------+--------------------------
>>  2009-03-09 11:39:14.1619-07 | 2009-04-08 11:39:14.1619-07 | 30 days     |
>> 177 days 17:59:09.868431
>

Is the machine where it's failing Windows? Windows builds have used
floating point dates in the past. Floating point arithmetic can be
funny and result in numbers that are not perfectly precise and compare
suprisingly, especially when -- as you're effectively doing here --
the you're testing for equality.

You could rebuild with 64-bit integer timestamps which represent
milliseconds precisely. 8.4 defaults to integer timestamps even on
Windows.

--
greg
http://mit.edu/~gsstark/resume.pdf