PostgreSQL – Creating Unique Constraint on PostGIS Raster Column

constraintpostgispostgresqlraster

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.

Best Answer

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.

Related Question