2

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.

  • What datatype is rast? – Colin 't Hart Aug 19 '14 at 10:32
  • 1
    @Colin'tHart raster; per the error message. – Craig Ringer Aug 19 '14 at 13:21
  • @Colin'tHart: It is raster. – Zia Aug 19 '14 at 13:29
5

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.

  • hmm.. I have partially understood. Unfortunately, the same error exists even in PostgreSQL-9.3.4(PostGIS-2.1.2) version also. I have just checked. I really appreciate your elaborate explanation. Zia – Zia Aug 19 '14 at 8:24
  • @Zia If the underlying operators for the raster type exist, you might just be able to CREATE OPERATOR CLASS for it yourself. – Craig Ringer Aug 19 '14 at 9:07
  • How to check if the desired operator exists? I was trying to Create Operator Class but not sure how exactly I should proceed. Any sample example? By the way, when I imported my raster ASCII file through Terminal, I do put the Add Constraints flag ("-C" in the following code) and that worked fine before me dropping those constraints. raster2pgsql -d -I -C -M -F -t 100x100 -s 4326 us_tmin.asc chp05.us_tmin | psql -h localhost -p 5432 -U postgres1 -d database1 Now when I am again adding those dropped constraints, there is this issue. – Zia Aug 19 '14 at 14:03
  • @Zia See edit. In short - the required operators don't exist, so you can't just create an opclass. As for what you're saying with -C and "add constraints" ... show me the SQL or the output of \d+ thetablename in psql, I can't really say anything useful w/o that. – Craig Ringer Aug 19 '14 at 14:40
  • Coz you coined-up so many new terminologies, it took me a while to go through some online-tutorials to get myself comfortable :) See edit section makes proper sense why unique indexing is not feasible for raster. What is regtype? – Zia Aug 20 '14 at 8:58
4

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:

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:

  • Thx a lot Erwin. So mainly what you are suggesting is to change the raster datatype to the text. But can't we change it to something like int in order to apply mathematical operators (>, <, = ...) also? Plus I am getting this error: ERROR: syntax error at or near "cast" LINE 1: ...s_tmin ADD CONSTRAINT enforce_scalex_rast unique (cast(rast .... Any idea? – Zia Aug 20 '14 at 9:34
  • 1
    Good idea. It didn't occur to me. There is a possible issue with that though (one that would also affect a native raster btree index) - the limit on varlena index entries of PAGESIZE/2, i.e. 4k or so. I'd look at hashing it and using a unique index on the hash; there is a tiny risk of collisions, but really really tiny. – Craig Ringer Aug 20 '14 at 9:43
  • Actually by using -C flag in the Terminal to import my raster file into the Db, my primary aim is not to bring uniqueness but the following rules in one go: enforce_scalex_rast & enforce_scaley_rast: This rule states that all the rasters must have the same scale X and Y. enforce_max_extent_rast, enforce_srid_rast, enforce_out_db_rast, enforce_width_rast, enforce_height_rast, enforce_same_alignment_rast, enforce_num_band_rast, enforce_pixel_types_rast, enforce_nodata_values_rast and enforce_max_extent_rast. – Zia Aug 20 '14 at 9:50
  • 2
    @Zia Argh, why didn't you say so? You should post a new, corrected question with that information and the other details you've since supplied, and link back to this one for context. – Craig Ringer Aug 20 '14 at 11:38
  • 1
    @Zia: We are not "changing" the data type, just creating a functional index on the unchanged column. Obviously, one or more raster columns are too big. The remaining option would be a unique index on a hash value, like Craig already mentioned. More in this related answer. It's not clear, whether that would solve your actual problem. You seem to have more constraints. – Erwin Brandstetter Aug 20 '14 at 18:27

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.