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. Regards, Hari babu.