Re: [HACKERS] 'a' == 'a ' - Mailing list pgsql-general

From Lincoln Yeoh
Subject Re: [HACKERS] 'a' == 'a '
Date
Msg-id 5.2.1.1.1.20051020223403.02e29520@localhost
Whole thread Raw
List pgsql-general
At 05:33 PM 10/19/2005 -0700, Dann Corbit wrote:

>If there is a significant performance benefit to not expanding text
>columns in comparison operations, then it seems it should be OK.
>
>I probably read the standard wrong, but it seems to me that varchar, char,
>and bpchar columns should all behave the same (e.g. if you do not expand
>with <blank> or the PAD character (whatever that is) then all char type
>columns should behave the same.  I guess that there could be different
>default collations for different column

I am not a DB guru. BUT IMO they should NOT behave the same.

Varchars should NOT be padded.

For the very reason when you select text out of varchar fields the result
is not padded.

If I insert a string with a single trailing space into a varchar, I _want_
that single trailing space to still be there when I retrieve it, and not
followed by more spaces. Otherwise I will have to pick a different database ;).

So similarly, I would expect that varchars 'a ' and 'a    ' when compared
should be different.

However, in the case of _chars_ which are padded, then 'a ' should be
padded so that it can be compared with 'a     '.

Otherwise there will be no reason to do equality comparisons of char(5)
fields with char(8) fields - they can NEVER be the same :).

But would that mean that when one does equality comparisons of varchars
with chars, one would probably want padding? Or only varchars of the same
length as the char would have a chance of matching?

Hmm.. I think I better leave this one to the DB gurus :). But I really
don't ever want 'a ' to be the same as 'a   ' for varchars.

Link.


pgsql-general by date:

Previous
From: Michael Fuhr
Date:
Subject: Re: NULL != text ?
Next
From: "Kevin Grittner"
Date:
Subject: Re: [HACKERS] 'a' == 'a ' (Was: RE: [pgsql-advocacy]