EXCLUDE, Gist and integers

Hi,

Le 16 juil. 2015 2:23 AM, "Ken Tanzer" <ken.tanzer@gmail.com> a écrit :

Hi. I'm looking into adding daterange exclusions to some of my tables.

Following the documentation, I can do this no problem to prevent any
records from overlapping:

CREATE TEMP TABLE foo (
client_id integer,
start_date date NOT NULL,
end_date date,
EXCLUDE using gist (daterange(start_date,end_date) with &&)
);

But what I really want is no overlapping records on a per-client basis.

I optimistically tried this:

CREATE TEMP TABLE foo (
client_id integer,
start_date date NOT NULL,
end_date date,
EXCLUDE using gist (daterange(start_date,end_date) with &&,client_id

with =)

);

But Postgres responds thusly:

ERROR: data type integer 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.

Can someone tell me what's the easiest way to make this work? Thanks in

advance!

You should install the btree_gist extension.

On Wed, Jul 15, 2015 at 10:03 PM, Guillaume Lelarge <guillaume@lelarge.info>
wrote:

Hi,

Le 16 juil. 2015 2:23 AM, "Ken Tanzer" <ken.tanzer@gmail.com> a écrit :

Hi. I'm looking into adding daterange exclusions to some of my tables.

Following the documentation, I can do this no problem to prevent any
records from overlapping:

CREATE TEMP TABLE foo (
client_id integer,
start_date date NOT NULL,
end_date date,
EXCLUDE using gist (daterange(start_date,end_date) with &&)
);

But what I really want is no overlapping records on a per-client basis.

I optimistically tried this:

CREATE TEMP TABLE foo (
client_id integer,
start_date date NOT NULL,
end_date date,
EXCLUDE using gist (daterange(start_date,end_date) with &&,client_id

with =)

);

But Postgres responds thusly:

ERROR: data type integer 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.

Can someone tell me what's the easiest way to make this work? Thanks in

advance!

You should install the btree_gist extension.

That sure did the trick. Thanks!

Ken

p.s., in case it's helpful for anyone, as superuser, "CREATE EXTENSION
btree_gist;"

--
AGENCY Software
A Free Software data system
By and for non-profits
*http://agency-software.org/ <http://agency-software.org/&gt;*
*https://agency-software.org/demo/client
<https://agency-software.org/demo/client&gt;*
ken.tanzer@agency-software.org
(253) 245-3801

Subscribe to the mailing list
<agency-general-request@lists.sourceforge.net?body=subscribe> to
learn more about AGENCY or
follow the discussion.