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

Ken Johanson <pg-user(at)kensystem(dot)com> writes:
> For sake of interoperability (and using an API that requires String-type
> hashtable keys), I'm trying to find a single CAST (int -> var/char)
> syntax that works between the most databases. Only char seems to be a
> candidate, but in 8.3 casting from an integer outputs only the first char...

> Is this a bug, or would someone like to horrify me by stating something
> like "spec says this is correct". :-)

Okay: the spec says this is correct.

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

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

Case:

i) If the length in characters of SV is equal to LTD, then TV
is SV.

ii) If the length in characters of SV is larger than LTD, then
TV is the first LTD characters of SV. If any of the re-
maining characters of SV are non-<space> characters, then a
completion condition is raised: warning-string data, right
truncation.

iii) If the length in characters M of SV is smaller than LTD,
then TV is SV extended on the right by LTD-M <space>s.

We don't report a "completion condition" for lack of any infrastructure
for that, but the result of the expression is per spec.

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

regards, tom lane