Expression indexes ignore typmod of expression - Mailing list pgsql-hackers

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];