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

> -----Original Message-----
> From: Martijn van Oosterhout [mailto:kleptog(at)svana(dot)org]
> Sent: Wednesday, October 19, 2005 2:46 PM
> To: Dann Corbit
> Cc: Terry Fielder; Tino Wildenhain; Marc G. Fournier;
> Richard_D_Levine(at)raytheon(dot)com; pgsql-hackers(at)postgresql(dot)org; pgsql-
> general(at)postgresql(dot)org
> Subject: Re: 'a' == 'a ' (Was: RE: [pgsql-advocacy] [GENERAL] 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?

Storage. The blank padding is only for comparison purposes.

>You may as well get rid of one...

All the other database systems seem to handle it in the way that I
expect.
Which is not to say that it is the right way or that it agrees with the
standard.
But it is how it appears to me, so far.