Re: simple function index question

On Wed, Mar 23, 2016 at 8:14 AM, Michael Moore <michaeljmoore(at)gmail(dot)com>
wrote:

>
> On Tue, Mar 22, 2016 at 5:54 PM, Rosser Schwarz <rosser(dot)schwarz(at)gmail(dot)com>
> wrote:
>
>> On Tuesday, March 22, 2016, Michael Moore <michaeljmoore(at)gmail(dot)com>
>> wrote:
>>>
>>> ERROR: column "" has pseudo-type record
>>>
>>
>> Just guessing, and don't have time to verify, but you might try casting
>> the null value in the WHEN clause to the same type as
>> the question_set_dir_map_key column in the ELSE clause.
>>
>> I'd probably also try doing this with two separate indexes; this feels a
>> bit like it might asking the one index to do too much.
>>
>>
>
Related to your comment, I have successfully used two indexes in the past
for conditional indexing like this. YMMV.
Eg.
CREATE INDEX TX_QSET_DIR_MAP_PRI_URI_TYP_UK
ON tx_question_set_dir_map (question_set2tx_question_set ,uri_type, null)
WHERE uri_type = 201900
TABLESPACE qsn_indx_ol;

CREATE INDEX TX_QSET_DIR_MAP_PRI_URI_TYP_2_UK
ON tx_question_set_dir_map (question_set2tx_question_set ,uri_type,
question_set_dir_map_key)
WHERE uri_type != 201900
TABLESPACE qsn_indx_ol;

I notice the suffix "_UK" - is that to denote "UNIQUE"? If so, a couple
things there is no UNIQUE in the index, and the NULL will cause any UNIQUE
constraining to not be enforced. If you want the NULL to apply in the
constraint, you need to coalesce it to a defined value Eg
"coalesce(null,'-1')".