I am loading census tiger data for state 2023. I used this output SELECT loader_generate_nation_script('sh'), as a starting point. I then updated the 2022 references on to 2023 thinking what could possibly change:

To create my tiger_staging tables which will then load direct into tiger_data tables: shp2pgsql -s 4269 -g the_geom -I {local_shp_filepath} tiger_staging.{table_name} > {sql_file_path}

That outputs this staging table

-- tiger_staging.state definition

-- Drop table

-- DROP TABLE tiger_staging.state;

CREATE TABLE tiger_staging.state (
    gid serial4 NOT NULL,
    region varchar(2) NULL,
    division varchar(2) NULL,
    statefp varchar(2) NULL,
    statens varchar(8) NULL,
    geoid varchar(2) NULL, <----- hello this is a new guy on the block
    geoidfq varchar(11) NULL, <----- hello this is a new guy on the block
    stusps varchar(2) NULL,
    "name" varchar(100) NULL,
    lsad varchar(2) NULL,
    mtfcc varchar(5) NULL,
    funcstat varchar(1) NULL,
    aland float8 NULL,
    awater float8 NULL,
    intptlat varchar(11) NULL,
    intptlon varchar(12) NULL,
    the_geom public.geometry(multipolygon, 4269) NULL,
    CONSTRAINT state_pkey PRIMARY KEY (gid)
);
CREATE INDEX state_the_geom_idx ON tiger_staging.state USING gist (the_geom);

Then creates the tiger_data.state from tiger.data

CREATE TABLE tiger_data.state_all(CONSTRAINT pk_state_all PRIMARY KEY (statefp),CONSTRAINT uidx_state_all_stusps  UNIQUE (stusps), CONSTRAINT uidx_state_all_gid UNIQUE (gid) ) INHERITS(tiger.state); 

Then loads that data (THIS IS THE FAILURE BECAUSE COLUMNS DO NOT MATCH)

SELECT loader_load_staged_data(lower('state'), lower('state_all'));

Additional info:

SELECT postgis_full_version() — POSTGIS="3.4.0 0"

SELECT VERSION() — PostgreSQL 16.2

The tiger.state created by PostGIS.

-- tiger.state definition

-- Drop table

-- DROP TABLE tiger.state;

CREATE TABLE tiger.state (
    gid serial4 NOT NULL,
    region varchar(2) NULL,
    division varchar(2) NULL,
    statefp varchar(2) NOT NULL,
    statens varchar(8) NULL,
    stusps varchar(2) NOT NULL,
    "name" varchar(100) NULL,
    lsad varchar(2) NULL,
    mtfcc varchar(5) NULL,
    funcstat varchar(1) NULL,
    aland int8 NULL,
    awater int8 NULL,
    intptlat varchar(11) NULL,
    intptlon varchar(12) NULL,
    the_geom public.geometry NULL,
    CONSTRAINT enforce_dims_the_geom CHECK ((st_ndims(the_geom) = 2)),
    CONSTRAINT enforce_geotype_the_geom CHECK (((geometrytype(the_geom) = 'MULTIPOLYGON'::text) OR (the_geom IS NULL))),
    CONSTRAINT enforce_srid_the_geom CHECK ((st_srid(the_geom) = 4269)),
    CONSTRAINT pk_tiger_state PRIMARY KEY (statefp),
    CONSTRAINT uidx_tiger_state_gid UNIQUE (gid),
    CONSTRAINT uidx_tiger_state_stusps UNIQUE (stusps)
);
CREATE INDEX idx_tiger_state_the_geom_gist ON tiger.state USING gist (the_geom);

This is my first time loading tiger data following this I intend to iterate through and load county and zcta520.

I want to know how to fix this problem, one solution that would work is to delete those 2 columns from the tiger.state table created by postgis, although a workaround that does not seem correct. Second possible solution is to change the tiger_data.state create table statement INHERITS (tiger.state) -> INHERITS(tiger_data.state), but I do not have experience to know what I could be breaking here.