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
