On Sun, Jul 19, 2009 at 01:20:27PM -0400, James B. Byrne wrote: > I was sending off a where clause that looked somewhat like this: > > WHERE 'date_of_interest' <= 2009-07-18 > > Now, as the date of interest was, in all but one case, prior to 1970 > this appeared to work. However, in one case the date was in 1999 > and this was the record that exposed the error. I extrapolated, > perhaps incorrectly, from my *nix experience and inferred that the > timestamp value 'date_of_interest' used a *nix epoch time value and > that the expression 2009-07-18 was resolving to 1984 at the DBMS. > If true then this would account for the behaviour observed. I assume you're not using PG 8.3 or later? This would catch this sort of mistake and complain that you're trying to compare an integer and a date. The reason you're getting 1984 is because the numeric expression (2009 - 7 - 18) equals 1984. PG would then (silently) coerce both sides of the comparison to a common type (i.e. text) and then end up doing a lexicographical comparison on the two. This is the reason that PG started complaining about doing this silent casting and forcing users to explicitly say that this is what they want. -- Sam http://samason.me.uk/ -- Sent via pgsql-general mailing list (pgsql-general@xxxxxxxxxxxxxx) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
