Tom Lane wrote:
>> b) If the length in characters of X is not equal to the length
>> in characters of Y, then the shorter string is effectively
>> replaced, for the purposes of comparison, with a copy of itself
>> that has been extended to the length of the longer string by
>> concatenation on the right of one or more pad characters,
>> where the pad character is chosen based on CS.
>> If CS has the NO PAD characteristic, then the pad character is
>> an implementation-dependent character different from
>> any character in the character set of X and Y that collates
>> less than any string under CS.
>> Otherwise, the pad character is a <space>.
>
> The PAD case is specifying the way that CHAR(n) comparison should work.
> (We don't expose the PAD/NO PAD distinction in any other way than
> CHAR vs VARCHAR/TEXT types.)
>
> AFAICS, the NO PAD case is ignorable BS: [...]
> In any case, the most significant word in that whole paragraph is
> "effectively", which means you can do it however you want as long
> as you get an equivalent comparison result.
>
>> That would effectively mean that 'a'='a ' is TRUE for
>> all character string types.
>
> In the PAD case, yes. Else no.
Thanks for the clarification.
>> Of the DBMS I tested, Microsoft SQL Server and MySQL gave me
>> that very result, while PostgreSQL and Oracle gave me FALSE.
>
> This probably has more to do with what these systems think the
> data type of an undecorated literal is, than with whether they do
> trailing-space-insensitive comparison all the time.
I tested not only with string literals, but also comparing
table columns of the respective types.
I came up with the following table of semantics used for
comparisons:
| CHAR(n)=CHAR(n) | VARCHAR(n)=VARCHAR(n) | CHAR(n)=VARCHAR(n) |
-----------+-----------------+-----------------------+--------------------+
Oracle | PAD SPACE | NO PAD | NO PAD |
-----------+-----------------+-----------------------+--------------------+
PostgreSQL | PAD SPACE | NO PAD | PAD SPACE |
-----------+-----------------+-----------------------+--------------------+
MySQL | PAD SPACE | PAD SPACE | PAD SPACE |
-----------+-----------------+-----------------------+--------------------+
SQL Server | PAD SPACE | PAD SPACE | PAD SPACE |
-----------+-----------------+-----------------------+--------------------+
Yours,
Laurenz Albe
--
Sent via pgsql-general mailing list (pgsql-general@xxxxxxxxxxxxxx)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
