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

Re: varchar index joins not working?

From: Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us>
To: Adam Gundy <adam(at)starsilk(dot)net>
Cc: Richard Huxton <dev(at)archonet(dot)com>, pgsql-performance(at)postgresql(dot)org
Subject: Re: varchar index joins not working?
Date: 2008-04-14 22:01:16
Message-ID: 23669.1208210476@sss.pgh.pa.us (view raw or flat)
Thread:
Lists: pgsql-performance
Adam Gundy <adam(at)starsilk(dot)net> writes:
> Tom Lane wrote:
>> Oh, it coerces the type all right, just not in the direction you'd like.

> is there a reason it doesn't coerce to a type that's useful to the 
> planner (ie varchar in my case),

In this case I think the choice is probably semantically correct:
shouldn't a comparison of varchar (trailing space sensitive) and
char (trailing space INsensitive) follow trailing-space-insensitive
semantics?

I wouldn't swear that the behavior is intentional ;-) as to going
that way rather than the other, but I'm disinclined to change it.

> or the planner doesn't accept any type 
> of string as a valid match for index scan?

Can't.  This equality operator doesn't have the same notion of equality
that that index does.

The long and the short of it is that mixing char and varchar is
hazardous.

			regards, tom lane

In response to

Responses

pgsql-performance by date

Next:From: Adam GundyDate: 2008-04-14 22:16:41
Subject: Re: varchar index joins not working?
Previous:From: Adam GundyDate: 2008-04-14 21:13:13
Subject: Re: varchar index joins not working?

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