Mac OS X:
postgres% psql --version
psql (PostgreSQL) 7.2.1
contains support for: multibyte

LEDEV=# create table test1 (foo varchar(5));
CREATE
LEDEV=# create table test2 (foo char(5));
CREATE
LEDEV=# insert into test2 (foo) values ('S');
INSERT 3724249 1
LEDEV=# insert into test1 (foo) values ('S');
INSERT 3724250 1
LEDEV=# select a.foo, b.foo from test1 a, test2 b where a.foo = 
b.foo::text;
  foo | foo
-----+-----
(0 rows)

LEDEV=# select a.foo = 'S', b.foo = 'S' from test1 a, test2 b;
  ?column? | ?column?
----------+----------
  t        | t
(1 row)

LEDEV=# select a.foo, b.foo from test1 a, test2 b where CAST(a.foo as 
CHAR) = b.foo;
  foo |  foo
-----+-------
  S   | S
(1 row)


Thu, 11 Jul 2002 16:47:39 -0500
Scott Royston <scroyston@mac.com>
string cast/compare broken?
Scott Royston <scroyston@mac.com> writes:
> [ various examples of comparing char and varchar ]

I see no bug here.  For the CHAR datatype, trailing spaces are defined
to be insignificant.  For VARCHAR and TEXT, trailing spaces are
significant.  If you want to compare a CHAR value to a VARCHAR or TEXT
value, your best bet is a locution like
	rtrim(charval) = varcharval

			regards, tom lane

Thu, 11 Jul 2002 23:50:25 -0400
Tom Lane <tgl@sss.pgh.pa.us>
Re: string cast/compare broken? 
On Fri, 2002-07-12 at 08:50, Tom Lane wrote:
> Scott Royston <scroyston@mac.com> writes:
> > [ various examples of comparing char and varchar ]
> 
> I see no bug here.  For the CHAR datatype, trailing spaces are defined
> to be insignificant.  For VARCHAR and TEXT, trailing spaces are
> significant.  If you want to compare a CHAR value to a VARCHAR or TEXT
> value, your best bet is a locution like
> 	rtrim(charval) = varcharval

I guess the strangest part was that both a.foo = 'S' and b.foo = 'S' but
not a.foo=b.foo;  (a.foo is varchar(5) , b.foo is char(5) )

I guess that tha 'S' that b.foo gets compared to is converted to 'S    '
before comparison but when comparing varchar(5) and char(5) they are
both compared by converting them to varchar which keeps the trailing
spaces from char(5). If the conversion where varchar(5) --> char(5) then
they would compare equal.

I vaguely remember something in the standard about cases when comparing
char() types should discard extra spaces.

-------------
Hannu




12 Jul 2002 09:14:07 +0500
Hannu Krosing <hannu@tm.ee>
Re: string cast/compare broken?