Skip site navigation (1) Skip section navigation (2)

Re: How are null's stored?

From: Stephan Szabo <sszabo(at)megazone23(dot)bigpanda(dot)com>
To: Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us>
Cc: Josh Berkus <josh(at)agliodbs(dot)com>,Ryan <pgsql-performance(at)seahat(dot)com>,<pgsql-performance(at)postgresql(dot)org>
Subject: Re: How are null's stored?
Date: 2003-05-13 19:45:27
Message-ID: (view raw, whole thread or download thread mbox)
Lists: pgsql-hackerspgsql-performance
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
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.

In response to


pgsql-performance by date

Next:From: Tom LaneDate: 2003-05-13 20:16:10
Subject: Re: How are null's stored?
Previous:From: Josh BerkusDate: 2003-05-13 16:15:34

pgsql-hackers by date

Next:From: Manfred KoizarDate: 2003-05-13 19:57:39
Subject: Re: [PATCHES] Static snapshot data
Previous:From: Josh BerkusDate: 2003-05-13 18:04:56
Subject: Re: Scheduled Jobs

Privacy Policy | About PostgreSQL
Copyright © 1996-2017 The PostgreSQL Global Development Group