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-16 13:52:39 |
Message-ID: | 8977CB36860C5843884E0A18D8747B036B9B4043@szxeml558-mbs.china.huawei.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | 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.
Regards,
Hari babu.
From | Date | Subject | |
---|---|---|---|
Next Message | Chris Travers | 2013-09-16 13:57:19 | Re: Proposal: json_populate_record and nested json objects |
Previous Message | Boszormenyi Zoltan | 2013-09-16 13:51:23 | Proposal: UPDATE/DELETE ... WHERE OFFSET n OF cursor_name, was: Re: New ECPG idea, was: Re: ECPG FETCH readahead |