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

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.