Re: to_timestamp docs

On Wed, May 1, 2019 at 10:01:50AM -0400, Tom Lane wrote:
> Bruce Momjian <bruce(at)momjian(dot)us> writes:
> > I don't think the changes made in PG 12 are documented accurately.
>
> That code is swapped out of my head at the moment, but it looks
> to me like the para before the one you changed is where we discuss
> the behavior for whitespace. I'm not sure that this change is
> right, or an improvement, in the context of both paras.

Thanks. I think I see the sentence you are thinking of:

<function>to_timestamp</function> and <function>to_date</function>
skip multiple blank spaces at the beginning of the input string
and around date and time values unless the <literal>FX</literal>
option is used.

However, first, it is unclear what 'skip' means here, i.e., does it mean
multiple blank spaces become a single space, or they are ignored. That
should be clarified, though I am unclear if that matters based on how
separators are handled. Also, I think "blank spaces" should be
"whitespace".

Second, I see inconsistent behaviour around the use of FX for various
patterns, e.g.:

SELECT to_timestamp('5 1976','FXDD_FXYYYY');
to_timestamp
------------------------
1976-01-05 00:00:00-05

SELECT to_timestamp('JUL JUL','FXMON_FXMON');
to_timestamp
---------------------------------
0001-07-01 00:00:00-04:56:02 BC

SELECT to_timestamp('JUL JUL','FXMON_FXMON');
ERROR: invalid value " " for "MON"
DETAIL: The given value did not match any of the allowed values for this field.

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.

Once we figure out how it is behaving I think we can pull together the
FX text above to reference the separator text below.

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