Skip site navigation (1) Skip section navigation (2)

issue with an assembled date field

From: Chris Bowlby <excalibur(at)accesswave(dot)ca>
To: pgsql-general(at)postgresql(dot)org
Subject: issue with an assembled date field
Date: 2008-02-29 17:09:02
Message-ID: 1204304942.2839.38.camel@efnisien.dreadnet.org (view raw or flat)
Thread:
Lists: pgsql-general
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?

Responses

pgsql-general by date

Next:From: Brent FriedmanDate: 2008-02-29 17:13:58
Subject: Re: Insert vs Update syntax
Previous:From: Marko KreenDate: 2008-02-29 17:07:14
Subject: Re: partitioning using dblink

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group