Albe Laurenz <laurenz.albe@xxxxxxxxxx> 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 -- Sent via pgsql-general mailing list (pgsql-general@xxxxxxxxxxxxxx) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
