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.

• Zia: For discussion, go link.
• Erwin Brandstetter: We have established why the presented code is not working under your previous question. This question should probably be about how to best implement your constraints - which you need to describe.
• Craig Ringer: @Zia - this isn't really an improvement. Think about what was confusing last time you asked and explain that. Talk about the -C flag, what it does, why it isn't enough for you. Explain what you're trying to achieve, what problem your're trying to solve with this. If some existing constraints are like what you want, show their definitions (\d tablename in psql will show them) so we know what you're talking about. Explain what you meant by your comment "my primary aim is not to bring uniqueness but the following rules in one go" in the previous question. Edit the question please.
• Craig Ringer: @Zia We have already established that the code will not work. You can't just create a unique constraint. You know why now. So this needs to be about what to do instead - and to answer that, we need to know what you're trying to achieve, what actual problem you're attempting to solve.
• Zia: @Craig and Erwin I hope it is better now.

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

• Zia: ALTER TABLE us_tmin ADD CONSTRAINT "enforce_max_extent_rast" CHECK (st_coveredby(st_convexhull(rast), '01030000 20E61000000***some big aplhanumric string***10000002D0000008AAAAAAAAA9E50C00200000000F0384083840'::geometry)) is giving error ERROR: Invalid hex string, length (1489) has to be a multiple of two! ********** Error ********** ERROR: Invalid hex string, length (1489) has to be a multiple of two! SQL state: XX000. Any idea how to add this enforce_max_extent_rast constraint?
• Zia: Your explanation _all constraints have number-of-bands equal to some particular value_ has confused me. Please explain again what you meant? As far as I know, enforce_num_bands_rast is assuring that all the raster tiles in the table have the same number of bands.
• Craig Ringer: @Zia The point is that a check constraint can't say "this new row I'm inserting must have the same value as all prior rows". It can only say "this row I'm inserting must satisfy this constraint, independent of any other rows that might or might not exist". The constraint can only say "this must be true".
• Zia: hmm.. That's true. Thanks for the correction. I have added proper explanation.

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.