Re: simple function index question

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

>
>
> *From:* pgsql-sql-owner(at)postgresql(dot)org [mailto:
> pgsql-sql-owner(at)postgresql(dot)org] *On Behalf Of *Michael Moore
> *Sent:* Wednesday, March 23, 2016 11:14 AM
> *To:* postgres list <pgsql-sql(at)postgresql(dot)org>
> *Subject:* Re: [SQL] simple function index question
>
>
>
> Thanks guys,
>
> I agree that the data model is suspect. I'm trying to convert from Oracle
> to Postgres and changing table structures is not really an option for me. I
> tried casting the null, ... same problem. It does not like the undefined
> record type as David is saying. I will try his solution and let you know
> how it goes.
>
>
>
>
>
> 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.
​