Home > mailing lists

Thread: simple function index question

simple function index question

From

Michael Moore

Date:

23 March 2016, 03:23:55

This:

CREATE INDEX TX_QSET_DIR_MAP_PRI_URI_TYP_UK

ON tx_question_set_dir_map

USING btree

((

CASE

WHEN uri_type = 201900 then

(question_set2tx_question_set ,uri_type, null)

ELSE

(question_set2tx_question_set ,uri_type, question_set_dir_map_key)

END))

TABLESPACE qsn_indx_ol;

results in

ERROR: column "" has pseudo-type record

********** Error **********

ERROR: column "" has pseudo-type record

SQL state: 42P16

Hopefully it is clear what I am attempting to do here. How do I make this happen?

Re: simple function index question

From

"David G. Johnston"

Date:

23 March 2016, 03:39:36

On Tuesday, March 22, 2016, Michael Moore <michaeljmoore@gmail.com> wrote:

This:
CREATE INDEX TX_QSET_DIR_MAP_PRI_URI_TYP_UK
ON tx_question_set_dir_map
USING btree
((
CASE
WHEN uri_type = 201900 then
(question_set2tx_question_set ,uri_type, null)
ELSE
(question_set2tx_question_set ,uri_type, question_set_dir_map_key)
END))
TABLESPACE qsn_indx_ol;

results in
ERROR: column "" has pseudo-type record
********** Error **********

ERROR: column "" has pseudo-type record
SQL state: 42P16

Hopefully it is clear what I am attempting to do here. How do I make this happen?

I suspect the system dislikes your attempt to use an anonymous composite as a column in an index.

I question whether what you are trying to do resembles good data modeling. But if you must you can probably define Ana,Ed composite type and cast the result of the case to it.

David J.

Re: simple function index question

From

Rosser Schwarz

Date:

23 March 2016, 03:54:27

On Tuesday, March 22, 2016, Michael Moore <michaeljmoore@gmail.com> wrote:

ERROR: column "" has pseudo-type record

Just guessing, and don't have time to verify, but you might try casting the null value in the WHEN clause to the same type as the question_set_dir_map_key column in the ELSE clause.

I'd probably also try doing this with two separate indexes; this feels a bit like it might asking the one index to do too much.

--
:wq

Re: simple function index question

From

Igor Neyman

Date:

23 March 2016, 15:56:43

From: pgsql-sql-owner@postgresql.org [mailto:pgsql-sql-owner@postgresql.org] On Behalf Of Michael Moore
Sent: Tuesday, March 22, 2016 8:24 PM
To: postgres list <pgsql-sql@postgresql.org>
Subject: [SQL] simple function index question

This:

CREATE INDEX TX_QSET_DIR_MAP_PRI_URI_TYP_UK

ON tx_question_set_dir_map

USING btree

((

CASE

WHEN uri_type = 201900 then

(question_set2tx_question_set ,uri_type, null)

ELSE

(question_set2tx_question_set ,uri_type, question_set_dir_map_key)

END))

TABLESPACE qsn_indx_ol;

results in

ERROR: column "" has pseudo-type record

********** Error **********

ERROR: column "" has pseudo-type record

SQL state: 42P16

Hopefully it is clear what I am attempting to do here. How do I make this happen?

Try casting NULL to whatever type question_set_dirmap_key is.

Sai it is varchar, then:

……………………………………………..

WHEN uri_type = 201900 then

(question_set2tx_question_set ,uri_type, null::varchar)

……………………………………………………………….

Regards,

Igor Neyman

Re: simple function index question

From

Michael Moore

Date:

23 March 2016, 18:14:17

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.

On Tue, Mar 22, 2016 at 5:54 PM, Rosser Schwarz <rosser.schwarz@gmail.com> wrote:

On Tuesday, March 22, 2016, Michael Moore <michaeljmoore@gmail.com> wrote:
ERROR: column "" has pseudo-type record

Just guessing, and don't have time to verify, but you might try casting the null value in the WHEN clause to the same type as the question_set_dir_map_key column in the ELSE clause.

I'd probably also try doing this with two separate indexes; this feels a bit like it might asking the one index to do too much.

--
:wq

Re: simple function index question

From

bricklen

Date:

23 March 2016, 18:34:10

On Wed, Mar 23, 2016 at 8:14 AM, Michael Moore <michaeljmoore@gmail.com> wrote:

On Tue, Mar 22, 2016 at 5:54 PM, Rosser Schwarz <rosser.schwarz@gmail.com> wrote:
On Tuesday, March 22, 2016, Michael Moore <michaeljmoore@gmail.com> wrote:
ERROR: column "" has pseudo-type record

Just guessing, and don't have time to verify, but you might try casting the null value in the WHEN clause to the same type as the question_set_dir_map_key column in the ELSE clause.

I'd probably also try doing this with two separate indexes; this feels a bit like it might asking the one index to do too much.

Related to your comment, I have successfully used two indexes in the past for conditional indexing like this. YMMV.

Eg.
CREATE INDEX TX_QSET_DIR_MAP_PRI_URI_TYP_UK
ON tx_question_set_dir_map (question_set2tx_question_set ,uri_type, null)
WHERE uri_type = 201900
TABLESPACE qsn_indx_ol;

CREATE INDEX TX_QSET_DIR_MAP_PRI_URI_TYP_2_UK
ON tx_question_set_dir_map (question_set2tx_question_set ,uri_type, question_set_dir_map_key)
WHERE uri_type != 201900
TABLESPACE qsn_indx_ol;

I notice the suffix "_UK" - is that to denote "UNIQUE"? If so, a couple things there is no UNIQUE in the index, and the NULL will cause any UNIQUE constraining to not be enforced. If you want the NULL to apply in the constraint, you need to coalesce it to a defined value Eg "coalesce(null,'-1')".

Re: simple function index question

From

Igor Neyman

Date:

23 March 2016, 18:57:32

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,

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).

Regards,

Igor Neyman

Re: simple function index question

From

Igor Neyman

Date:

23 March 2016, 19:10:13

From: pgsql-sql-owner@postgresql.org [mailto:pgsql-sql-owner@postgresql.org] On Behalf Of Igor Neyman
Sent: Wednesday, March 23, 2016 11:57 AM
To: Michael Moore <michaeljmoore@gmail.com>; postgres list <pgsql-sql@postgresql.org>
Subject: Re: [SQL] simple function index question

Thanks guys,

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).

Regards,

Igor Neyman

Of course, missed “)” at the end:

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));

Regards,

Igor

Re: simple function index question

From

"David G. Johnston"

Date:

23 March 2016, 19:10:59

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.

Re: simple function index question

From

Igor Neyman

Date:

23 March 2016, 19:30:30

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

Re: simple function index question

From

Michael Moore

Date:

23 March 2016, 22:24:05

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