Search Postgresql Archives

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

[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]

 



Ken Johanson <pg-user@xxxxxxxxxxxxx> 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

---------------------------(end of broadcast)---------------------------
TIP 9: In versions below 8.0, the planner will ignore your desire to
       choose an index scan if your joining column's datatypes do not
       match

[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]
[Index of Archives]     [Postgresql Jobs]     [Postgresql Admin]     [Postgresql Performance]     [Linux Clusters]     [PHP Home]     [PHP on Windows]     [Kernel Newbies]     [PHP Classes]     [PHP Books]     [PHP Databases]     [Postgresql & PHP]     [Yosemite]
  Powered by Linux