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 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 по дате отправления:

Предыдущее
От: Michael Moore
Дата:
Сообщение: Re: simple function index question
Следующее
От: Igor Neyman
Дата:
Сообщение: Re: simple function index question