Re: [GENERAL] issue with an assembled date field
Martin Gainty wrote: Chris Bowlby wrote: 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 || ... You're going to have another problem in about 22 months. > Brian is right > > change substring(ilch.lot_id::text, 5, 1) and > change '01/01/0'::text || > > to > '01/01/'::text || substring(ilch.lot_id::text,4,2) That's not quite it. The data contain just the last digit of the year, not the last 2. So, unless the data itself is changed, there will still be a bit of a headache developing in 22 months time. In any case, as i said also, the syntax is incorrect: substr(ilch.lot_id::text, 5, 1) or: substring(ilch.lot_id::text FROM 5 FOR 1) b ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
Re: [GENERAL] issue with an assembled date field
Chris Bowlby <[EMAIL PROTECTED]> writes: > ERROR: invalid input syntax for type date: "200W-01-01" > the test data I am using for this example is as follows: FWIW, I don't see any problem here using that test case. Have you tried looking directly at the output of the substring function, ie select substring(ilch.lot_id::text, 5, 1) FROM my_lot_test ilch; regards, tom lane ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
Re: [GENERAL] issue with an assembled date field
Brian is right change substring(ilch.lot_id::text, 5, 1) and change '01/01/0'::text || to '01/01/'::text || substring(ilch.lot_id::text,4,2) M-- - Original Message - From: "brian" <[EMAIL PROTECTED]> To: Sent: Friday, February 29, 2008 1:11 PM Subject: Re: [GENERAL] issue with an assembled date field > Chris Bowlby wrote: > > 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 || ... > > > > You're going to have another problem in about 22 months. > > b > > ---(end of broadcast)--- > TIP 4: Have you searched our list archives? > >http://archives.postgresql.org/ > ---(end of broadcast)--- TIP 1: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly
Re: [GENERAL] issue with an assembled date field
ho Tom, Thanks that gave me the brain burp I needed to click into what was causing the root issue. On Fri, 2008-02-29 at 13:47 -0500, Tom Lane wrote: > Chris Bowlby <[EMAIL PROTECTED]> writes: > > 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) > > Well, I'd say that the failure proves that some of your data does > *not* have the year in the fifth character. > > > ERROR: invalid input syntax for type date: "01/01/0W" > > Time for some data sanitizing? > > regards, tom lane ---(end of broadcast)--- TIP 6: explain analyze is your friend
Re: [GENERAL] issue with an assembled date field
Chris Bowlby wrote:
Hi Colin,
Thanks for your response, if I remove the where clause from my example,
I also am able to execute the query with out issue, as follows:
test=# 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 limit 1;
dr_prod_date
--
2007-01-01
(1 row)
That syntax is incorrect for substring(). Use:
substr(ilch.lot_id::text, 5, 1)
or:
substring(ilch.lot_id::text FROM 5 FOR 1)
---(end of broadcast)---
TIP 4: Have you searched our list archives?
http://archives.postgresql.org/
Re: [GENERAL] issue with an assembled date field
Chris Bowlby <[EMAIL PROTECTED]> writes: > 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) Well, I'd say that the failure proves that some of your data does *not* have the year in the fifth character. > ERROR: invalid input syntax for type date: "01/01/0W" Time for some data sanitizing? regards, tom lane ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [GENERAL] issue with an assembled date field
Hi Colin,
Thanks for your response, if I remove the where clause from my example,
I also am able to execute the query with out issue, as follows:
test=# 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 limit 1;
dr_prod_date
--
2007-01-01
(1 row)
And using slashes or dashes, or even a full year specification (as shown
by my following query) still gives me the same issue, just in a
different location:
test=# select tab.dr_prod_date FROM (SELECT ('200' ||
substring(ilch.lot_id::text, 5, 1) || '-01-01')::date AS dr_prod_date
FROM my_lot_test ilch) AS tab where tab.dr_prod_date = '2/5/08' limit
1;
ERROR: invalid input syntax for type date: "200W-01-01"
the test data I am using for this example is as follows:
CREATE TABLE my_lot_test
( idSERIAL,
lot_idVARCHAR(5),
PRIMARY KEY(id));
INSERT INTO my_lot_test(lot_id) VALUES('01025');
INSERT INTO my_lot_test(lot_id) VALUES('01026');
INSERT INTO my_lot_test(lot_id) VALUES('01027');
INSERT INTO my_lot_test(lot_id) VALUES('02027');
Note that the formatting here is unique to my test, but the issue arises
with this any valid combination of string that I have tried, short and
longer.
On Fri, 2008-02-29 at 13:12 -0500, Colin Wetherbee wrote:
> Chris Bowlby wrote:
> > 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"
>
> Using arbitrary slashes can confuse a lot of things, although I'm not
> sure why you're getting a W there. Perhaps you could send us some test
> data?
>
> The following works fine for me on 8.1.10.
>
> cww=# create table foo (mydate text);
> CREATE TABLE
> cww=# insert into foo values ('1');
> INSERT 0 1
> cww=# insert into foo values ('2');
> INSERT 0 1
> cww=# insert into foo values ('3');
> INSERT 0 1
> cww=# select ('200' || substring(mydate, 5, 1) || '-01-01')::date from foo;
> date
>
> 2001-01-01
> 2002-01-01
> 2003-01-01
> (3 rows)
>
> Colin
---(end of broadcast)---
TIP 9: In versions below 8.0, the planner will ignore your desire to
choose an index scan if your joining column's datatypes do not
match
Re: [GENERAL] issue with an assembled date field
Chris Bowlby wrote: 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 || ... You're going to have another problem in about 22 months. b ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org/
Re: [GENERAL] issue with an assembled date field
Chris Bowlby wrote:
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"
Using arbitrary slashes can confuse a lot of things, although I'm not
sure why you're getting a W there. Perhaps you could send us some test
data?
The following works fine for me on 8.1.10.
cww=# create table foo (mydate text);
CREATE TABLE
cww=# insert into foo values ('1');
INSERT 0 1
cww=# insert into foo values ('2');
INSERT 0 1
cww=# insert into foo values ('3');
INSERT 0 1
cww=# select ('200' || substring(mydate, 5, 1) || '-01-01')::date from foo;
date
2001-01-01
2002-01-01
2003-01-01
(3 rows)
Colin
---(end of broadcast)---
TIP 9: In versions below 8.0, the planner will ignore your desire to
choose an index scan if your joining column's datatypes do not
match
[GENERAL] issue with an assembled date field
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
