Re: to_timestamp docs

On Thu, May 2, 2019 at 12:49:23AM +0300, Alexander Korotkov wrote:
> On Wed, May 1, 2019 at 11:20 PM Arthur Zakirov <a(dot)zakirov(at)postgrespro(dot)ru> wrote:
> > Hello,
> > Not sure if we need some additional checks here if FX is set.
>
> I'd like to add that this behavior is not new in 12. It was the same before.

Agreed, but since we are looking at it, let's document it.

> > > It seems DD and YYYY (as numerics?) in FX mode eat trailing whitespace,
> > > while MON does not? Also, I used these queries to determine it is
> > > "trailing" whitespace that "FXMON" controls:
> > >
> > > SELECT to_timestamp('JUL JUL JUL','MON_FXMON_MON');
> > > to_timestamp
> > > ---------------------------------
> > > 0001-07-01 00:00:00-04:56:02 BC
> > >
> > > SELECT to_timestamp('JUL JUL JUL','MON_FXMON_MON');
> > > ERROR: invalid value " J" for "MON"
> > > DETAIL: The given value did not match any of the allowed values for this field.
> >
> > The problem here is that you need to specify FX only once and at beginning of
> > the format string. It is stated in the documentation:
> >
> > "FX must be specified as the first item in the template."
> >
> > It works globally (but only for remaining string if you don't put it
> > at the beginning)
> > and you can set it only once. For example:
> >
> > =# SELECT to_timestamp('JUL JUL JUL','FXMON_MON_MON');
> > ERROR: invalid value " J" for "MON"
> > DETAIL: The given value did not match any of the allowed values for this field.
>
> Actually, FX takes effect on subsequent format patterns. This is not
> documented, but it copycats Oracle behavior. Sure, normally FX should
> be specified as the first item. We could document current behavior or
> restrict specifying FX not as first item. This is also not new in 12,
> so documenting current behavior is better for compatibility.

Agreed. Since is it pre-12 behavior, I suggest we just document it and
not change it.

--
Bruce Momjian <bruce(at)momjian(dot)us> http://momjian.us
EnterpriseDB http://enterprisedb.com

+ As you are, so once was I. As I am, so you will be. +
+ Ancient Roman grave inscription +