[postgis-tickets] [PostGIS] #5280: Can't load dc_place table with shp2pgsql, invalid characer varying(0)

[PostGIS]

#5280: Can't load dc_place table with shp2pgsql, invalid characer varying(0)
---------------------------+---------------------------
 Reporter:  robe           |      Owner:  robe
     Type:  defect         |     Status:  new
 Priority:  blocker        |  Milestone:  PostGIS 3.3.2
Component:  loader/dumper  |    Version:  3.3.x
 Keywords:                 |
---------------------------+---------------------------
 In trying to update for tiger 2022, I seem to have run into an issue with
 loading file -
 https://www2.census.gov/geo/tiger/TIGER2022/PLACE/tl_2022_12_place.zip

 Using my compiled shp2pgsql for from PostGIS 3.3.1.

 this command:


 {{{
 shp2pgsql -D -c -s 4269 -g the_geom   -W "latin1" tl_2022_11_place.dbf
 tiger_staging.dc_place
 }}}


 Is generating a table structure that looks like this:


 {{{
 CREATE TABLE "tiger_staging"."dc_place" (gid serial,
 "statefp" varchar(2),
 "placefp" varchar(5),
 "placens" varchar(8),
 "geoid" varchar(7),
 "name" varchar(100),
 "namelsad" varchar(100),
 "lsad" varchar(2),
 "classfp" varchar(2),
 "pcicbsa" varchar(0),
 "pcinecta" varchar(0),
 "mtfcc" varchar(5),
 "funcstat" varchar(1),
 "aland" float8,
 "awater" float8,
 "intptlat" varchar(11),
 "intptlon" varchar(12));
 }}}


 So it's failing on the create table because of the pcinecta, pcicbsa
 columns being varchar(0) which is illegal.

 I've been able to load other tables.

 When I use ogr_fdw, it creates a structure like this:


 {{{
 CREATE FOREIGN TABLE IF NOT EXISTS staging.tl_2022_11_place(
     fid bigint NULL,
     geom geometry(Polygon,4269) NULL,
     statefp character varying(2) NULL COLLATE pg_catalog."default",
     placefp character varying(5) NULL COLLATE pg_catalog."default",
     placens character varying(8) NULL COLLATE pg_catalog."default",
     geoid character varying(7) NULL COLLATE pg_catalog."default",
     name character varying(100) NULL COLLATE pg_catalog."default",
     namelsad character varying(100) NULL COLLATE pg_catalog."default",
     lsad character varying(2) NULL COLLATE pg_catalog."default",
     classfp character varying(2) NULL COLLATE pg_catalog."default",
     pcicbsa character varying NULL COLLATE pg_catalog."default",
     pcinecta character varying NULL COLLATE pg_catalog."default",
     mtfcc character varying(5) NULL COLLATE pg_catalog."default",
     funcstat character varying(1) NULL COLLATE pg_catalog."default",
     aland bigint NULL,
     awater bigint NULL,
     intptlat character varying(11) NULL COLLATE pg_catalog."default",
     intptlon character varying(12) NULL COLLATE pg_catalog."default"
 )
     SERVER svr_shps
     OPTIONS (layer 'tl_2022_11_place');

 }}}


 Which I think is the right thing to do when no length is provided.

 I haven't checked to see if this is a windows only issue, or just
 something that has always been present and not an issue before.

 When I compare this table to the
 https://www2.census.gov/geo/tiger/TIGER2021/PLACE/tl_2021_12_place.zip

 the 2021 registered these columns and character varying(1)
-- 
Ticket URL: <https://trac.osgeo.org/postgis/ticket/5280>
PostGIS <http://trac.osgeo.org/postgis/>
The PostGIS Trac is used for bug, enhancement & task tracking, a user and developer wiki, and a view into the subversion code repository of PostGIS project.