Re: Bug on version 12 ?

On Fri, May 15, 2020 at 8:08 AM PegoraroF10 <marcos(at)f10(dot)com(dot)br> wrote:

> select To_Json(Current_Timestamp);
> returns "2020-05-15T14:49:44.266342+00:00" on version 11.7 or 12.3
>
> So I have lots of JSONS which have timestamp on them.
>
> select
> JS ->> 'mydate'::text,
> to_timestamp((JS ->> 'mydate'), 'YYYY-MM-DD HH24:MI:SS')
> from (select '{"somefield": true, "otherfield": true, "mydate":
> "2020-04-02T18:26:50.941531-03:00"}'::jsonb) j(JS)
>
> This SQL works fine on 11.7 but not on 12.3 version.
>
> ERROR: invalid value "T1" for "HH24"
> Detail: Value must be an integer.
> Where: SQL function "castimmutabletimestamp" statement 1
>
> Is that a version 12 bug or a server configuration ?
>

Its a version 12 behavior change, though its somewhat unfortunate that its
covered by the imprecise:
"Adjust to_timestamp()/to_date() functions to be more forgiving of template
mismatches ..." item in the release notes.

I believe (cannot test at the moment) that the issue is that the code no
longer likes to match space template markers with non-space input, skipping
the template position altogether without moving along the input string.
You will want to change your template to use "T" which more closely matches
the input data anwyay.

Order you can, and probably should, just stop using to_timestamp and do
("JS->>'mydate)::timestamptz (which has the added benefit of keeping the
timezone information).

David J.