2

I have a table like

my_type | text
my_box  | box

where my_box is of type box from the geometric types, and not from postgis.

I want a way to ensure that there are no duplicate boxes across all values of my_type. Usually, if it were an integer or somesuch, I'd just create a unique index across both columns. However, when trying to create an index on a box, I get

data type box has no default operator class for access method "btree"
HINT:  You must specify an operator class for the index or define a default operator class for the data type.

Can I specify an operator class for the index? Do I cast the box as something else? (I'd like this solution to also apply to polygon, if possible) Do I create a function index?

Note that this is specifically NOT having to do with PostGis (unless that's the only way to do this - I'm not even sure if PostGis applies to geometric types). That's all that comes up when I google this, though.

Erwin Brandstetter
  • 605,456
  • 145
  • 1,078
  • 1,228
Hoopes
  • 3,943
  • 4
  • 44
  • 60
  • You want a [GIST](https://www.postgresql.org/docs/current/gist-builtin-opclasses.html) index –  Sep 16 '21 at 21:37
  • The answer below says `btree` is the only one available to do a unique constraint with? – Hoopes Sep 18 '21 at 19:04

1 Answers1

2

The current version Postgres 13 (as well as Postgres 12) supports the index types brin, gist and spgist for the built-in geometric type box. (No btree index.)

But none of these "access methods" (a.k.a. "index type") supports a UNIQUE constraint or index. UNIQUE is only implemented for btree indexes.

You can create a UNIQUE index on the text-representation of the value or a on hash value. There are many equivalent syntax variants for the same box value, but if you cast box to text you get the canonical form:

SELECT '((1, 1), (2, 2))'::box::text AS b1
     , '2, 2, 1, 1'::box::text AS b2;

     b1      |     b2
-------------+-------------
 (2,2),(1,1) | (2,2),(1,1)

Solutions

This UNIQUE, multicolumn, expression (btree!) index should work for you:

CREATE UNIQUE INDEX tbl_box_uni ON tbl (my_type, (my_box::text));

I didn't spell out btree, as that's the default. And the only option for a UNIQUE index anyway.

It's unlikely that the canonical text representation of boxes will change in future versions, but I am also not aware of any guarantees.

To disallow any two boxes with the same my_type to overlap (a more common requirement!) use an exclusion constraint. See:

This is much more restrictive, but also rules out exact duplicates.

db<>fiddle here

Erwin Brandstetter
  • 605,456
  • 145
  • 1,078
  • 1,228
  • Thanks! (extra chars...) – Hoopes Sep 17 '21 at 17:12
  • If the table has many rows (eg hundreds of millions), would it make sense to do some sort of function index, such that the index itself is smaller? (eg https://www.postgresql.org/docs/12/indexes-expressional.html). Especially if I'll do it across more than 2 columns? Much obliged, thanks for your time! – Hoopes Sep 18 '21 at 19:06
  • 1
    @Hoopes: My unique index with a cast is just another "functional index" (= expression index). The cast is lossless. You might consider a hash function - at the cost of possible hash collisions. It all depends. On actual numbers (rows in table, cardinality in box column, expected growth / churn), you willingness to risk hash collisions, performance characteristics and requirements, typical queries, available resources, read/write ratio, ... Here is a related answer: https://dba.stackexchange.com/a/299107/3684. You might ask a new question disclosing all relevant information. – Erwin Brandstetter Sep 19 '21 at 23:22