To: Lance Munslow <lance.munslow@ttsltd.com>
Cc: "'pgsql-sql@postgresql.org'" <pgsql-sql@postgresql.org>
Subject: Re: Date select question... 
In-reply-to: 
 <1536369C345BD4118148000629C9833D57EAB5@nifty.preston.traveltech.co.uk>
References: 
 <1536369C345BD4118148000629C9833D57EAB5@nifty.preston.traveltech.co.uk>
Comments: In-reply-to Lance Munslow <lance.munslow@ttsltd.com>
	message dated "Thu, 08 Jan 2004 13:13:58 +0000"
Date: Fri, 09 Jan 2004 00:49:45 -0500
Message-ID: <20967.1073627385@sss.pgh.pa.us>
From: Tom Lane <tgl@sss.pgh.pa.us>

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.00 rows=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.00 rows=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 do
	select 'Today is ' || current_date;
without having to write an explicit cast to text.

			regards, tom lane