Thread: Difference in text/char data matching between 7.3.4 & 7.4.2

Hi all,

I have a question regarding to text/char data matching difference
between PostgreSQL version 7.3.4 and 7.4.2, and would like to have your
variable opinions on this issue.

With version 7.3.4, following query returns true, but with version 7.4.2
it returns false.

   select ' '::text = ' '::char;

Following query works fine (returns true) with both versions:

   select ' '::text = ' '::varchar;


Further I checked the difference in text/char data matching between
7.3.4 and 7.4.2, and here is what I did:

1) Create Test Table
create table test(text_col text, char_col char(1), varchar_col varchar(1));

2) Insert Test Data
insert into test (text_col, char_col, varchar_col) values (' ',' ',' ');

3) Issued Following 3 Queries
select count(*) from test where text_col = char_col;
select count(*) from test where text_col = varchar_col;
select count(*) from test where varchar_col = char_col;

4) Compare the Results of both 7.3.4 and 7.4.2


======================
The Result with 7.3.4
======================
# create table test(text_col text, char_col char(1), varchar_col
varchar(1));
CREATE TABLE
# insert into test (text_col, char_col, varchar_col) values (' ',' ',' ');
INSERT 1253317 1
# select count(*) from test where text_col = char_col;
  count
-------
      1
(1 row)

# select count(*) from test where text_col = varchar_col;
  count
-------
      1
(1 row)

# select count(*) from test where varchar_col = char_col;
ERROR:  Unable to identify an operator '=' for types 'character varying'
and 'character'
         You will have to retype this query using an explicit cast


======================
The Result with 7.4.2
======================
# create table test(text_col text, char_col char(1), varchar_col
varchar(1));
CREATE TABLE
# insert into test (text_col, char_col, varchar_col) values (' ',' ',' ');
INSERT 784823 1
# select count(*) from test where text_col = char_col;
  count
-------
      0   => with 7.3.4, it returns "1".
(1 row)

# select count(*) from test where text_col = varchar_col;
  count
-------
      1
(1 row)

# select count(*) from test where varchar_col = char_col;
  count
-------
      1   => with 7.3.4, this query triggers an error.
(1 row)


I would like to know if this is a bug or something should be fixed. If
not, I guess I have to always do casting?

Any opinion is appreciated.

Thanks,
Kazuya

Kazuya Togashi <togashi@sra.co.jp> writes:
> With version 7.3.4, following query returns true, but with version 7.4.2
> it returns false.

>    select ' '::text = ' '::char;

Offhand that seems like a step forward to me.  "char" type considers
trailing spaces to be insignificant, so it is really correct to consider
that ' '::char is equivalent to ''::text, not ' '::text which represents
a single significant space.

If you do not like this behavior, I suggest casting your text input to
char so that the comparison will occur under char rules not text rules.

            regards, tom lane

Hi Tom,

Thanks for your input. So if I understand you correctly, it is implied
that the behavior with PostgreSQL 7.3.4 is likely to be incorrect (or it
was a bug, I guess?)

One more question, in your reply, you mentioned that "char" type
considers trailing spaces to be insignificant. Should this idea be
applied to both how "char" type holds whitespaces and how equal operator
works on "char" type?

Thanks,
Kazuya

Tom Lane wrote:

> Kazuya Togashi <togashi@sra.co.jp> writes:
>
>>With version 7.3.4, following query returns true, but with version 7.4.2
>>it returns false.
>
>
>>   select ' '::text = ' '::char;
>
>
> Offhand that seems like a step forward to me.  "char" type considers
> trailing spaces to be insignificant, so it is really correct to consider
> that ' '::char is equivalent to ''::text, not ' '::text which represents
> a single significant space.
>
> If you do not like this behavior, I suggest casting your text input to
> char so that the comparison will occur under char rules not text rules.
>
>             regards, tom lane
>
>