Adding standard set of constraints (rules) to PostGIS `raster` type column 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.
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.)