Re: simple function index question - Mailing list pgsql-sql

From David G. Johnston
Subject Re: simple function index question
Date
Msg-id CAKFQuwbeqMpTanF3h+Mbddot9-100Vj3KbkivAD47M4-VWEwfA@mail.gmail.com
Whole thread Raw
In response to Re: simple function index question  (Igor Neyman <ineyman@perceptron.com>)
Responses Re: simple function index question
List pgsql-sql
On Wed, Mar 23, 2016 at 8:57 AM, Igor Neyman <ineyman@perceptron.com> wrote:

 

From: pgsql-sql-owner@postgresql.org [mailto:pgsql-sql-owner@postgresql.org] On Behalf Of Michael Moore
Sent: Wednesday, March 23, 2016 11:14 AM
To: postgres list <pgsql-sql@postgresql.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.
 

pgsql-sql by date:

Previous
From: Igor Neyman
Date:
Subject: Re: simple function index question
Next
From: Igor Neyman
Date:
Subject: Re: simple function index question