Re: New to_timestamp implementation is pretty strict

On Mon, Dec 1, 2008 at 11:08 PM, Heikki Linnakangas
<heikki(dot)linnakangas(at)enterprisedb(dot)com> wrote:
> postgres=# SELECT to_timestamp('29-12-2005 01:02:3', 'DD-MM-YYYY
> HH24:MI:SS'); -- doesn't work
...
> I think the end of string should be treated like a field separator, colon in
> this example, and we should accept both of the above. Opinions?
>

(With apologies for being late to the thread)

I can agree that it is inconsistent to treat separator characters,
like colons and hyphens, differently to the end of the string. If
we're allowing separators to imply variable-width nodes, then the end
of the string should do so as well, so I think Heikki did the right
thing here.

However, I actually think that, ultimately, Heikki's example above
*should* be rejected, and so should any other attempt to provide a
value of the wrong width, even if there are separators present, unless
the user has specified the 'FM' fill mode flag.

Heikki's example shows a too-short string for SS, but what about one
that is too long? Should we accept

# to_timestamp('29122005 0102333', 'DDMMYYYY HH24MISS')

As meaning three hundred and thirty-three seconds? I would argue we
shouldn't; it's most likely that the user made an error, so the right
thing to do is throw an exception and give them an opportunity to fix
it. Making guesses about the user's intention when the input is
heavily ambiguous isn't a fun game to be playing, for us or for the
user.

Given the contrary arguments (backwards- and Oracle- compatibility,
mostly) I decided that was too much for me to bite off in my patch.
That was the same reason I didn't fiddle with the treatment of
end-of-string; 8.3 didn't treat it as a separator either. The
different is that, although it always treated the final field as
fixed-width, prior to my patch it didn't actually throw an error when
fixed-width fields were too short.

Cheers,
BJ