This generated nationscript includes the following line: PSQL=${PGBIN}/p (...) I have tried 'umpteen' variations of replacements, including the logical one: the physical path to the psql file.

I am running the script as the postgres user who has permissions on the path to the psql file. This is an installation of enterpriseDB postgres (standard) on a big-honking mac running Yosemite. So the postgres user owns the /library/postgresql/9.5/bin folder.

And I have executed sudo su postgres in the folder where the nationscript.sh resides, and tried to execute it without that as well.

In any case the first use of the ${PSQL} macro is on line 15 through 22 or so. and that's the heart of the script and nothing will make these lines do anything but fail.

So it's probably some bash syntax arcanity that I can't deduce. But this is all 'supposed' to be generated script out of the postgis-tiger-geocoder extension.




export PGBIN=/library/postgresql/9.5/bin

export PGPORT=5432

export PGHOST=localhost

export PGUSER=postgres

export PGPASSWORD=uggabuggababytellmewhyowhyowhy

export PGDATABASE=tiger

PSQL=${PGBIN}/p (...)"

cd /volumes/metro/_data/gisdata

wget ftp://ftp2.census.gov/geo/tiger/TIGER2015/STATE/ --no-parent --relative --

recursive --level=1 --accept=zip --mirror --reject=html 

cd /volumes/metro/_data/gisdata/ftp2.census.gov/geo/tiger/TIGER2015/STATE

rm -f ${TMPDIR}/*.*


${PSQL} -c "CREATE SCHEMA tiger_staging;"

for z in tl_*state.zip ; do $UNZIPTOOL -o -d $TMPDIR $z; done

for z in */tl_*state.zip ; do $UNZIPTOOL -o -d $TMPDIR $z; done


${PSQL} -c "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); "
${SHP2PGSQL} -D -c -s 4269 -g the_geom   -W "latin1" tl_2015_us_state.dbf tiger_staging.state | ${PSQL}
${PSQL} -c "SELECT loader_load_staged_data(lower('state'), lower('state_all')); "
    ${PSQL} -c "CREATE INDEX tiger_data_state_all_the_geom_gist ON tiger_data.state_all USING gist(the_geom);"
    ${PSQL} -c "VACUUM ANALYZE tiger_data.state_all"
cd /volumes/metro/_data/gisdata
wget ftp://ftp2.census.gov/geo/tiger/TIGER2015/COUNTY/ --no-parent --relative --recursive --level=1 --accept=zip --mirror --reject=html 
cd /volumes/metro/_data/gisdata/ftp2.census.gov/geo/tiger/TIGER2015/COUNTY
rm -f ${TMPDIR}/*.*
${PSQL} -c "CREATE SCHEMA tiger_staging;"
for z in tl_*county.zip ; do $UNZIPTOOL -o -d $TMPDIR $z; done
for z in */tl_*county.zip ; do $UNZIPTOOL -o -d $TMPDIR $z; done

${PSQL} -c "CREATE TABLE tiger_data.county_all(CONSTRAINT pk_tiger_data_county_all PRIMARY KEY (cntyidfp),CONSTRAINT uidx_tiger_data_county_all_gid UNIQUE (gid)  ) INHERITS(tiger.county); " 
${SHP2PGSQL} -D -c -s 4269 -g the_geom   -W "latin1" tl_2015_us_county.dbf tiger_staging.county | ${PSQL}
${PSQL} -c "ALTER TABLE tiger_staging.county RENAME geoid TO cntyidfp;  SELECT loader_load_staged_data(lower('county'), lower('county_all'));"
    ${PSQL} -c "CREATE INDEX tiger_data_county_the_geom_gist ON tiger_data.county_all USING gist(the_geom);"
    ${PSQL} -c "CREATE UNIQUE INDEX uidx_tiger_data_county_all_statefp_countyfp ON tiger_data.county_all USING btree(statefp,countyfp);"
    ${PSQL} -c "CREATE TABLE tiger_data.county_all_lookup ( CONSTRAINT pk_county_all_lookup PRIMARY KEY (st_code, co_code)) INHERITS (tiger.county_lookup);"
    ${PSQL} -c "VACUUM ANALYZE tiger_data.county_all;"
    ${PSQL} -c "INSERT INTO tiger_data.county_all_lookup(st_code, state, co_code, name) SELECT CAST(s.statefp as integer), s.abbrev, CAST(c.countyfp as integer), c.name FROM tiger_data.county_all As c INNER JOIN state_lookup As s ON s.statefp = c.statefp;"
    ${PSQL} -c "VACUUM ANALYZE tiger_data.county_all_lookup;" 

The ellipse and double-quote should not be there and should read as:


The ellipse (...) you are seeing is not part of the actual text and is just telling you that there is more data not being shown. Your 'tiger.loader_platform.geocoder' field should end like this for os= 'sh':

cd ${staging_fold}

Here is a simple method to see the remaining text:

select unnest(string_to_array(declare_sect,E'\n')) from loader_platform where os ='sh'

It's a little tricky for sure!

I would recommend to leave the defaults and create a new record where os='yourid' for your use.

You can also change the "Max. characters per column" setting in pgAdmin:

File > Options > Query Tool > Max. characters per column

I bumped mine up to 512.

  • Ok, that explains quite a bit of what I'm experiencing. And 'unnest' is an interesting concept on its own. I'll try it! Thanks/k (...) – metro-mark Jun 29 '16 at 18:34
  • updated answer to include pgAdmin setting to see more text. – kttii Jun 29 '16 at 18:45
  • another great tip!! – metro-mark Jun 30 '16 at 5:37

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.