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

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 ('00001');
INSERT 0 1
cww=# insert into foo values ('00002');
INSERT 0 1
cww=# insert into foo values ('00003');
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

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
( id SERIAL,
lot_id VARCHAR(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.

Chris Bowlby <excalibur@accesswave.ca> 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

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)

ho Tom,

Thanks that gave me the brain burp I needed to click into what was
causing the root issue.

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" <brian@zijn-digital.com>
To: <pgsql-general@postgresql.org>
Sent: Friday, February 29, 2008 1:11 PM
Subject: Re: [GENERAL] issue with an assembled date field

Chris Bowlby <excalibur@accesswave.ca> 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

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

CREATE TABLE "fav5"."batchnumber" (
"batchnumberid" SERIAL NOT NULL,
"processtype" SMALLINT NOT NULL,
"termloc" CHAR(3) NOT NULL,
"batchno" INTEGER NOT NULL
) WITHOUT OIDS;

With the above table definition, is batchnumberid by default also defined as the primary key or do I still need to define a separate Primary Key constraint?

Best Regards,

Michael Gould
All Coast Intermodal Services, Inc.
904-226-0978

On Feb 29, 2008, at 2:28 PM, Mike Gould wrote:

CREATE TABLE "fav5"."batchnumber" (
"batchnumberid" SERIAL NOT NULL,
"processtype" SMALLINT NOT NULL,
"termloc" CHAR(3) NOT NULL,
"batchno" INTEGER NOT NULL
) WITHOUT OIDS;
With the above table definition, is batchnumberid by default also
defined as the primary key or do I still need to define a separate
Primary Key constraint?

No, you need to add the PRIMARY KEY bit which means you can also drop
the NOT NULL bit for that field.

Erik Jones

DBA | Emma®
erik@myemma.com
800.595.4401 or 615.292.5888
615.292.0777 (fax)

Emma helps organizations everywhere communicate & market in style.
Visit us online at http://www.myemma.com

On Fri, Feb 29, 2008 at 03:28:11PM -0500, Mike Gould wrote:

CREATE TABLE "fav5"."batchnumber" (
"batchnumberid" SERIAL NOT NULL,
"processtype" SMALLINT NOT NULL,
"termloc" CHAR(3) NOT NULL,
"batchno" INTEGER NOT NULL
) WITHOUT OIDS;
With the above table definition, is batchnumberid by default also defined as the primary key or do I still need to define a separate Primary Key constraint?

wouldn't it be faster to simply try it?

it will be just a simple column. you have to add primary key
separately.

depesz

--
quicksil1er: "postgres is excellent, but like any DB it requires a
highly paid DBA. here's my CV!" :)
http://www.depesz.com/ - blog dla ciebie (i moje CV)

On Fri, Feb 29, 2008 at 2:28 PM, Mike Gould <mgould@allcoast.net> wrote:

CREATE TABLE "fav5"."batchnumber" (
"batchnumberid" SERIAL NOT NULL,
"processtype" SMALLINT NOT NULL,
"termloc" CHAR(3) NOT NULL,
"batchno" INTEGER NOT NULL
) WITHOUT OIDS;

With the above table definition, is batchnumberid by default also defined as
the primary key or do I still need to define a separate Primary Key
constraint?

Once upon a time a serial type was assumed to be a PK (or was that
unique not null???) automagically. However, this was considered bad
form since some uses of sequence did not need those extra settings.

Serial is JUST serial nowadays, and has been for quite some time.