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 c

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

StackExchange Database Administrators Q#74469, answer score: 6