Constraint violation loading tiger_data schema from backup

[bpanulla]

After loading the Tiger Geocoder in a staging database I moved it to my main development database on the same server via pg_dump/pg_restore.

The restore generated constraint errors trying to load table zcta_all:

pg_restore: [archiver (db)] Error from TOC entry 5776; 0 1025631 TABLE DATA zcta5_all dbadmin
pg_restore: [archiver (db)] COPY failed for table "zcta5_all": ERROR:  new row for relation "zcta5_all" violates check constraint "enforce_geotype_the_geom"
DETAIL:  Failing row contains (1, 39, 43451, B5, G6350, S, 63411475, 157689, +41.3183010, -083.6174935, N, 0103000020AD100000010000001600000014950D6B2AEB54C0C80A7E1B62AA44...).
CONTEXT:  COPY zcta5_all, line 1: "1	39	43451	B5	G6350	S	63411475	157689	+41.3183010	-083.6174935	N	0103000020AD10000001000000160000001..."

Seems as though the tiger.zcta5 table has a constraint enforcing type of objects:

CONSTRAINT enforce_geotype_the_geom CHECK ((geometrytype(the_geom) = 'MULTIPOLYGON'::text)
    OR  (the_geom IS NULL)),

Most of the records from the 2017 load are POLYGON type, with a handful GEOMETRYCOLLECTION.

This constraint seems to be created on the tiger.zcta5 table by the CREATE EXTENSION for tiger_geocoder

Dropping the constraint allowed the restore to proceed normally.

Discovered on Ubuntu 16.04 with PostGIS installed via package

POSTGIS="2.4.4 r16526" PGSQL="100" GEOS="3.5.0-CAPI-1.9.0 r4084" PROJ="Rel. 4.9.2, 08 September 2015" GDAL="GDAL 1.11.3, released 2015/09/16" LIBXML="2.9.3" LIBJSON="0.11.99" LIBPROTOBUF="1.2.1" RASTER

[robe]

In 17324:

Prep for PostGIS 2.4.7 release
Remove zcta5 geometry constraint
References #4086

[robe]

In 17326:

Remove zcta5 geometry constraint
Closes #4086 for PostGIS 2.5.2