Creating a unique constraint on a PostGIS 'raster' type column

Question

I am using the following command to add constraints to one of the raster image in PostGIS-2.1.3 (PostgreSQL-9.1.14).

ALTER TABLE schema1.table1 ADD CONSTRAINT enforce_scalex_rast unique (rast);

But getting the following errors:

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

Kindly someone help me to fix this error up. I have no basic idea about the operator classes. Thx.

Zia.

Answer 1

A unique constraint creates a unique index to implement the constraint. The only index type in PostgreSQL that supports unique indexes is the default b-tree index type; you can't make a unique GIN or GiST index, e.g.:

regress=> create unique index indexname on test USING GiST(id) ;
ERROR:  access method "gist" does not support unique indexes

Data types must specifically support different kinds of indexing, and this data type doesn't seem to support b-tree indexes. So you can't make a unique constraint on this type.

---

Details:

There are a few index types used by PostgreSQL. Types must provide an operator class for each index type they support. For your purposes there are really two groups of interest:

• b-tree - the default, what you get when you don't say the index type. Supports equality, less-than, and greater-than operations. Supports creation of unique indexes.

• GiST and GIN - popular flexible index types, used heavily by PostGIS, fulltext search, etc. Does not support creation of unique indexes. Supports more kinds of comparisons than b-tree.

Most data types support b-tree indexing, which is the default, by providing a b-tree operator class.

Some data types, especially those used in GIS, don't provide a b-tree opclass, either because nobody wanted one, or because it doesn't make much sense.

This type seems to be one of them. Nobody's added b-tree support for the raster type, so you can't create a b-tree index on it. As only b-trees are supported for unique indexes, and a unique constraint creates a unique index to implement the constraint, that means you can't make a unique constraint on a raster type.

You might find that a newer PostgreSQL/PostGIS version has the desired opclass. Or might not. I haven't checked. Either way, you're out of luck in 9.1.

---

Lets try creating an operator class for b-tree support of the raster type. The docs on operator classes and families shows the strategy numbers for the b-tree index strategy, showing that we need support for =, <, <=, > and >= for raster. Do we have those?

regress=# select oprname from pg_operator where oprname IN ('=', '<=', '<', '>', '>=') and oprleft = 'raster'::regtype and oprright = 'raster'::regtype;
 oprname 
---------
 =
(1 row)

Nope. So we can't create a b-tree operator class for raster without first implementing ordering operators for it and making sure that those operators follow the required rules about transitivity, commutivity, etc.

That's not overly surprisingly, since it's not clear what exactly raster < raster would actually mean. It does, however, mean you can't enable unique index support easily for raster.

---

Another possibility would be to hash the input with md5 or similar, then create a unique index on the hash. This solves the index operator type issues and the problems with maximum b-tree entry sizes.

Creating indexes on hashed values is well documented elsewhere.

Answer 2

As Craig explained very well, you cannot create a unique index on the type raster without all the necessary operators.

Your second best bet to enforce uniqueness is to create a functional index on the text representation:

CREATE UNIQUE INDEX us_tmin_enforce_scalex_rast
ON chp05.us_tmin (cast(rast as text))

Note that this cannot be implemented as CONSTRAINT, as my code example suggested in my first draft. Unique constraints only work on columns, not expressions. Needs to be a UNIQUE INDEX.

Details about cast() in a functional index:

• How to create an index on an integer json property in postgres

However, there may be multiple possible text representations for the same raster value (as identified by the = operator). It is your responsibility to rule out side effects from that.

Since we cast the value, we get the canonical text representation and additional variants due to noise characters in allowed input literals are folded already. But there may be more.

If one or more columns are to big to be indexed, the only remaining option is to create the index on a hash value:

• Index max row size error