From: | Haribabu kommi <haribabu(dot)kommi(at)huawei(dot)com> |
---|---|
To: | Rushabh Lathia <rushabh(dot)lathia(at)gmail(dot)com> |
Cc: | Rushabh Lathia <rushabh(dot)lathia(at)enterprisedb(dot)com>, PostgreSQL Hackers <pgsql-hackers(at)postgresql(dot)org> |
Subject: | Re: insert throw error when year field len > 4 for timestamptz datatype |
Date: | 2013-09-17 12:53:48 |
Message-ID: | 8977CB36860C5843884E0A18D8747B036B9B4B4F@szxeml558-mbs.china.huawei.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-hackers |
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<mailto: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.
Regards,
Hari babu.
From | Date | Subject | |
---|---|---|---|
Next Message | Alexander Korotkov | 2013-09-17 13:04:19 | Re: Fix picksplit with nan values |
Previous Message | Fujii Masao | 2013-09-17 12:52:59 | Re: Patch for fail-back without fresh backup |