Horton

Thread

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

    Andreas Joseph Krogh <andreak@officenet.no> — 2014-03-23T00:20:28Z

    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

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

    Emre Hasegeli <emre@hasegeli.com> — 2014-03-23T07:13:43Z

    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.

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

    Andreas Kretschmer <akretschmer@spamfence.net> — 2014-03-23T08:59:27Z

    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°

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

    Andreas Joseph Krogh <andreak@officenet.no> — 2014-03-23T09:22:03Z

    På søndag 23. mars 2014 kl. 08:13:43, skrev Emre Hasegeli <emre@hasegeli.com 
<mailto:emre@hasegeli.com>>: 2014-03-23, Andreas Joseph Krogh <
andreak@officenet.no <mailto:andreak@officenet.no>>: I'm trying to define the 
following exclusion-constraint   alter table my_table add EXCLUDE USING gist 
(is_defaultWITH =, 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?   --
 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  

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

    Andreas Joseph Krogh <andreak@officenet.no> — 2014-03-23T10:10:36Z

    På søndag 23. mars 2014 kl. 09:59:27, skrev Andreas Kretschmer <
akretschmer@spamfence.net <mailto: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.   --
 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