Re: insert throw error when year field len > 4 for timestamptz datatype

Sorry for delay in reply.

On Tue, Sep 17, 2013 at 6:23 PM, Haribabu kommi
<haribabu(dot)kommi(at)huawei(dot)com>wrote:

> On Tue, 17 September 2013 14:33 Rushabh Lathia wrote:****
>
> >>On Mon, Sep 16, 2013 at 7:22 PM, Haribabu kommi <
> haribabu(dot)kommi(at)huawei(dot)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');****
>
> >>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.****
>
> ** **
>
> I am getting some other failures with the updated patch also, please check
> the following tests.****
>
> ** **
>
> select date 'January 8, 19990';****
>
> select timestamptz 'January 8, 199910 01:01:01 IST';****
>
> INSERT INTO TIMESTAMPTZ_TST VALUES(4, '10001 SAT 8 MAR 10:10:10 IST');****
>
> ** **
>
> you can get the test scripts from regress test files of date.sql,
> timetz.sql, timestamp.sql and timestamptz.sql****
>
> and modify according to the patch for verification.****
>
> ** **
>
> I feel changing the year value to accept the length (>4) is not simple. **
> **
>
> So many places the year length crossing more than length 4 is not
> considered.****
>
> Search in the code with “yyyy” and correct all related paths.
>

Right, changing the year value to accept the length (>4) is not simple
because so
many places the year length crossing plus most of the please having
assumption
that it will be always <4.

Tried to fix issue more couple of places but I don't feeling like its
always going
to be safe to assume that we covered all path.

Still looking and wondering if we can do change in any simple place or
whether
we can find any other smarter way to fix the issue.

> ****
>
> ** **
>
> Regards,****
>
> Hari babu.****
>
> ** **
>

--
Rushabh Lathia