Re: Date select question...

I have the following table:

Table "public.test"
Column | Type | Modifiers
-----------+------+-----------
test_date | date |

with the following data:

test_date
------------
2004-10-31
2004-11-01
(2 rows)

Why does the query:

select * from test where test_date between '20041001' and '20041101';

return TWO rows:

test_date
------------
2004-10-31
2004-11-01
(2 rows)

and the query:

select * from test where test_date between 20041001 and 20041101;
test_date
------------
2004-10-31
(1 row)

return just ONE row:

test_date
------------
2004-10-31
(1 row)

regards,

Lance Munslow
Software Development
Travel Technology Systems Ltd

This Email may contain information of a confidential and/or privileged
nature.
The information transmitted is intended only for the benefit of the person
or entity to which it is addressed and must not be copied or forwarded
without the sender's express permission.
This Email does not reflect the views or opinions of Travel Technology
Systems Ltd.
This Email is without prejudice.
This Email does not constitute an agreement either explicitly or implicitly
with Travel Technology Systems Ltd.

Lance Munslow <lance(dot)munslow(at)ttsltd(dot)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