Albe Laurenz writes:
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 |
-----------+-----------------+-----------------------+--------------------+
Interesting. Did you determine which type is assigned to an
unmarked literal string by each system?

BTW, the last entry for PG surprised me a bit, because I would've
expected the varchar semantics to "win". Some experimentation shows
that you're correct about comparing char and varchar: we coerce the
varchar to char and use bpchareq, which ignores trailing blanks.
But if you compare char and text, we coerce the char to text (stripping
any trailing blanks as we do so) and then apply texteq. So in that
scenario, trailing blanks in the char datum are ignored, but trailing
blanks in the text datum are not, which is the behavior I was
remembering. It's a bit surprising that the two cases are resolved
differently --- I think that's coming out of the "most exact matches"
rule for ambiguous-operator resolution, because the available operators
are declared char = char and text = text.

    regards, tom lane

Search Discussions

Discussion Posts

Previous

Follow ups

Related Discussions

Discussion Navigation
viewthread | post
posts ‹ prev | 6 of 7 | next ›
Discussion Overview
grouppgsql-general @
categoriespostgresql
postedJan 17, '13 at 9:26a
activeJan 21, '13 at 3:54p
posts7
users3
websitepostgresql.org
irc#postgresql

People

Translate

site design / logo © 2021 Grokbase