Re: How are null's stored?

On Mon, 12 May 2003, Tom Lane wrote:

> Stephan Szabo <sszabo(at)megazone23(dot)bigpanda(dot)com> writes:
> > All in all, I'm not sure what the semantic differences between a varchar
> > with no length specified and a text are in PostgreSQL actually and if the
> > whole thing could be simplified in some way that doesn't break backwards
> > compatibility.
>
> Yeah, I've been wondering about that too. A large part of the problem
> is that varchar has its own set of operators, which the planner has no
> right to assume behave exactly like the text ones ... but they do. It
> might work to rip out the redundant varchar operators and allow indexes
> on varchar to become truly textual indexes (ie, they'd be text_ops not
> varchar_ops opclass). There might be a few tweaks needed to get the
> planner to play nice with indexes that require implicit coercions, but
> I think it could be made to work.

This seems to possibly work on 7.4. I took my system and removed the
varchar comparison operators and directly made a text_ops index on a
varchar(30).
That gave me indexscans for
col = 'a'
col = 'a'::varchar
col = 'a'::text
col = 'a' || 'b'

but I don't know if it has other bad effects yet.

> Another idea that has been rattling around is to stop treating bpchar as
> binary-equivalent to text, and in fact to make bpchar-to-text promotion
> go through rtrim() to eliminate padding spaces.

I guess this depends on how we read the comparisons/conversions from PAD
SPACE to NO PAD are supposed to work, but I think this would be good and
make things easier for alot of people since most people don't expect it,
especially when using functions like upper and lower that return text.