Postgres Pro
Downloads
Home   >   mailing lists

Re: Indexes for inequalities - Mailing list pgsql-novice

From David G. Johnston
Subject Re: Indexes for inequalities
Date
Msg-id CAKFQuwaHSiVN9OjdQR=qNc97L7UJjroYLrgGQC1wcUqmOrHhiQ@mail.gmail.com
Whole thread Raw
In response to RE: Indexes for inequalities  (Stephen Froehlich <s.froehlich@cablelabs.com>)
List pgsql-novice
Tree view
On Fri, Apr 13, 2018 at 1:12 PM, Stephen Froehlich <s.froehlich@cablelabs.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.

pgsql-novice by date:

Previous
From: Stephen Froehlich
Date:
Subject: RE: Indexes for inequalities
Next
From: "Ron Watkins"
Date:
Subject: RE: dual active 2-node cluster?