From: Tom Lane <tgl@sss.pgh.pa.us>
To: Lance Munslow <lance.munslow@ttsltd.com>
Cc: 'pgsql-sql@postgresql.org' <pgsql-sql@postgresql.org>
Subject: Re: Date select question...
Date: Fri, 09 Jan 2004 00:49:45 -0500
Message-ID: <20967.1073627385@sss.pgh.pa.us> (raw)
In-Reply-To: <1536369C345BD4118148000629C9833D57EAB5@nifty.preston.traveltech.co.uk>
References: <1536369C345BD4118148000629C9833D57EAB5@nifty.preston.traveltech.co.uk>
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
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-sql@postgresql.org
Cc: tgl@sss.pgh.pa.us, lance.munslow@ttsltd.com
Subject: Re: Date select question...
In-Reply-To: <20967.1073627385@sss.pgh.pa.us>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox