Hi All,
I am currently running into an issue with a query and would like to get
some assistance if possible.
The PostgreSQL version I am running is 8.0.11 64-Bit, under SuSE Linux
Enterprise Server 9 SP3
I am converting an encoded field (lot_id) into a date field, the 5
character of every lot_id is always the year and as such I need to
extract the year using the following function:
substring(ilch.lot_id::text, 5, 1)
I am not worried about month or day as it is not used in what I need to
do, which is why I am using '01/01' for my main concatenation:
'01/01/0'::text || ...
The sample test query I am using is as follows:
test=# select tab.dr_prod_date FROM
test-# (SELECT ('01/01/0'::text || substring(ilch.lot_id::text, 5,
1))::date AS dr_prod_date FROM my_lot_test ilch) AS tab
test-# where tab.dr_prod_date = '2/5/08' limit 1;
ERROR: invalid input syntax for type date: "01/01/0W"
this query is the end result of a lot of smaller queries that I was
using to narrow down where I was running into the error. As such, my
thoughts were that if I ensured the field was properly converted into a
date before a comparison was run in the where clause, I would be able to
by pass this issue, but I am completely stumped as to what is going on.
The explain below indicates to me that I am correct in assuming the
concatenated date is properly converted before the comparison, yet the
issue still remains.
test=# explain select tab.dr_prod_date FROM (SELECT ('01/01/0'::text ||
substring(ilch.lot_id::text, 5, 1))::date AS dr_prod_date FROM
my_lot_test ilch) AS tab where tab.dr_prod_date = '2/5/08' limit 1;
QUERY
PLAN
-------------------------------------------------------------------------------------------------------
Limit (cost=0.00..6.26 rows=1 width=14)
-> Seq Scan on my_lot_test ilch (cost=0.00..17092.90 rows=2731
width=14)
Filter: ((('01/01/0'::text || "substring"((lot_id)::text, 5,
1)))::date = '2008-02-05'::date)
(3 rows)
can anyone with more experience then me see where the issue might be
arising?
---------------------------(end of broadcast)---------------------------
TIP 6: explain analyze is your friend
