Populate a postgres table for 2010 sf1 geo headers based on a scratch table containing each row of geo file as a string

geo-header.sql

INSERT INTO "GEO_HEADER_SF1" ("FILEID", "STUSAB", "SUMLEV", "GEOCOMP", "CHARITER", "CIFSN", "LOGRECNO", "REGION", "DIVISION", "STATE", "COUNTY", "COUNTYCC", "COUNTYSC", "COUSUB", "COUSUBCC", "COUSUBSC", "PLACE", "PLACECC", "PLACESC", "TRACT", "BLKGRP", "BLOCK", "IUC", "CONCIT", "CONCITCC", "CONCITSC", "AIANHH", "AIANHHFP", "AIANHHCC", "AIHHTLI", "AITSCE", "AITS", "AITSCC", "TTRACT", "TBLKGRP", "ANRC", "ANRCCC", "CBSA", "CBSASC", "METDIV", "CSA", "NECTA", "NECTASC", "NECTADIV", "CNECTA", "CBSAPCI", "NECTAPCI", "UA", "UASC", "UATYPE", "UR", "CD", "SLDU", "SLDL", "VTD", "VTDI", "RESERVE2", "ZCTA5", "SUBMCD", "SUBMCDCC", "SDELEM", "SDSEC", "SDUNI", "AREALAND", "AREAWATR", "NAME", "FUNCSTAT", "GCUNI", "POP100", "HU100", "INTPTLAT", "INTPTLON", "LSADC", "PARTFLAG", "RESERVE3", "UGA", "STATENS", "COUNTYNS", "COUSUBNS", "PLACENS", "CONCITNS", "AIANHHNS", "AITSNS", "ANRCNS", "SUBMCDNS", "CD113", "CD114", "CD115", "SLDU2", "SLDU3", "SLDU4", "SLDL2", "SLDL3", "SLDL4", "AIANHHSC", "CSASC", "CNECTASC", "MEMI", "NMEMI", "PUMA", "RESERVED")
SELECT
trim(substring(data,1,6)) AS	fileid,
trim(substring(data,7,2)) AS	stusab,
trim(substring(data,9,3)) AS	sumlev,
trim(substring(data,12,2)) AS	geocomp,
trim(substring(data,14,3)) AS	chariter,
trim(substring(data,17,2)) AS	cifsn,
trim(substring(data,19,7))::integer AS	logrecno,
trim(substring(data,26,1)) AS	region,
trim(substring(data,27,1)) AS	division,
trim(substring(data,28,2)) AS	state,
trim(substring(data,30,3)) AS	county,
trim(substring(data,33,2)) AS	countycc,
trim(substring(data,35,2)) AS	countysc,
trim(substring(data,37,5)) AS	cousub,
trim(substring(data,42,2)) AS	cousubcc,
trim(substring(data,44,2)) AS	cousubsc,
trim(substring(data,46,5)) AS	place,
trim(substring(data,51,2)) AS	placecc,
trim(substring(data,53,2)) AS	placesc,
trim(substring(data,55,6)) AS	tract,
trim(substring(data,61,1)) AS	blkgrp,
trim(substring(data,62,4)) AS	block,
trim(substring(data,66,2)) AS	iuc,
trim(substring(data,68,5)) AS	concit,
trim(substring(data,73,2)) AS	concitcc,
trim(substring(data,75,2)) AS	concitsc,
trim(substring(data,77,4)) AS	aianhh,
trim(substring(data,81,5)) AS	aianhhfp,
trim(substring(data,86,2)) AS	aianhhcc,
trim(substring(data,88,1)) AS	aihhtli,
trim(substring(data,89,3)) AS	aitsce,
trim(substring(data,92,5)) AS	aits,
trim(substring(data,97,2)) AS	aitscc,
trim(substring(data,99,6)) AS	ttract,
trim(substring(data,105,1)) AS	tblkgrp,
trim(substring(data,106,5)) AS	anrc,
trim(substring(data,111,2)) AS	anrccc,
trim(substring(data,113,5)) AS	cbsa,
trim(substring(data,118,2)) AS	cbsasc,
trim(substring(data,120,5)) AS	metdiv,
trim(substring(data,125,3)) AS	csa,
trim(substring(data,128,5)) AS	necta,
trim(substring(data,133,2)) AS	nectasc,
trim(substring(data,135,5)) AS	nectadiv,
trim(substring(data,140,3)) AS	cnecta,
trim(substring(data,143,1)) AS	cbsapci,
trim(substring(data,144,1)) AS	nectapci,
trim(substring(data,145,5)) AS	ua,
trim(substring(data,150,2)) AS	uasc,
trim(substring(data,152,1)) AS	uatype,
trim(substring(data,153,1)) AS	ur,
trim(substring(data,154,2)) AS	cd,
trim(substring(data,156,3)) AS	sldu,
trim(substring(data,159,3)) AS	sldl,
trim(substring(data,162,6)) AS	vtd,
trim(substring(data,168,1)) AS	vtdi,
trim(substring(data,169,3)) AS	reserve2,
trim(substring(data,172,5)) AS	zcta5,
trim(substring(data,177,5)) AS	submcd,
trim(substring(data,182,2)) AS	submcdcc,
trim(substring(data,184,5)) AS	sdelem,
trim(substring(data,189,5)) AS	sdsec,
trim(substring(data,194,5)) AS	sduni,
trim(substring(data,199,14))::float AS	arealand,
trim(substring(data,213,14))::float AS	areawatr,
trim(substring(data,227,90)) AS	name,
trim(substring(data,317,1)) AS	funcstat,
trim(substring(data,318,1)) AS	gcuni,
trim(substring(data,319,9))::integer AS	pop100,
trim(substring(data,328,9))::integer AS	hu100,
trim(substring(data,337,11)) AS	intptlat,
trim(substring(data,348,12)) AS	intptlon,
trim(substring(data,360,2)) AS	lsadc,
trim(substring(data,362,1)) AS	partflag,
trim(substring(data,363,6)) AS	reserve3,
trim(substring(data,369,5)) AS	uga,
trim(substring(data,374,8)) AS	statens,
trim(substring(data,382,8)) AS	countyns,
trim(substring(data,390,8)) AS	cousubns,
trim(substring(data,398,8)) AS	placens,
trim(substring(data,406,8)) AS	concitns,
trim(substring(data,414,8)) AS	aianhhns,
trim(substring(data,422,8)) AS	aitsns,
trim(substring(data,430,8)) AS	anrcns,
trim(substring(data,438,8)) AS	submcdns,
trim(substring(data,446,2)) AS	cd113,
trim(substring(data,448,2)) AS	cd114,
trim(substring(data,450,2)) AS	cd115,
trim(substring(data,452,3)) AS	sldu2,
trim(substring(data,455,3)) AS	sldu3,
trim(substring(data,458,3)) AS	sldu4,
trim(substring(data,461,3)) AS	sldl2,
trim(substring(data,464,3)) AS	sldl3,
trim(substring(data,467,3)) AS	sldl4,
trim(substring(data,470,2)) AS	aianhhsc,
trim(substring(data,472,2)) AS	csasc,
trim(substring(data,474,2)) AS	cnectasc,
trim(substring(data,476,1)) AS	memi,
trim(substring(data,477,1)) AS	nmemi,
trim(substring(data,478,5)) AS	puma,
trim(substring(data,483,18)) AS	reserved
FROM geo_header_staging;