Re: Date select question...

Lance Munslow <lance.munslow@ttsltd.com> writes:
> [ why do these act differently: ]
> select * from test where test_date between '20041001' and '20041101';
> select * from test where test_date between 20041001 and 20041101;

In the latter case the constants are integers, not dates.  IMHO the best
policy would be for Postgres to reject it as an invalid operation, since
there are no date-vs-integer comparison operators.  What you are
actually getting though is an implicit coercion of both sides to text
followed by a textual comparison, as you can see if you look at EXPLAIN
output:

regression=# create table test(test_date date);
CREATE TABLE
regression=# explain select * from test where test_date between '20041001' and '20041101';
     QUERY PLAN
 
-------------------------------------------------------------------------------------Seq Scan on test
(cost=0.00..25.00rows=5 width=4)  Filter: ((test_date >= '2004-10-01'::date) AND (test_date <= '2004-11-01'::date))
 
(2 rows)

regression=# explain select * from test where test_date between 20041001 and 20041101;
       QUERY PLAN
 
-------------------------------------------------------------------------------------------------Seq Scan on test
(cost=0.00..30.00rows=5 width=4)  Filter: (((test_date)::text >= '20041001'::text) AND ((test_date)::text <=
'20041101'::text))
(2 rows)

I've been harping for awhile on the notion that having all these implicit
cross-type-category coercions to text is Evil And Dangerous, and this is
another example in support of that theory.  But I fully expect a lot of
people to scream loudly if we disable these implicit coercions.  You can
bet there is someone out there who thinks he should be able to doselect 'Today is ' || current_date;
without having to write an explicit cast to text.
        regards, tom lane