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

On Tue, 17 September 2013 14:33 Rushabh Lathia wrote:

>>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');

>>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.

 

Regards,

Hari babu.