Re: SELECT CAST(123 AS char) -> 1

Gregory Stark wrote:
> "Ken Johanson" <pg-user(at)kensystem(dot)com> writes:
>
>> Tom Lane wrote:
>>
>>> SQL92 section 6.1 <data type> quoth
>>>
>>> <character string type> ::=
>>> CHARACTER [ <left paren> <length> <right paren> ]
>>> | CHAR [ <left paren> <length> <right paren> ]
>>>
>>> ...
>>>
>>> 4) If <length> is omitted, then a <length> of 1 is implicit.
>>>
>>> Therefore, writing just "char" is defined as equivalent to "char(1)".
>> However when length is not defined I think it will generally be safe(r) to
>> auto-size. In the grand scheme auto-size creates much more sensible output than
>> a 1-char wide one (even if right-padded to max char-length of the type).
>
> Sure, but you're a prime candidate for understanding the value of following
> the spec if you're trying to write software that works with multiple
> databases.

The spec has diminished in this (CAST without length) context:
a) following it produces an output which has no usefulness whatsoever
(123 != 1)
b) all the other databases chose to not follow the spec in the context
of cast and char with implicit length.

When the length is unqualified, a cast to char should one of:

1) failfast
2) auto-size to char-count (de facto)
3) pad to the max-length

>
> It's a bit crazy to be using CHAR and then complaining about padding...

I did say earlier that I could at least accept padding to the max-char
length, even though in my use-case it wont work.