Re: Indexes for inequalities

On Fri, Apr 13, 2018 at 1:12 PM, Stephen Froehlich <
s(dot)froehlich(at)cablelabs(dot)com> wrote:

> I'm averaging 1.5 ranges per ID over about 3 months.
>
> Still, it's a good opportunity to learn to do it right.
>
> Follow-up questions:
> - what data type do you use to go from RPostgreSQL to a tztrange using
> dbWriteTable? (Or do I need to make it into a text field in R first ...
> which is doable.)
>

​Don't know...​

- how do you build a constraint that there are no overlapping ranges for a
> given ID?
>

Not sure on the exact syntax you'll need here but the following example
seems to be relevant:

​https://www.postgresql.org/docs/10/static/btree-gist.html

CREATE TABLE zoo (
cage INTEGER,
animal TEXT,
EXCLUDE USING GIST (cage WITH =, animal WITH <>)
);

Except you'll use an overlap operation instead of inequality for the one
check.

You'll need ( think...) the extension since both operators must belong to
the same class and btree doesn't have an overlaps

David J.