Re: How are null's stored?

On Mon, 12 May 2003, Josh Berkus wrote:

> > So if the internal format is identical, why does the INFERNAL database
> > ignore indexes when you have a text compared to a varchar?
>
> I don't seem to have this problem; I use TEXT or VARCHAR willy-nilly,
> including in LIKE 'string%' and UPPER(field) queries, and the indexes work
> fine.

I can get the case he's complaining about with some cases I believe.

With an indexed varchar field, I can get 7.3.1 to give me:
sszabo=# set enable_seqscan=off;
SET
sszabo=# explain select * from aq2 where a=('f' || 'g');
                             QUERY PLAN
---------------------------------------------------------------------
 Seq Scan on aq2  (cost=100000000.00..100000022.50 rows=1 width=168)
   Filter: ((a)::text = 'fg'::text)

but

sszabo=# explain select * from aq2 where a=('f' || 'g')::varchar;
                              QUERY PLAN
----------------------------------------------------------------------
 Index Scan using aq2_pkey on aq2  (cost=0.00..4.82 rows=1 width=168)
   Index Cond: (a = 'fg'::character varying)

or

sszabo=# explain select * from aq2 where a=('f' || 'g'::varchar);
                              QUERY PLAN
----------------------------------------------------------------------
 Index Scan using aq2_pkey on aq2  (cost=0.00..4.82 rows=1 width=168)
   Index Cond: (a = 'fg'::character varying)

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.