Bug in to_timestamp().

amul sul <sul_amul@yahoo.co.in> writes:

It's look like bug in to_timestamp() function when format string has more whitespaces compare to input string, see below:

No, I think this is a case of "input doesn't match the format string".

As a rule of thumb, using to_timestamp() for input that could be parsed
just fine by the standard timestamp input function is not a particularly
good idea. to_timestamp() is meant to deal with input that is in a
well-defined format that happens to not be parsable by timestamp_in.
This example doesn't meet either of those preconditions.

regards, tom lane

--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers

On Mon, Jun 13, 2016 at 12:12 PM, Tom Lane <tgl@sss.pgh.pa.us> wrote:

amul sul <sul_amul@yahoo.co.in> writes:

It's look like bug in to_timestamp() function when format string has more whitespaces compare to input string, see below:

No, I think this is a case of "input doesn't match the format string".

As a rule of thumb, using to_timestamp() for input that could be parsed
just fine by the standard timestamp input function is not a particularly
good idea. to_timestamp() is meant to deal with input that is in a
well-defined format that happens to not be parsable by timestamp_in.
This example doesn't meet either of those preconditions.

I think a space in the format string should skip a whitespace
character in the input string, but not a non-whitespace character.
It's my understanding that these functions exist in no small part for
compatibility with Oracle, and Oracle declines to skip the digit '1'
on the basis of an extra space in the format string, which IMHO is the
behavior any reasonable user would expect.

--
Robert Haas
EnterpriseDB: http://www.enterprisedb.com
The Enterprise PostgreSQL Company

--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers

On Monday, 13 June 2016 9:57 PM, Robert Haas <robertmhaas@gmail.com> wrote:

I think a space in the format string should skip a whitespace
character in the input string, but not a non-whitespace character.

+1, enough the benefit is we are giving correct output.

PFA patch proposing this fix.

Regards,
Amul Sul.

On Mon, Jun 13, 2016 at 12:25 PM, Robert Haas <robertmhaas@gmail.com> wrote:

On Mon, Jun 13, 2016 at 12:12 PM, Tom Lane <tgl@sss.pgh.pa.us> wrote:

amul sul <sul_amul@yahoo.co.in> writes:

It's look like bug in to_timestamp() function when format string has more whitespaces compare to input string, see below:

No, I think this is a case of "input doesn't match the format string".

As a rule of thumb, using to_timestamp() for input that could be parsed
just fine by the standard timestamp input function is not a particularly
good idea. to_timestamp() is meant to deal with input that is in a
well-defined format that happens to not be parsable by timestamp_in.
This example doesn't meet either of those preconditions.

I think a space in the format string should skip a whitespace
character in the input string, but not a non-whitespace character.
It's my understanding that these functions exist in no small part for
compatibility with Oracle, and Oracle declines to skip the digit '1'
on the basis of an extra space in the format string, which IMHO is the
behavior any reasonable user would expect.

So Amul and I are of one opinion and Tom is of another. Anyone else
have an opinion?

--
Robert Haas
EnterpriseDB: http://www.enterprisedb.com
The Enterprise PostgreSQL Company

--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers

On Mon, Jun 20, 2016 at 8:19 AM, Robert Haas <robertmhaas@gmail.com> wrote:

On Mon, Jun 13, 2016 at 12:25 PM, Robert Haas <robertmhaas@gmail.com>
wrote:

On Mon, Jun 13, 2016 at 12:12 PM, Tom Lane <tgl@sss.pgh.pa.us> wrote:

amul sul <sul_amul@yahoo.co.in> writes:

It's look like bug in to_timestamp() function when format string has

more whitespaces compare to input string, see below:

No, I think this is a case of "input doesn't match the format string".

As a rule of thumb, using to_timestamp() for input that could be parsed
just fine by the standard timestamp input function is not a particularly
good idea. to_timestamp() is meant to deal with input that is in a
well-defined format that happens to not be parsable by timestamp_in.
This example doesn't meet either of those preconditions.

I think a space in the format string should skip a whitespace
character in the input string, but not a non-whitespace character.
It's my understanding that these functions exist in no small part for
compatibility with Oracle, and Oracle declines to skip the digit '1'
on the basis of an extra space in the format string, which IMHO is the
behavior any reasonable user would expect.

So Amul and I are of one opinion and Tom is of another. Anyone else
have an opinion?

​At least Tom's position has the benefit of being consistent with current
behavior. The current implementation doesn't actually care what literal
value you specify - any non-special character consumes a single token from
the input, period.

SELECT TO_TIMESTAMP('2016-06-13 15:43:36', 'YYYY/MM/DD--HH24:MI:SS');
SELECT TO_TIMESTAMP('2016-06-13 15:43:36', 'YYYY/MM/DD-HH24:MI:SS');

Both the above exhibit the same behavior as if you used a space instead of
the hyphen as the group separator.

The documentation should be updated to make this particular dynamic more
clear.

I don't see changing the general behavior of these "date formatting"
functions a worthwhile endeavor.​ Adding a less-liberal "parse_timestamp"
function I could get behind.

IOW, the user seems to be happy with the fact that the "/" in the date
matches his "-" but them complains that the space matches the number "1".
You don't get to have it both ways.

[re-reads the third usage note]

Or maybe you do. We already define space as a being a greedy operator
(when not used in conjunction with FX). A greedy space-space sequence
makes little sense on its face and if we are going to be helpful here we
should treat it as a single greedy space matcher.

Note that "returns an error because to_timestamp expects one space only" is
wrong - it errors because only a single space is captured and then the
attempt to parse ' JUN' using "MON" fails. The following query doesn't
fail though it exhibits the same space discrepancy (it just gives the same
"wrong" result).

SELECT TO_TIMESTAMP('2016-06-13 15:43:36', 'FXYYYY/MM/DD HH24:MI:SS');

Given that we already partially special-case the space expression I'd be
inclined to consider Robert's and Amul's position on the matter. I think
I'd redefine our treatment of space to be "zero or more" instead of "one or
more" and require that it only match a literal space in the input.

Having considered that, I'm not convinced its worth a compatibility break.
I'd much rather deprecate these <to_*> versions and write
slightly-less-liberal versions named <parse_*>.

In any case I'd called the present wording a bug. Instead:

A single space consumes a single token of input and then, unless the FX
modifier is present, consumes zero or more subsequent literal spaces.
Thus, using two spaces in a row without the FX modifier, while allowed, is
unlikely to give you a satisfactory result. The first space will consume
all available consecutive spaces so that the second space will be
guaranteed to consume a non-space token from the input.

David J.

Robert Haas <robertmhaas@gmail.com> writes:

On Mon, Jun 13, 2016 at 12:25 PM, Robert Haas <robertmhaas@gmail.com> wrote:

I think a space in the format string should skip a whitespace
character in the input string, but not a non-whitespace character.
It's my understanding that these functions exist in no small part for
compatibility with Oracle, and Oracle declines to skip the digit '1'
on the basis of an extra space in the format string, which IMHO is the
behavior any reasonable user would expect.

So Amul and I are of one opinion and Tom is of another. Anyone else
have an opinion?

I don't necessarily have an opinion yet. I would like to see more than
just an unsupported assertion about what Oracle's behavior is. Also,
how should FM mode affect this?

regards, tom lane

--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers

Tom Lane wrote:

I don't necessarily have an opinion yet. I would like to see more than
just an unsupported assertion about what Oracle's behavior is. Also,
how should FM mode affect this?

I can supply what Oracle 12.1 does:

SQL> SELECT to_timestamp('2016-06-13 15:43:36', ' YYYY/MM/DD HH24:MI:SS') AS ts FROM dual;

TS
--------------------------------
2016-06-13 15:43:36.000000000 AD

SQL> SELECT to_timestamp('2016-06-13 15:43:36', 'YYYY/MM/DD HH24:MI:SS') AS ts FROM dual;

TS
--------------------------------
2016-06-13 15:43:36.000000000 AD

SQL> SELECT to_timestamp('2016-06-13 15:43:36', 'YYYY/MM/DD HH24:MI:SS') AS ts FROM dual;

TS
--------------------------------
2016-06-13 15:43:36.000000000 AD

(to_timestamp_tz behaves the same way.)

So Oracle seems to make no difference between one or more spaces.

Yours,
Laurenz Albe

--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers

On 20.06.2016 16:36, Tom Lane wrote:

Robert Haas <robertmhaas@gmail.com> writes:

On Mon, Jun 13, 2016 at 12:25 PM, Robert Haas <robertmhaas@gmail.com> wrote:

I think a space in the format string should skip a whitespace
character in the input string, but not a non-whitespace character.
It's my understanding that these functions exist in no small part for
compatibility with Oracle, and Oracle declines to skip the digit '1'
on the basis of an extra space in the format string, which IMHO is the
behavior any reasonable user would expect.

So Amul and I are of one opinion and Tom is of another. Anyone else
have an opinion?

I don't necessarily have an opinion yet. I would like to see more than
just an unsupported assertion about what Oracle's behavior is. Also,
how should FM mode affect this?

regards, tom lane

Oracle:
SQL> SELECT TO_TIMESTAMP('2016-06-13 99:99:99', 'YYYYMMDD HH24:MI:SS')
from dual;
SELECT TO_TIMESTAMP('2016-06-13 99:99:99', 'YYYYMMDD HH24:MI:SS') from dual
*
ERROR at line 1:
ORA-01843: not a valid month

PG:

postgres=# SELECT TO_TIMESTAMP('2016-06-13 99:99:99', 'YYYYMMDD
HH24:MI:SS');
to_timestamp
------------------------
2016-01-06 14:40:39+03
(1 row)

I know about:
"These functions interpret input liberally, with minimal error checking.
While they produce valid output, the conversion can yield unexpected
results" from docs but by providing illegal input parameters we have no
any exceptions or errors about that.
I think that to_timestamp() need to has more format checking than it has
now.

Alex Ignatov
Postgres Professional: http://www.postgrespro.com
The Russian Postgres Company

--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers

On 13.06.2016 18:52, amul sul wrote:

Hi,

It's look like bug in to_timestamp() function when format string has more whitespaces compare to input string, see below:

Ex.1: Two white spaces before HH24 whereas one before input time string

postgres=# SELECT TO_TIMESTAMP('2016-06-13 15:43:36', 'YYYY/MM/DD HH24:MI:SS');
to_timestamp
------------------------
2016-06-13 05:43:36-07 <— incorrect time
(1 row)

Ex.2: One whitespace before YYYY format string

postgres=# SELECT TO_TIMESTAMP('2016/06/13 15:43:36', ' YYYY/MM/DD HH24:MI:SS');
to_timestamp
------------------------------
0016-06-13 15:43:36-07:52:58 <— incorrect year
(1 row)

If there are one or more consecutive whitespace in the format, we should skip those as long as we could get an actual field.
Thoughts?
Thanks & Regards,
Amul Sul

From docs about to_timestamp() (
https://www.postgresql.org/docs/9.5/static/functions-formatting.html)
"These functions interpret input liberally, with minimal error checking.
While they produce valid output, the conversion can yield unexpected
results. For example, input to these functions is not restricted by
normal ranges, thus to_date('20096040','YYYYMMDD') returns 2014-01-17
rather than causing an error. Casting does not have this behavior."

And it wont stop on some simple whitespace. By using to_timestamp you
can get any output results by providing illegal input parameters values:

postgres=# SELECT TO_TIMESTAMP('2016-06-13 99:99:99', 'YYYYMMDD
HH24:MI:SS');
to_timestamp
------------------------
2016-01-06 14:40:39+03
(1 row)

Alex Ignatov
Postgres Professional: http://www.postgrespro.com
The Russian Postgres Company

--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers

On Monday, 20 June 2016 8:53 PM, Alex Ignatov <a.ignatov@postgrespro.ru> wrote:

On 13.06.2016 18:52, amul sul wrote:

And it wont stop on some simple whitespace. By using to_timestamp you
can get any output results by providing illegal input parameters values:

postgres=# SELECT TO_TIMESTAMP('2016-06-13 99:99:99', 'YYYYMMDD
HH24:MI:SS');
to_timestamp
------------------------
2016-01-06 14:40:39+03

(1 row)

We do consume extra space from input string, but not if it is in format string, see below:

postgres=# SELECT TO_TIMESTAMP('2016-06-13 15:43:36', 'YYYY/MM/DD HH24:MI:SS');
to_timestamp
------------------------
2016-06-13 15:43:36-07
(1 row)

We should have same treatment for format string too.

Thoughts? Comments?

Regards,
Amul Sul

--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers

On Thu, Jun 23, 2016 at 07:41:26AM +0000, amul sul wrote:

On Monday, 20 June 2016 8:53 PM, Alex Ignatov <a.ignatov@postgrespro.ru> wrote:

On 13.06.2016 18:52, amul sul wrote:

And it wont stop on some simple whitespace. By using to_timestamp you
can get any output results by providing illegal input parameters values:

postgres=# SELECT TO_TIMESTAMP('2016-06-13 99:99:99', 'YYYYMMDD
HH24:MI:SS');
to_timestamp
------------------------
2016-01-06 14:40:39+03

(1 row)

We do consume extra space from input string, but not if it is in format string, see below:

postgres=# SELECT TO_TIMESTAMP('2016-06-13 15:43:36', 'YYYY/MM/DD HH24:MI:SS');
to_timestamp
------------------------
2016-06-13 15:43:36-07
(1 row)

We should have same treatment for format string too.

Thoughts? Comments?

Well, the user specifies the format string, while the input string comes
from the data, so I don't see having them behave the same as necessary.

--
Bruce Momjian <bruce@momjian.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 +

--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers

On 23.06.2016 16:30, Bruce Momjian wrote:

On Thu, Jun 23, 2016 at 07:41:26AM +0000, amul sul wrote:

On Monday, 20 June 2016 8:53 PM, Alex Ignatov <a.ignatov@postgrespro.ru> wrote:

On 13.06.2016 18:52, amul sul wrote:

And it wont stop on some simple whitespace. By using to_timestamp you
can get any output results by providing illegal input parameters values:
postgres=# SELECT TO_TIMESTAMP('2016-06-13 99:99:99', 'YYYYMMDD
HH24:MI:SS');
to_timestamp
------------------------
2016-01-06 14:40:39+03

(1 row)

We do consume extra space from input string, but not if it is in format string, see below:

postgres=# SELECT TO_TIMESTAMP('2016-06-13 15:43:36', 'YYYY/MM/DD HH24:MI:SS');
to_timestamp
------------------------
2016-06-13 15:43:36-07
(1 row)

We should have same treatment for format string too.

Thoughts? Comments?

Well, the user specifies the format string, while the input string comes
from the data, so I don't see having them behave the same as necessary.

To be honest they not just behave differently. to_timestamp is just
incorrectly handles input data and nothing else.There is no excuse for
such behavior:

postgres=# SELECT TO_TIMESTAMP('20:-16-06:13: 15_43:!36', 'YYYY/MM/DD
HH24:MI:SS');
to_timestamp
------------------------------
0018-08-05 13:15:43+02:30:17
(1 row)

Alex Ignatov
Postgres Professional: http://www.postgrespro.com
The Russian Postgres Company

--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers

On Thu, Jun 23, 2016 at 12:16 PM, Alex Ignatov <a.ignatov@postgrespro.ru>
wrote:

On 23.06.2016 16:30, Bruce Momjian wrote:

On Thu, Jun 23, 2016 at 07:41:26AM +0000, amul sul wrote:

On Monday, 20 June 2016 8:53 PM, Alex Ignatov <a.ignatov@postgrespro.ru>
wrote:

On 13.06.2016 18:52, amul sul wrote:

And it wont stop on some simple whitespace. By using to_timestamp you
can get any output results by providing illegal input parameters values:
postgres=# SELECT TO_TIMESTAMP('2016-06-13 99:99:99', 'YYYYMMDD
HH24:MI:SS');
to_timestamp
------------------------
2016-01-06 14:40:39+03

(1 row)

We do consume extra space from input string, but not if it is in format
string, see below:

postgres=# SELECT TO_TIMESTAMP('2016-06-13 15:43:36', 'YYYY/MM/DD
HH24:MI:SS');
to_timestamp
------------------------
2016-06-13 15:43:36-07
(1 row)

We should have same treatment for format string too.

Thoughts? Comments?

Well, the user specifies the format string, while the input string comes
from the data, so I don't see having them behave the same as necessary.

To be honest they not just behave differently. to_timestamp is just
incorrectly handles input data and nothing else.There is no excuse for
such behavior:

postgres=# SELECT TO_TIMESTAMP('20:-16-06:13: 15_43:!36', 'YYYY/MM/DD
HH24:MI:SS');
to_timestamp
------------------------------
0018-08-05 13:15:43+02:30:17
(1 row)

T
​o be honest I don't see how this is relevant to quoted content. And
you've already made this point quite clearly - repeating it isn't
constructive. This behavior has existed for a long time and I don't see
that changing it is a worthwhile endeavor. I believe a new function is
required that has saner behavior. Otherwise given good input and a
well-formed parse string the function does exactly what it is designed to
do. Avoid giving it garbage and you will be fine. Maybe wrap the call to
the in a function that also checks for the expected layout and RAISE
EXCEPTION if it doesn't match.

​David J.
​
​

On 23.06.2016 19:37, David G. Johnston wrote:

On Thu, Jun 23, 2016 at 12:16 PM, Alex Ignatov
<a.ignatov@postgrespro.ru <mailto:a.ignatov@postgrespro.ru>>wrote:

On 23.06.2016 16:30, Bruce Momjian wrote:

On Thu, Jun 23, 2016 at 07:41:26AM +0000, amul sul wrote:

On Monday, 20 June 2016 8:53 PM, Alex Ignatov
<a.ignatov@postgrespro.ru
<mailto:a.ignatov@postgrespro.ru>> wrote:

On 13.06.2016 18:52, amul sul wrote:

And it wont stop on some simple whitespace. By using
to_timestamp you
can get any output results by providing illegal input
parameters values:
postgres=# SELECT TO_TIMESTAMP('2016-06-13 99
<tel:2016-06-13%2099>:99:99', 'YYYYMMDD
HH24:MI:SS');
to_timestamp
------------------------
2016-01-06 14:40:39+03

(1 row)

We do consume extra space from input string, but not if it
is in format string, see below:

postgres=# SELECT TO_TIMESTAMP('2016-06-13 15:43:36',
'YYYY/MM/DD HH24:MI:SS');
to_timestamp
------------------------
2016-06-13 15:43:36-07
(1 row)

We should have same treatment for format string too.

Thoughts? Comments?

Well, the user specifies the format string, while the input
string comes
from the data, so I don't see having them behave the same as
necessary.

To be honest they not just behave differently. to_timestamp is
just incorrectly handles input data and nothing else.There is no
excuse for such behavior:

postgres=# SELECT TO_TIMESTAMP('20:-16-06:13: 15_43:!36',
'YYYY/MM/DD HH24:MI:SS');
to_timestamp
------------------------------
0018-08-05 13:15:43+02:30:17
(1 row)

T
​o be honest I don't see how this is relevant to quoted content. And
you've already made this point quite clearly - repeating it isn't
constructive. This behavior has existed for a long time and I don't
see that changing it is a worthwhile endeavor. I believe a new
function is required that has saner behavior. Otherwise given good
input and a well-formed parse string the function does exactly what it
is designed to do. Avoid giving it garbage and you will be fine.
Maybe wrap the call to the in a function that also checks for the
expected layout and RAISE EXCEPTION if it doesn't match.

​David J.
​
​

Arguing just like that one can say that we don't even need exception
like "division by zero". Just use well-formed numbers in denominator...
Input data sometimes can be generated automagically. Without exception
throwing debugging stored function containing to_timestamp can be painful.

On Thursday, June 23, 2016, Alex Ignatov <a.ignatov@postgrespro.ru> wrote:

Arguing just like that one can say that we don't even need exception like
"division by zero". Just use well-formed numbers in denominator...
Input data sometimes can be generated automagically. Without exception
throwing debugging stored function containing to_timestamp can be painful.

to_timestamp with its present behavior is, IMO, a poorly designed function
that would never be accepted today. Concrete proposals for either fixing
or deprecating (or both) are welcome. Fixing it should not
cause unnecessary errors to be raised.

My main point is that I'm inclined to deprecate it.

My second point is if you are going to use this badly designed function you
need to protect yourself.

My understanding is that is not going to change for 9.6.

David J.

On Thu, Jun 23, 2016 at 12:37 PM, David G. Johnston
<david.g.johnston@gmail.com> wrote

To be honest I don't see how this is relevant to quoted content. And you've
already made this point quite clearly - repeating it isn't constructive.
This behavior has existed for a long time and I don't see that changing it
is a worthwhile endeavor. I believe a new function is required that has
saner behavior. Otherwise given good input and a well-formed parse string
the function does exactly what it is designed to do. Avoid giving it
garbage and you will be fine. Maybe wrap the call to the in a function that
also checks for the expected layout and RAISE EXCEPTION if it doesn't match.

Well, I think other people are allowed to disagree about whether
changing it is a worthwhile endeavor. I think there's an excellent
argument that the current behavior is stupid and broken and probably
nobody is intentionally relying on it.

Obviously, if somebody is relying on the existing behavior and we
change it and it breaks things, then that's bad, and a good argument
for worrying about backward-compatibility - e.g. by adding a new
function. But who would actually like the behavior that an extra
space in the format string causes non-whitespace characters to get
skipped? Next you'll be arguing that we can't fix a server crash
that's triggered by a certain query because somebody might be relying
on that query to restart the server...

--
Robert Haas
EnterpriseDB: http://www.enterprisedb.com
The Enterprise PostgreSQL Company

--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers

On Thu, Jun 23, 2016 at 1:12 PM, David G. Johnston
<david.g.johnston@gmail.com> wrote:

to_timestamp with its present behavior is, IMO, a poorly designed function
that would never be accepted today. Concrete proposals for either fixing or
deprecating (or both) are welcome. Fixing it should not cause unnecessary
errors to be raised.

Sheesh. Who put you in charge of this? You basically seem like you
are trying to shut up anyone who supports this change, and I don't
think that's right. Alex's opinion is just as valid as yours -
neither more nor less - and he has every right to express it without
being told by you that his emails are "not constructive".

My main point is that I'm inclined to deprecate it.

I can almost guarantee that would make a lot of users very unhappy.
This function is widely used.

My second point is if you are going to use this badly designed function you
need to protect yourself.

I agree that anyone using this function should test their format
strings carefully.

My understanding is that is not going to change for 9.6.

That's exactly what is under discussion here.

--
Robert Haas
EnterpriseDB: http://www.enterprisedb.com
The Enterprise PostgreSQL Company

--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers

David G. Johnston wrote:

On Thursday, June 23, 2016, Alex Ignatov <a.ignatov@postgrespro.ru> wrote:

Arguing just like that one can say that we don't even need exception like
"division by zero". Just use well-formed numbers in denominator...
Input data sometimes can be generated automagically. Without exception
throwing debugging stored function containing to_timestamp can be painful.

to_timestamp with its present behavior is, IMO, a poorly designed function
that would never be accepted today.

I'm not sure about that.

to_timestamp was added to improve compatibility with Oracle, by commit
b866d2e2d794. I suppose the spec should follow what's documented here,

http://docs.oracle.com/cd/B19306_01/server.102/b14200/functions193.htm
http://docs.oracle.com/cd/B19306_01/server.102/b14200/sql_elements004.htm#i34924

and that wherever we deviate from that, is a bug that should be fixed.

--
�lvaro Herrera http://www.2ndQuadrant.com/
PostgreSQL Development, 24x7 Support, Remote DBA, Training & Services

--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers

Robert Haas <robertmhaas@gmail.com> writes:

On Thu, Jun 23, 2016 at 1:12 PM, David G. Johnston
<david.g.johnston@gmail.com> wrote:

My understanding is that is not going to change for 9.6.

That's exactly what is under discussion here.

I would definitely agree with David on that point. Making to_timestamp
noticeably better on this score seems like a nontrivial project, and
post-beta is not the time for that sort of thing, even if we had full
consensus on what to do. I'd suggest somebody work on a patch and put
it up for review in the next cycle.

Now, if you were to narrowly define the problem as "whether to skip
non-spaces for a space in the format", maybe that could be fixed
post-beta, but I think that's a wrongheaded approach. to_timestamp's
issues with input that doesn't match the format are far wider than that.
IMO we should try to resolve the whole problem with one coherent change,
not make incremental incompatible changes at the margins.

At the very least I'd want to see a thought-through proposal that
addresses all three of these interrelated points:

* what should a space in the format match
* what should a non-space, non-format-code character in the format match
* how should we handle fields that are not exactly the width suggested
by the format

regards, tom lane

--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers