Re: simple function index question

On Wed, Mar 23, 2016 at 9:30 AM, Igor Neyman <ineyman(at)perceptron(dot)com> wrote:

> Interestingly a version of this:
>
>
>
> CREATE INDEX TX_QSET_DIR_MAP_PRI_URI_TYP_UK
>
> ON tx_question_set_dir_map
>
> USING btree
>
> (question_set2tx_question_set ,uri_type,
>
> (CASE WHEN uri_type = 201900 THEN null::varchar(100)
>
> ELSE question_set_dir_map_key END);
>
>
>
> Worked for me. Try it if you are still interested.
>
> Just do proper casting of null (it was varchar(100) in my case).
>
>
>
>
>
> ​This is a multi-column index - with the third column being an expression
> - as opposed to a single-column index of a composite.
>
>
>
> David J.
>
> ​
>
>
> ________________________________________________________________________________
>
>
>
> You are right.
>
> But the question is whether OP needs a single_column_index_of_a_composite
> or multi_column_index will do.
>
>
>
> Regards,
>
> Igor Neyman
>

Okay, one more thing to clear up. I *should have* made the index UNIQUE for
the sake of this discussion even though it will ultimately not be unique.
It's a shop standard and if I had to explain the reasoning behind it, you
would probably need a morphine drip for the pain.

I think part of my problem is that I am trying to solve a problem in the
same way that Oracle solved it.
With Oracle considers null = null when evaluated within the context of an
index
For example:
*create table abc ( a numeric, b numeric, c numeric);*
*create unique index abc_is on abc ** (a,b,(case when b=0 then null else c
END));*
*insert into abc values ( 1,0,4 );*
*insert into abc values ( 1,0,5 );*
The second insert WILL violate the UNIQUE index constraint in Oracle.
Thanks to bricklin for pointing out to me that such is not the case for
Postgres. Wow, that's REALLY important.

I tried Igor's solution and it works just fine ( it does what it is
supposed to do), unfortunately it does not do what I want, due to postgres
handling of nulls in UNIQUE indexes.

I am going to use the "two index" method that bricklen proposed. By the
way, this is not allowed in Oracle since the WHERE clause in CREATE INDEX
is not supported.

This makes perfect sense in regards to enforcing uniqueness, however it
raises all sorts of question about how such indexes (ones that use where
clauses) would be used by the query optimizer. That's a question for
another thread.
Thanks everybody!
Mike