Thread: Default operator class for data type boolean for access method gist is missing

Default operator class for data type boolean for access method gist is missing

From

Andreas Joseph Krogh

Date:

23 March 2014, 03:22:40

I'm trying to define the following exclusion-constraint

alter table my_table add EXCLUDE USING gist (is_default WITH =, daterange(valid_from, valid_to, '[)') WITH &&) WHERE (is_default = true);

But it fails with:

ERROR: data type boolean has no default operator class for access method "gist"
HINT: You must specify an operator class for the index or define a default operator class for the data type.

I have the btree_gist extension installed.

So, my question is; Is there an existing operator-class I can install to make this work, and if not - how do I make it?

I know I can change is_default to INTEGER and use 1 and 0, but I'd really like to use boolean for this.

Thanks.

--
Andreas Joseph Krogh <andreak@officenet.no> mob: +47 909 56 963
Senior Software Developer / CTO - OfficeNet AS - http://www.officenet.no
Public key: http://home.officenet.no/~andreak/public_key.asc

Re: Default operator class for data type boolean for access method gist is missing

From

Emre Hasegeli

Date:

23 March 2014, 10:13:50

2014-03-23, Andreas Joseph Krogh <andreak@officenet.no>:

I'm trying to define the following exclusion-constraint

alter table my_table add EXCLUDE USING gist (is_default WITH =, daterange(valid_from, valid_to, '[)') WITH &&) WHERE (is_default = true);

is_default should not be in the index as only one

value filtered on the WHERE clause.

Re: Default operator class for data type boolean for access method gist is missing

From

Andreas Kretschmer

Date:

23 March 2014, 11:57:48

Andreas Joseph Krogh <andreak@officenet.no> wrote:

> I'm trying to define the following exclusion-constraint
>  
>
> alter table my_table add EXCLUDE USING gist (is_default WITH =, daterange(valid_from, valid_to, '[)') WITH &&) WHERE
(is_default= true); 
>
>  
> But it fails with:
> ERROR:  data type boolean has no default operator class for access method
> "gist"
> HINT:  You must specify an operator class for the index or define a default
> operator class for the data type.
>  
> I have the btree_gist extension installed.
>  
> So, my question is; Is there an existing operator-class I can install to make
> this work, and if not - how do I make it?

CAST is to int?

test=# create table xx(b bool, tr tsrange, exclude using gist(b with =, tr with &&));
ERROR:  data type boolean has no default operator class for access method "gist"
HINT:  You must specify an operator class for the index or define a default operator class for the data type.
Time: 3,160 ms
test=*# create table xx(b bool, tr tsrange, exclude using gist((b::int) with =, tr with &&));
CREATE TABLE
Time: 3,621 ms

Andreas
--
Really, I'm not out to destroy Microsoft. That will just be a completely
unintentional side effect.                              (Linus Torvalds)
"If I was god, I would recompile penguin with --enable-fly."   (unknown)
Kaufbach, Saxony, Germany, Europe.              N 51.05082°, E 13.56889°

Re: Default operator class for data type boolean for access method gist is missing

From

Andreas Joseph Krogh

Date:

23 March 2014, 12:24:14

På søndag 23. mars 2014 kl. 08:13:43, skrev Emre Hasegeli <emre@hasegeli.com>:

2014-03-23, Andreas Joseph Krogh <andreak@officenet.no>:
I'm trying to define the following exclusion-constraint
alter table my_table add EXCLUDE USING gist (is_default WITH =, daterange(valid_from, valid_to, '[)') WITH &&) WHERE (is_default = true);
 
is_default should not be in the index as only one
value filtered on the WHERE clause.

Thanks.

If I had more filters in WHERE, then what?

Re: Default operator class for data type boolean for access method gist is missing

From

Andreas Joseph Krogh

Date:

23 March 2014, 13:10:43

På søndag 23. mars 2014 kl. 09:59:27, skrev Andreas Kretschmer <akretschmer@spamfence.net>:

Andreas Joseph Krogh <andreak@officenet.no> wrote:

> I'm trying to define the following exclusion-constraint
>
>
> alter table my_table add EXCLUDE USING gist (is_default WITH =, daterange(valid_from, valid_to, '[)') WITH &&) WHERE (is_default = true);
>
>
> But it fails with:
> ERROR: data type boolean has no default operator class for access method
> "gist"
> HINT: You must specify an operator class for the index or define a default
> operator class for the data type.
>
> I have the btree_gist extension installed.
>
> So, my question is; Is there an existing operator-class I can install to make
> this work, and if not - how do I make it?

CAST is to int?

test=# create table xx(b bool, tr tsrange, exclude using gist(b with =, tr with &&));
ERROR: data type boolean has no default operator class for access method "gist"
HINT: You must specify an operator class for the index or define a default operator class for the data type.
Time: 3,160 ms
test=*# create table xx(b bool, tr tsrange, exclude using gist((b::int) with =, tr with &&));
CREATE TABLE
Time: 3,621 ms

Nice workaround, thanks.