PostGIS – Adding Standard Constraints to Raster Type Column

constraintpostgispostgresql

Following on from my related prior question, let me elaborate this topic a bit.

raster2pgsql is a raster loader executable that loads GDAL supported raster formats in PostGIS. It has a -C flag defined as follows:

gislinux@gislinux-Precision-M4600:~$ raster2pgsql

Output:

-C  Set the standard set of constraints on the raster
column after the rasters are loaded. Some constraints may fail
if one or more rasters violate the constraint.

When I am importing my raster file like this:

gislinux@gislinux-Precision-M4600:~$ raster2pgsql -d -I -C -M -F -t 100x100 -s 4326
us_tmin_2012.01.asc chp05.us_tmin_new | psql -h localhost -p 5432 -U postgres -d pgrouting

Output:

ANALYZE
NOTICE:  Adding SRID constraint
CONTEXT:  PL/pgSQL function addrasterconstraints line 53 at RETURN
NOTICE:  Adding scale-X constraint

Few constraints have been applied to this new table by the -C flag.

pgrouting=# \d+ chp05.us_tmin_new

Output:

Indexes:
"us_tmin_new_pkey" PRIMARY KEY, btree (rid)
"us_tmin_new_rast_gist" gist (st_convexhull(rast))
Check constraints:
"enforce_height_rast" CHECK (st_height(rast) = ANY (ARRAY[100, 21]))
"enforce_max_extent_rast" CHECK (st_coveredby(st_convexhull(rast),

The standard constraints comprise the following rules (although these constraints are acting independently on all the incoming raster tiles):

Width and height: This rule states that all the rasters must have the same width and height.
Scale X and Y: This rule states that all the rasters must have the same scale X and Y.
SRID: This rule states that all rasters must have the same SRID.
Same alignment: This rule states that all rasters must be aligned to one another.
Maximum extent: This rule states that all rasters must be within the table's
maximum extent.
Number of bands: This rule states that all rasters must have the same number
of bands (means this ensures that all the coming rasters must have a specific number of bands, it's not comparing existing raster tiles).
NODATA values: This rule states that all raster bands at a specific index must have the same NODATA value.
Out-db: This rule states that all raster bands at a specific index must be in-db or out-db, not both.
Pixel type: This rule states that all raster bands at a specific index must be of the same pixel type.

NOW, in order to run ST_MapAlgebra function, I had to drop these std constraints individually, which I did using:

ALTER TABLE chp05.us_tmin_new DROP CONSTRAINT enforce_scalex_rast

in pgAdmin SQL Editor for each of those std constraints. But now I don't know how to bring these standard constraints back? The following command is not working:

ALTER TABLE chp05.us_tmin_new ADD CONSTRAINT enforce_scalex_rast unique (rast);

and giving the following error:

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.

Best Answer

OK, so you:

Dropped a bunch of constraints
Did some work
Attempted to add a completely unrelated random constraint and that failed

You only showed two of the constraints in the output (why?) but the two you showed were CHECK constraints, not UNIQUE constraints. So it makes absolutely no sense to attempt to replace them with a UNIQUE constraint.

You must:

take a note of each constraint name and expression;
Drop them
Do the work
Re-create them with matching name and expression

So, say you have the constraint "enforce_height_rast" CHECK (st_height(rast) = ANY (ARRAY[100, 21])) on table chp05.us_tmin_new. To recreate it, you must use the same constraint expression and name. So that'd be:

ALTER TABLE chp05.us_tmin_new
ADD CONSTRAINT "enforce_height_rast" CHECK (st_height(rast) = ANY (ARRAY[100, 21]));

See how it conveniently matches the psql output for the constraint? You can just copy and paste it.

I have no idea why you were attempting to create a unique constraint instead, when it could not possibly have the effects you described above.

(BTW, your descriptions are slightly inaccurate. Those check constraints don't assert that all the rasters have the same number of bands, etc. They assert that all constraints have number-of-bands equal to some particular value. In other words, the constraint isn't relative to other values in the table, it's checked independently for each row.)

Related Solutions

PostgreSQL – Creating Unique Constraint on PostGIS Raster Column

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.

PostgreSQL – Unique Index vs Unique Constraint with Operator Class

For a moment I thought one might be able to use a pre-existing text_pattern_ops index with the USING INDEX clause when adding a UNIQUE CONSTRAINT. But that fails, because:

ERROR: index "book2_name_like" does not have default sorting behavior

Per documentation:

The index cannot have expression columns nor be a partial index. Also, it must be a b-tree index with default sort ordering. These restrictions ensure that the index is equivalent to one that would be built by a regular ADD PRIMARY KEY or ADD UNIQUE command.

For instance, a unique index like that would allow a FK constraint referencing it, but perform terribly, because it does not support standard operators.
Per documentation:

Note that you should also create an index with the default operator class if you want queries involving ordinary <, <=, >, or >= comparisons to use an index.

So to answer the question:

If you need a UNIQUE CONSTRAINT (among other reasons: to reference it with a FK), your first variant with constraint and index is the only option. Additionally, the default operator class of the index created by the constraint supports more operations (like sorting in default sort order).

If you don't need any of that go with your second variant because, obviously, just one index is cheaper to maintain: just a UNIQUE text_pattern_ops index.

Differences between index and constraint:

How does PostgreSQL enforce the UNIQUE constraint / what type of index does it use?

Alternative with COLLATE "C"

Instead of creating two indexes, there is another alternative for xxx_pattern_ops indexes that may be preferable. The documentation:

The difference from the default operator classes is that the values are compared strictly character by character rather than according to the locale-specific collation rules. This makes these operator classes suitable for use by queries involving pattern matching expressions (LIKE or POSIX regular expressions) when the database does not use the standard "C" locale.

And:

The index automatically uses the collation of the underlying column.

You can create the column without collation (using COLLATE "C"). Then the default operator class behaves the same way as text_pattern_ops would - plus the index can be used with all the standard operators.

CREATE TABLE book2 (
   book_id serial PRIMARY KEY,
   book    text NOT NULL COLLATE "C" UNIQUE  -- that's all!
);

Now, LIKE can use the index:

SELECT * FROM book2 WHERE book LIKE 'foo%';

But ILIKE still can't:

SELECT * FROM book2 WHERE book ILIKE 'foo%';

SQL Fiddle.

Consider a trigram index using the additional module pg_trgm for a more general solution.:

Pattern matching with LIKE, SIMILAR TO or regular expressions in PostgreSQL

Best Answer

Related Solutions

PostgreSQL – Creating Unique Constraint on PostGIS Raster Column

PostgreSQL – Unique Index vs Unique Constraint with Operator Class

Alternative with COLLATE "C"

Related Question