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

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).

>
> Also, section 6.10 <cast specification> defines an explicit cast to
> a fixed-length string type as truncating or padding to the target
> length (LTD):
>

And PG does this, perfectly. It even right-pads, the other databases
(tried My and Ms) do not...

>
>
> Possibly you could get what you want by casting to char(10) or so.
>

Alas the behavior is different. The right padding exists (in PG). So I
cannot get uniform behavior (the other DB's fault I agree for not
supporting cast as varchar).

Unless PG can start throwing an exception in this version when it
truncates to implicit-1, I think it should be forgiving and auto-size..

Is it possible to override this built-in cast function with a create-cast?