Re: simple function index question - Mailing list pgsql-sql

From bricklen
Subject Re: simple function index question
Date
Msg-id CAGrpgQ_ZfHNHvVksLAO5P6XhJk+iUp3U9WEL0bsBer8FxeH4LA@mail.gmail.com
Whole thread Raw
In response to Re: simple function index question  (Michael Moore <michaeljmoore@gmail.com>)
List pgsql-sql


On Wed, Mar 23, 2016 at 8:14 AM, Michael Moore <michaeljmoore@gmail.com> wrote:

On Tue, Mar 22, 2016 at 5:54 PM, Rosser Schwarz <rosser.schwarz@gmail.com> wrote:
On Tuesday, March 22, 2016, Michael Moore <michaeljmoore@gmail.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')".

pgsql-sql by date:

Previous
From: Michael Moore
Date:
Subject: Re: simple function index question
Next
From: Igor Neyman
Date:
Subject: Re: simple function index question