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

От:

Andreas Kretschmer <akretschmer@spamfence.net>

Дата:

23 марта 2014 г. в 08:57:48

Andreas Joseph Krogh  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

От:

Emre Hasegeli <emre@hasegeli.com>

Дата:

23 марта 2014 г. в 07: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.

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

От:

Andreas Joseph Krogh <andreak@officenet.no>

Дата:

23 марта 2014 г. в 00: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

От:

Andreas Joseph Krogh <andreak@officenet.no>

Дата:

23 марта 2014 г. в 09: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

От:

Andreas Joseph Krogh <andreak@officenet.no>

Дата:

23 марта 2014 г. в 10: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.