title: How to ensure a table of Postgres range types totally cover a range?
body: <p>I have a table of Postgres <code>numrange</code> types, which I'm calling <code>buckets</code>. The ranges on each of these buckets can be any size, but altogether they need to cover a certain range ([0 to 2<sup>160</sup>], in my case).</p>
  
  <p>Currently, I have a custom aggregation function that uses <code>+</code>, which is overloaded for <code>numrange + numrange</code> (<code>SUM</code> is not implemented for ranges). The rows need to be sorted before aggregation, so that the pieces stay contiguous as they are accumulated. Postgres's <code>CREATE AGGREGATE</code> statement allows for the <code>SORTOP</code> argument, and I provide the <code>&lt;</code> operator, which is <a href="https://www.postgresql.org/docs/current/functions-range.html" rel="nofollow noreferrer">implemented for ranges</a>.</p>
  
  <p>My problem is that when I implement this aggregator like in my code examples below, I get this error:</p>
  
  <blockquote>
    <p><code>ERROR 42883 (undefined_function) operator does not exist: numrange &lt; numrange</code></p>
  </blockquote>
  
  <p>If I remove <code>SORTOP</code>, this aggregator does work in the trivial case (two buckets that are side-by-side), but give more buckets, it predictably doesn't work:</p>
  
  <blockquote>
    <p><code>ERROR 22000 (data_exception) result of range union would not be contiguous</code></p>
  </blockquote>
  
  <p>I'm aware that I could get this error even if the sort operator does work, if my data is not actually contiguous, but I'm fine getting this error in that case.</p>
  
  <p>Here's what I'm working with right now that gives me the first error:</p>
  
  <pre><code>CREATE FUNCTION numrange_accum_sfunc(numrange, numrange)
  RETURNS numrange AS
  $$
          SELECT $1 + $2;
  $$ LANGUAGE 'sql' STRICT;
  
  CREATE AGGREGATE numrange_accum(numrange)
  (
    STYPE = numrange,
    SFUNC = numrange_accum_sfunc,
    SORTOP = &lt;
  );
  
  CREATE OR REPLACE FUNCTION enforce_bucket_coverage() RETURNS trigger AS $$
  DECLARE
      bucket_range numrange := numrange(0,0);
  BEGIN
      LOCK TABLE buckets IN EXCLUSIVE MODE;
  
      SELECT numrange_accum(range)
      FROM buckets
      INTO bucket_range;
  
      IF bucket_range &lt;&gt; numrange(0,1461501637330902918203684832716283019655932542977) THEN
          RAISE EXCEPTION 'Bucket ranges must completely cover the range from 0 to 2^160 (inclusive). Coverage after inserting was %', bucket_range;
      END IF;
  
      RETURN NEW;
  END;
  $$ LANGUAGE plpgsql;
  </code></pre>