-- Note: the aliases don't matter and are included mor for the 
-- reader than pg, which only goes by order

insert into census2000.sf1geo select
   nullif(trim(substr(line, 1, (6-1)+1)), '') as FILEID,
   nullif(trim(substr(line, 7, (8-7)+1)), '') as STUSAB ,
   nullif(trim(substr(line, 9, (11-9)+1)), '') as SUMLEV  ,
   nullif(trim(substr(line, 12, (13-12)+1)), '') as GEOCOMP   ,
   nullif(trim(substr(line, 14, (16-14)+1)), '') as CHARITER  ,
   nullif(trim(substr(line, 17, (18-17)+1)), '') as CIFSN,
   nullif(trim(substr(line, 19, (25-19)+1)), '') as LOGRECNO   ,
   nullif(trim(substr(line, 26, (26-26)+1)), '') as REGION ,
   nullif(trim(substr(line, 27, (27-27)+1)), '') as DIVISION,
   nullif(trim(substr(line, 28, (29-28)+1)), '') as STATECE  ,
   nullif(trim(substr(line, 30, (31-30)+1)), '') as STATE ,
   nullif(trim(substr(line, 32, (34-32)+1)), '') as COUNTY  ,
   nullif(trim(substr(line, 35, (36-35)+1)), '') as COUNTYSC   ,
   nullif(trim(substr(line, 37, (41-37)+1)), '') as COUSUB  ,
   nullif(trim(substr(line, 42, (43-42)+1)), '') as COUSUBCC   ,
   nullif(trim(substr(line, 44, (45-44)+1)), '') as COUSUBSC   ,
   nullif(trim(substr(line, 46, (50-46)+1)), '') as PLACE ,
   nullif(trim(substr(line, 51, (52-51)+1)), '') as PLACECC  ,
   nullif(trim(substr(line, 53, (53-53)+1)), '') as PLACEDC  ,
   nullif(trim(substr(line, 54, (55-54)+1)), '') as PLACESC  ,
   nullif(trim(substr(line, 56, (61-56)+1)), '') as TRACT ,
   nullif(trim(substr(line, 62, (62-62)+1)), '') as BLKGRP  ,
   nullif(trim(substr(line, 63, (66-63)+1)), '') as BLOCK ,
   nullif(trim(substr(line, 67, (68-67)+1)), '') as IUC,
   nullif(trim(substr(line, 69, (73-69)+1)), '') as CONCIT ,
   nullif(trim(substr(line, 74, (75-74)+1)), '') as CONCITCC  ,
   nullif(trim(substr(line, 76, (77-76)+1)), '') as CONCITSC  ,
   nullif(trim(substr(line, 78, (81-78)+1)), '') as AIANHH ,
   nullif(trim(substr(line, 82, (86-82)+1)), '') as AIANHHFP  ,
   nullif(trim(substr(line, 87, (88-87)+1)), '') as AIANHHCC  ,
   nullif(trim(substr(line, 89, (89-89)+1)), '') as AIHHTLI,
   nullif(trim(substr(line, 90, (92-90)+1)), '') as AITSCE ,
   nullif(trim(substr(line, 93, (97-93)+1)), '') as AITS,
   nullif(trim(substr(line, 98, (99-98)+1)), '') as AITSCC ,
   nullif(trim(substr(line, 100, (104-100)+1)), '') as ANRC ,
   nullif(trim(substr(line, 105, (106-105)+1)), '') as ANRCCC  ,
   nullif(trim(substr(line, 107, (110-107)+1)), '') as MSACMSA   ,
   nullif(trim(substr(line, 111, (112-111)+1)), '') as MASC  ,
   nullif(trim(substr(line, 113, (114-113)+1)), '') as CMSA  ,
   nullif(trim(substr(line, 115, (115-115)+1)), '') as MACCI ,
   nullif(trim(substr(line, 116, (119-116)+1)), '') as PMSA  ,
   nullif(trim(substr(line, 120, (123-120)+1)), '') as NECMA  ,
   nullif(trim(substr(line, 124, (124-124)+1)), '') as NECMACCI   ,
   nullif(trim(substr(line, 125, (126-125)+1)), '') as NECMASC   ,
   nullif(trim(substr(line, 127, (127-127)+1)), '') as EXI,
   nullif(trim(substr(line, 128, (132-128)+1)), '') as UA,
   nullif(trim(substr(line, 133, (134-133)+1)), '') as UASC ,
   nullif(trim(substr(line, 135, (135-135)+1)), '') as UATYPE  ,
   nullif(trim(substr(line, 136, (136-136)+1)), '') as UR,
   nullif(trim(substr(line, 137, (138-137)+1)), '') as CD106 ,
   nullif(trim(substr(line, 139, (140-139)+1)), '') as CD108 ,
   nullif(trim(substr(line, 141, (142-141)+1)), '') as CD109 ,
   nullif(trim(substr(line, 143, (144-143)+1)), '') as CD110 ,
   nullif(trim(substr(line, 145, (147-145)+1)), '') as SLDU ,
   nullif(trim(substr(line, 148, (150-148)+1)), '') as SLDL ,
   nullif(trim(substr(line, 151, (156-151)+1)), '') as VTD,
   nullif(trim(substr(line, 157, (157-157)+1)), '') as VTDI,
   nullif(trim(substr(line, 158, (160-158)+1)), '') as ZCTA3 ,
   nullif(trim(substr(line, 161, (165-161)+1)), '') as ZCTA5 ,
   nullif(trim(substr(line, 166, (170-166)+1)), '') as SUBMCD   ,
   nullif(trim(substr(line, 171, (172-171)+1)), '') as SUBMCDCC    ,
   nullif(trim(substr(line, 173, (186-173)+1)), '') as AREALAND   ,
   nullif(trim(substr(line, 187, (200-187)+1)), '') as AREAWATR    ,
   nullif(trim(substr(line, 201, (290-201)+1)), '') as NAME  ,
   nullif(trim(substr(line, 291, (291-291)+1)), '') as FUNCSTAT  ,
   nullif(trim(substr(line, 292, (292-292)+1)), '') as GCUNI ,
   nullif(trim(substr(line, 293, (301-293)+1)), '') as POP100 ,
   nullif(trim(substr(line, 302, (310-302)+1)), '') as HU100 ,
   nullif(trim(substr(line, 311, (319-311)+1)), '') as INTPTLAT ,
   nullif(trim(substr(line, 320, (329-320)+1)), '') as INTPTLON ,
   nullif(trim(substr(line, 330, (331-330)+1)), '') as LSADC ,
   nullif(trim(substr(line, 332, (332-332)+1)), '') as PARTFLAG   ,
   nullif(trim(substr(line, 333, (337-333)+1)), '') as SDELM  ,
   nullif(trim(substr(line, 338, (342-338)+1)), '') as SDSEC  ,
   nullif(trim(substr(line, 343, (347-343)+1)), '') as SDUNI ,
   nullif(trim(substr(line, 348, (353-348)+1)), '') as TAZ,
   nullif(trim(substr(line, 354, (358-354)+1)), '') as UGA ,
   nullif(trim(substr(line, 359, (363-359)+1)), '') as PUMA5  ,
   nullif(trim(substr(line, 364, (368-364)+1)), '') as PUMA1  ,
   nullif(trim(substr(line, 369, (383-369)+1)), '') as RESERVE2   ,
   nullif(trim(substr(line, 384, (388-384)+1)), '') as MACC  ,
   nullif(trim(substr(line, 389, (393-389)+1)), '') as UACP ,
   nullif(trim(substr(line, 394, (400-394)+1)), '') as RESERVED
from census2000.tmp_geoheader

-- This isn't the best way to do this
update census2000.sf1geo set geoid00 = concat(state, county, case when tract is null then cousub else concat(tract, block) end)
