Re: 'a' == 'a ' (Was: RE: [pgsql-advocacy] Oracle buysInnobase)

On Wed, Oct 19, 2005 at 02:05:20PM -0700, Dann Corbit wrote:
> > When the compared datatypes are VARCHAR: YES
>
> What is the value of doing that?
>
> I can see plenty of harm and absolutely no return. We are talking about
> blank padding before comparison. Do you really want 'Danniel '
> considered distinct from 'Danniel ' in a comparison? In real life,
> what does that buy you?

Well, looking from the point of view of using indexes, indexes can only
really match on things that are equal. Which means the system is going
to have to trim them anyway. I'm of the opinion that strings are
strings and spaces are no different from other characters.

That bit of the standard quoted earlier, if you read the PAD character
that is different from any other character as being the NUL character,
then 'a<space><nul>' is clearly different from 'a<space><space>'. This
whacky space behaviour is something I associate with the char(N) and is
the main reason I never use it.

> Perhaps this is old hat to the long-timers around here and there is a
> good explanation as to why varchar should have non-blank padding when
> comparisons are performed. Can someone point me to documentation that
> explains it?

The way I understood it:

char(N) is blank padding
varchar(N) is not

If you make varchar(n) do blank padding, then what's the difference
between the two types? You may as well get rid of one...

Have a nice day,
--
Martijn van Oosterhout <kleptog(at)svana(dot)org> http://svana.org/kleptog/
> Patent. n. Genius is 5% inspiration and 95% perspiration. A patent is a
> tool for doing 5% of the work and then sitting around waiting for someone
> else to do the other 95% so you can sue them.