Re: insert throw error when year field len > 4 for timestamptz datatype - Mailing list pgsql-hackers

From Rushabh Lathia
Subject Re: insert throw error when year field len > 4 for timestamptz datatype
Date
Msg-id CAGPqQf24_hN-LadZU4izK3upWgmtc2CRqJ9-vvyXmaHQSGq1fw@mail.gmail.com
Whole thread Raw
In response to Re: insert throw error when year field len > 4 for timestamptz datatype  (Haribabu kommi <haribabu.kommi@huawei.com>)
Responses Re: insert throw error when year field len > 4 for timestamptz datatype
List pgsql-hackers



On Mon, Sep 16, 2013 at 7:22 PM, Haribabu kommi <haribabu.kommi@huawei.com> wrote:

On 14 August 2013 Rushabh Lathia wrote:

 

>postgres=# create table test ( a timestamptz);

>CREATE TABLE

 

>-- Date with year 1000

>postgres=#  insert into test values ( 'Sat Mar 11 23:58:48 1000 IST');

>INSERT 0 1

 

>-- Now try with year 10000 it will return error

>postgres=#  insert into test values ( 'Sat Mar 11 23:58:48 10000 IST');

>ERROR:  invalid input syntax for type timestamp with time zone: "Sat Mar 11 23:58:48 10000 IST" 

>LINE 1: insert into test values ( 'Sat Mar 11 23:58:48 10000 IST');

 

>here error coming from timestamptz_in() -> datefields_to_timestamp() ->

>DecodeDateTime() stack.

 

>Looking more at the DecodeDateTime() function, here error coming while trying

>to Decode year field which is 10000 in the our test. For year field ftype is

>DTK_NUMBER, and under DTK_NUMBER for this case if drop in to following condition:

 

>else if (flen > 4)

>{

>dterr = DecodeNumberField(flen, field[i], fmask,

> &tmask, tm,

> fsec, &is2digits);

>if (dterr < 0)

>return dterr;

>}

 

>because flen in out case flen is 5 (10000).

 

>As per the comment above DecodeNumberField(), it interpret numeric string as a

>concatenated date or time field. So ideally we should be into DecodeNumberField

>function only with (fmask & DTK_DATE_M) == 0 or (fmask & DTK_TIME_M) == 0,

>right ??

 

>So, I tried the same and after that test working fine.

 

>PFA patch and share your input/suggestions.

 

Patch applies cleanly to HEAD. As this patch tries to improve in inserting the date of the year value to be more than 4 in length.

But it didn’t solve all the ways to insert the year field more than 4 in length. Please check the following test.

 

 

postgres=# insert into test values ('10001010 10:10:10 IST');

INSERT 0 1

postgres=# insert into test values ('100011010 10:10:10 IST');

ERROR:  invalid input syntax for type timestamp with time zone: "100011010 10:10:10 IST" at character 26

STATEMENT:  insert into test values ('100011010 10:10:10 IST');

ERROR:  invalid input syntax for type timestamp with time zone: "100011010 10:10:10 IST"

LINE 1: insert into test values ('100011010 10:10:10 IST');

                                                            ^

 

I feel it is better to provide the functionality of inserting year field more than 4 in length in all flows.


+1. Nice catch.

Here is the latest version of patch which handles the functionality in all flows. 
Could you test it and share you comments.

Thanks,
Rushabh Lathia

Attachment

pgsql-hackers by date:

Previous
From: Alexander Korotkov
Date:
Subject: Re: GIN improvements part 1: additional information
Next
From: Hannu Krosing
Date:
Subject: Re: record identical operator