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.
