A Salesforce colleague asked me why, for something like
regression=# create table foo1 (f1 char(15), f2 char(15));
CREATE TABLE
regression=# create index on foo1((case when f1>'z' then f1 else f2 end));
CREATE INDEX
regression=# \d foo1_f2_idx
Index "public.foo1_f2_idx"
Column | Type | Definition
--------+--------+-----------------------------------
f2 | bpchar | ( +
| | CASE +
| | WHEN f1 > 'z'::bpchar THEN f1+
| | ELSE f2 +
| | END)
btree, for table "public.foo1"
the index column ends up as "bpchar" and not "char(15)". The CASE
expression does get resolved as char(15), but it turns out that
index.c just ignores that. I think this is just a hangover from
before we paid much attention to expression typmods at all, and
propose the attached patch.
Comments?
regards, tom lane
diff --git a/src/backend/catalog/index.c b/src/backend/catalog/index.c
index 47f0647..c932c83 100644
*** a/src/backend/catalog/index.c
--- b/src/backend/catalog/index.c
*************** ConstructTupleDescriptor(Relation heapRe
*** 389,395 ****
to->attalign = typeTup->typalign;
to->attstattarget = -1;
to->attcacheoff = -1;
! to->atttypmod = -1;
to->attislocal = true;
to->attcollation = collationObjectId[i];
--- 389,395 ----
to->attalign = typeTup->typalign;
to->attstattarget = -1;
to->attcacheoff = -1;
! to->atttypmod = exprTypmod(indexkey);
to->attislocal = true;
to->attcollation = collationObjectId[i];