Re: simple function index question
| От | bricklen |
|---|---|
| Тема | Re: simple function index question |
| Дата | |
| Msg-id | CAGrpgQ_ZfHNHvVksLAO5P6XhJk+iUp3U9WEL0bsBer8FxeH4LA@mail.gmail.com обсуждение исходный текст |
| Ответ на | Re: simple function index question (Michael Moore <michaeljmoore@gmail.com>) |
| Список | 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 recordJust 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;
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 по дате отправления: