Thread: to_timestamp() changes in 8.4 release notes
Hi guys, I noticed the following item under "Observe the following incompatibilities" in the 8.4 release notes (E.1.2.4.1.) * Require to_timestamp() input to match meridian (AM/PM) and era (BC/AD) format designations with respect to presence of periods (Brendan Jurd) For example, input value AD now does not match format string A.D.. This is actually not a change in behaviour. AM/PM and BC/AD have always required you to use the correct format string w.r.t. periods. I'm sorry if the comments in my patch submission were vague on this. I think this item should be pulled from the "incompatibilities" list. The preceding item (about not having to match case on AM/PM and BC/AD anymore) is still valid. Cheers, BJ
Brendan Jurd <direvus@gmail.com> writes:
> I noticed the following item under "Observe the following
> incompatibilities" in the 8.4 release notes (E.1.2.4.1.)
> * Require to_timestamp() input to match meridian (AM/PM) and era
> (BC/AD) format designations with respect to presence of periods
> (Brendan Jurd)
> For example, input value AD now does not match format string A.D..
> This is actually not a change in behaviour.
Well, it does seem to have some visible effect --- in 8.3 I see
regression=# select to_timestamp('1BC', 'YYYYA.D.'); to_timestamp
------------------------0001-01-01 00:00:00-05
(1 row)
ie, failure to match means the field is silently ignored. In HEAD,
regression=# select to_timestamp('1BC', 'YYYYA.D.');
ERROR: invalid value "BC" for "A.D."
DETAIL: The given value did not match any of the allowed values for this field.
ie, failure to match means you get an error.
I guess though your point is that this is part of the general tightening
of to_timestamp()'s error checking, and doesn't need a separate entry?
regards, tom lane
On Mon, Apr 20, 2009 at 1:06 AM, Tom Lane <tgl@sss.pgh.pa.us> wrote: > Well, it does seem to have some visible effect --- in 8.3 I see > ... > ie, failure to match means the field is silently ignored. In HEAD, > ... > ie, failure to match means you get an error. > > I guess though your point is that this is part of the general tightening > of to_timestamp()'s error checking, and doesn't need a separate entry? > You guess correctly =) There might be some value in changing the wording of that paragraph about the "general tightening" to emphasise that queries which previously succeeded (with some parts being misinterpreted or silently disregarded) will now throw an error. It is entirely possible that these changes will break existing queries, although I would like to hope that there aren't too many people out there relying on the quirky misbehaviours of to_timestamp(). To this item: Cause to_date() and to_timestamp() to more consistently report errors for invalid input (Brendan Jurd) We could add a line like: Some invalid inputs which were silently ignored or misread in 8.3 and earlier, will now cause an ERROR to be raised. Cheers, BJ
Brendan Jurd <direvus@gmail.com> writes:
> On Mon, Apr 20, 2009 at 1:06 AM, Tom Lane <tgl@sss.pgh.pa.us> wrote:
>> I guess though your point is that this is part of the general tightening
>> of to_timestamp()'s error checking, and doesn't need a separate entry?
> You guess correctly =)
> There might be some value in changing the wording of that paragraph
> about the "general tightening" to emphasise that queries which
> previously succeeded (with some parts being misinterpreted or silently
> disregarded) will now throw an error.
OK, done. I wrote
Previous versions would often ignore or silently misread input that did not match the format string. Such
caseswill now result in an error.
regards, tom lane
On Mon, Apr 20, 2009 at 1:51 AM, Tom Lane <tgl@sss.pgh.pa.us> wrote: > Brendan Jurd <direvus@gmail.com> writes: >> There might be some value in changing the wording of that paragraph >> about the "general tightening" ... > > OK, done. I wrote > > Previous versions would often ignore or silently misread input > that did not match the format string. Such cases will now > result in an error. > Excellent, thanks Tom. Cheers, BJ