Christoph Zwerschke <cito(at)online(dot)de> wrote:
> Am 26.04.2010 12:11, schrieb Takahiro Itagaki:
>> Do you know how the SQL standard mention the behavior? IMHO,
>> postgres' behavior is more reasonable because
>> length(' '::char(1)) is 0.
>
> Just found http://troels.arvin.dk/db/rdbms/ which claims that this
> is against the standard:
>
> "PostgreSQL: Stores CHARs in space padded form, but violates
> the standard by (conceptually) truncating trailing white-space
> before performing most functions, operators, and comparisons
> (like the CHARACTER_LENGTH-function and the concatenation(||)
> operator)."
>
> Not sure if this is correct and how well-defined the SQL standard
> actually is in this regard. It seems Oracle does not remove
> trailing spaces when converting from char to varchar.
That is consistent with how I remember the standard (although I
don't have time right now to fight my way through it to confirm).
My recollection is that char(n) should be treated exactly like a
varchar padded with spaces to n characters *except* for character
string comparisons, where trailing spaces are supposed to be
ignored.
-Kevin