Re: varchar index joins not working?

Adam Gundy <adam(at)starsilk(dot)net> writes:
> hmm. unfortunately it did turn out to be (part) of the issue. I've
> discovered that mixing char and varchar in a stored procedure does not
> coerce the types, and ends up doing seq scans all the time.

Oh, it coerces the type all right, just not in the direction you'd like.

regression=# create table v (f1 varchar(32) primary key);
NOTICE: CREATE TABLE / PRIMARY KEY will create implicit index "v_pkey" for table "v"
CREATE TABLE
regression=# explain select * from v where f1 = 'abc'::varchar;
QUERY PLAN
-----------------------------------------------------------------
Index Scan using v_pkey on v (cost=0.00..8.27 rows=1 width=34)
Index Cond: ((f1)::text = 'abc'::text)
(2 rows)

regression=# explain select * from v where f1 = 'abc'::char(3);
QUERY PLAN
---------------------------------------------------
Seq Scan on v (cost=0.00..25.88 rows=1 width=34)
Filter: ((f1)::bpchar = 'abc'::character(3))
(2 rows)

regards, tom lane