Re: PG handling of date expressions

"James B. Byrne" <byrnejb(at)harte-lyne(dot)ca> writes:
> My error was in not encapsulating a programmically inserted date
> string within quotation marks. This meant that I was sending off a
> where clause that looked somewhat like this:

> WHERE 'date_of_interest' <= 2009-07-18

Ah. You are apparently using a pre-8.3 PG release, and you have just
stumbled across one of the commoner sorts of errors that prompted us
to remove a lot of implicit casts in 8.3. The folks who screamed about
us doing that apparently hadn't yet made this type of mistake, or they'd
not have groused so much.

First off, the interpretation of the righthand side is obvious: it's a
numeric expression yielding the value 1984 (2009 minus 7 minus 18).
The parser is then faced with an expression
date_column <= integer
There is no "<=" operator that compares dates and integers. However,
there is a "text <= text" operator, and before 8.3 there were implicit
casts from date to text and from integer to text. So the parser decided
the correct interpretation of your command was
date_column::text <= '1984'::text
which indeed matches your description of how it acted. It would have
behaved very strangely indeed if you'd been using a non-ISO datestyle,
though ...

8.3 and up will throw an error on such cases:

regression=# select current_date <= 2009-07-18;
ERROR: operator does not exist: date <= integer
LINE 1: select current_date <= 2009-07-18;
^
HINT: No operator matches the given name and argument type(s). You might need to add explicit type casts.

regards, tom lane