Expression indexes ignore typmod of expression

From: Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us>
To: pgsql-hackers(at)postgreSQL(dot)org
Subject: Expression indexes ignore typmod of expression
Date: 2014-04-25 21:05:26
Message-ID: 28318.1398459926@sss.pgh.pa.us
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: 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

Attachment Content-Type Size
fix-typmod-of-expression-indexes.patch text/x-diff 672 bytes

Responses

Browse pgsql-hackers by date

  From Date Subject
Next Message Tomas Vondra 2014-04-25 21:16:01 Re: [GENERAL] aggregate returning anyarray and 'cannot determine result data type'
Previous Message Stephen Frost 2014-04-25 19:53:32 Planned downtime @ Rackspace