> is there any reason why the order of operations of the following query
> would matter?
Yes. But not a good one :(
> diw=# select interval '40 years' + date '2001-01-01' as test;
> ---------------------
> 2001-01-01 00:00:00
This is relying on the interval being turned into a time field, which
gets modulo'd by 24 hours. We should probably check the conversion and
reject anything which needs modulo to fit into 24 hours, and we should
probably not allow this particular implicit coersion by defining an
explicit operator for these two data types in this order.
We used to have the ability to reorder the arithmetic to get the correct
answer, but afaicr that was removed since we were apparently misusing
fields to accomplish this.
> also, is there a difference between:
> "interval('40 years') " and "interval '40 years' " ?
> or
> "date('2001-01-01')" and "date '2001-01-01' " ?
> diw=# select date('2001-01-01') + interval('40 years') as test;
> ERROR: parser: parse error at or near "'"
In 7.2.x, interval(int) is the specifier for a data type, not a function
call. The same new feature is available for timestamp. So you can't use
the function call form for type conversion anymore (at least not without
gymnastics). Use
interval '40 years'
instead.
- Thomas