Adding standard set of constraints (rules) to PostGIS `raster` type column

Question

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):

1. Width and height: This rule states that all the rasters must have the same width and height.
2. Scale X and Y: This rule states that all the rasters must have the same scale X and Y.
3. SRID: This rule states that all rasters must have the same SRID.
4. Same alignment: This rule states that all rasters must be aligned to one another.
5. Maximum extent: This rule states that all rasters must be within the table's maximum extent.
6. 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).
7. NODATA values: This rule states that all raster bands at a specific index must have the same NODATA value.
8. Out-db: This rule states that all raster bands at a specific index must be in-db or out-db, not both.
9. 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.

Answer 1

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.)

Answer 2

Bingo!! I did exactly what Craig said and that worked :)

Although I showed just two check constraints above, there were more actually.

And that was a blunder me trying to create unique constraints rather than check constraints. Thx for rectifying Craig.

---

What I did

1. Imported the raster data using raster2pgsql command from the terminal.
2. Made a copy of all the newly created check constraints (created by the -C flag) name and definitions using \d+ tablename command from PSQL Console (pgAdminIII).
3. Dropped the check constraints using the above stated DROP CONSTRAINT command.
4. Performed some task (which otherwise is not possible with those check constraints on).
5. Re-created the constraints individually using ADD CONSTRAINT command, as defined by the Craig above.