[prev in list] [next in list] [prev in thread] [next in thread]
List: postgresql-sql
Subject: Re: [SQL] simple function index question
From: Michael Moore <michaeljmoore () gmail ! com>
Date: 2016-03-23 19:24:01
Message-ID: CACpWLjMKgqO=9ZJoCKmzuqWsdup2-fANxH8kxayP56d8fHBw9Q () mail ! gmail ! com
[Download RAW message or body]
On Wed, Mar 23, 2016 at 9:30 AM, Igor Neyman <ineyman@perceptron.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
[Attachment #3 (text/html)]
<div dir="ltr"><div><br></div><div><br></div><div><br></div><div><br></div><div \
class="gmail_extra"><br><div class="gmail_quote">On Wed, Mar 23, 2016 at 9:30 AM, \
Igor Neyman <span dir="ltr"><<a href="mailto:ineyman@perceptron.com" \
target="_blank">ineyman@perceptron.com</a>></span> wrote:<br><blockquote \
class="gmail_quote" style="margin:0px 0px 0px \
0.8ex;border-left-width:1px;border-left-color:rgb(204,204,204);border-left-style:solid;padding-left:1ex">
<div lang="EN-US" link="blue" vlink="purple">
<div>
<div>
<div>
<div><span class="">
<blockquote style="border-style:none none none \
solid;border-left-color:rgb(204,204,204);border-left-width:1pt;padding:0in 0in 0in \
6pt;margin-left:4.8pt;margin-right:0in"> <div>
<div>
<div>
<div>
<p class="MsoNormal"><span \
style="font-size:11pt;font-family:Calibri,sans-serif;color:rgb(31,73,125)">Interestingly \
a version of this:</span><u></u><u></u></p> <p class="MsoNormal"><span \
style="font-size:11pt;font-family:Calibri,sans-serif;color:rgb(31,73,125)"> \
</span><u></u><u></u></p> <p class="MsoNormal"><span style="font-family:'Courier \
New'">CREATE INDEX TX_QSET_DIR_MAP_PRI_URI_TYP_UK</span><u></u><u></u></p> <p \
class="MsoNormal"><span style="font-family:'Courier New'"> ON \
tx_question_set_dir_map</span><u></u><u></u></p> <p class="MsoNormal"><span \
style="font-family:'Courier New'"> USING btree</span><u></u><u></u></p> <p \
class="MsoNormal"><span style="font-family:'Courier New'"> \
(question_set2tx_question_set ,uri_type,</span><u></u><u></u></p> <p \
class="MsoNormal"><span style="font-family:'Courier New'"> (CASE WHEN \
uri_type = 201900 THEN null::varchar(100) </span><u></u><u></u></p>
<p class="MsoNormal"><span style="font-family:'Courier New'"> ELSE \
question_set_dir_map_key END);</span><u></u><u></u></p> <p class="MsoNormal"><span \
style="font-family:'Courier New'"> </span><u></u><u></u></p> <p \
class="MsoNormal"><span \
style="font-size:11pt;font-family:Calibri,sans-serif;color:rgb(31,73,125)">Worked for \
me. Try it if you are still interested.</span><u></u><u></u></p> <p \
class="MsoNormal"><span \
style="font-size:11pt;font-family:Calibri,sans-serif;color:rgb(31,73,125)">Just do \
proper casting of null (it was varchar(100) in my case).</span><u></u><u></u></p> <p \
class="MsoNormal"><u></u> <u></u></p> </div>
</div>
</div>
</div>
</blockquote>
<div>
<p class="MsoNormal"><u></u> <u></u></p>
</div>
<div>
<div>
<p class="MsoNormal"><span style="font-family:Arial,sans-serif">This is a \
multi-column index - with the third column being an expression - as opposed to a \
single-column index of a composite.<u></u><u></u></span></p> </div>
</div>
<div>
<div>
<p class="MsoNormal"><span style="font-family:Arial,sans-serif"><u></u> \
<u></u></span></p> </div>
</div>
<div>
<div>
<p class="MsoNormal"><span style="font-family:Arial,sans-serif">David \
J.<u></u><u></u></span></p> </div>
</div>
</span><div>
<div>
<p class="MsoNormal"><span \
style="font-family:Arial,sans-serif"><u></u><u></u></span></p> </div>
<p class="MsoNormal"> <span \
style="color:rgb(31,73,125)">________________________________________________________________________________</span><u></u><u></u></p>
<p class="MsoNormal"><span \
style="font-size:11pt;font-family:Calibri,sans-serif;color:rgb(31,73,125)"><u></u> \
<u></u></span></p> <p class="MsoNormal"><span \
style="font-size:11pt;font-family:Calibri,sans-serif;color:rgb(31,73,125)">You are \
right.<u></u><u></u></span></p> <p class="MsoNormal"><span \
style="font-size:11pt;font-family:Calibri,sans-serif;color:rgb(31,73,125)">But the \
question is whether OP needs a single_column_index_of_a_composite or \
multi_column_index will do.<u></u><u></u></span></p> <p class="MsoNormal"><span \
style="font-size:11pt;font-family:Calibri,sans-serif;color:rgb(31,73,125)"><u></u> \
<u></u></span></p> <p class="MsoNormal"><span \
style="font-size:11pt;font-family:Calibri,sans-serif;color:rgb(31,73,125)">Regards,<u></u><u></u></span></p>
<p class="MsoNormal"><span \
style="font-size:11pt;font-family:Calibri,sans-serif;color:rgb(31,73,125)">Igor \
Neyman</span></p></div></div></div></div></div></div></blockquote><div> \
</div></div><div>Okay, one more thing to clear up. I <u>should have</u> 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. </div><div><br></div>I think part \
of my problem is that I am trying to solve a problem in the same way that Oracle \
solved it. <div>With Oracle considers null = null when evaluated within the context \
of an index</div><div>For example:</div><div><div><font face="monospace, monospace" \
size="1"><b>create table abc ( a numeric, b numeric, c \
numeric);</b></font></div><div><font face="monospace, monospace" size="1"><b>create \
unique index abc_is on abc </b></font><b \
style="font-family:monospace,monospace;font-size:x-small"> (a,b,(case when b=0 then \
null else c END));</b></div><div><font face="monospace, monospace" size="1"><b>insert \
into abc values ( 1,0,4 );</b></font></div><div><font face="monospace, monospace" \
size="1"><b>insert into abc values ( 1,0,5 );</b></font></div></div><div>The second \
insert WILL violate the UNIQUE index constraint in Oracle. </div><div>Thanks to \
bricklin for pointing out to me that such is not the case for Postgres. Wow, \
that's REALLY important. </div><div><br></div><div>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. \
</div><div><br></div><div>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. </div><div><br></div><div>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. </div><div>Thanks \
everybody!</div><div>Mike</div><div><br></div><div><br></div></div></div>
[prev in list] [next in list] [prev in thread] [next in thread]
Configure |
About |
News |
Add a list |
Sponsored by KoreLogic