create table geoname (
        geonameid       int,
        name            varchar(200),
        asciiname        varchar(200),
        alternatenames  varchar(4000),
        latitude        float,
        longitude       float,
        fclass  char(1),
        fcode   varchar(10),
        country varchar(2),
        cc2 varchar(60),
        admin1  varchar(20),
        admin2  varchar(80),
        admin3  varchar(20),
        admin4  varchar(20),
        population      bigint,
        elevation       int,
        gtopo30         int,
        timezone varchar(40),
        moddate         date
);

create table alternatename (
        alternatenameId  int,
        geonameid          int,
        isoLanguage        varchar(7),
        alternateName     varchar(200),
        isPreferredName      boolean,
        isShortName    boolean
);

Note: the countryInfo file has changed since this posting was written.


CREATE TABLE "countryinfo" (
     iso_alpha2 char(2),
     iso_alpha3 char(3),
     iso_numeric integer,
     fips_code character varying(3),
     name character varying(200),
     capital character varying(200),
     areainsqkm double precision,
     population integer,
     continent char(2),
     languages character varying(200),
     currency char(3),
     geonameId int
);

copy geoname (geonameid,name,asciiname,alternatenames,latitude,longitude,fclass,fcode,country,cc2, admin1,admin2,admin3,admin4,population,elevation,gtopo30,timezone,moddate) from 'allCountries.txt' null as '';
copy alternatename  (alternatenameid,geonameid,isoLanguage,alternateName,isPreferredName,isShortName) from 'alternateNames.txt' null as '';
copy countryInfo  (iso_alpha2,iso_alpha3,iso_numeric,fips_code,name,capital,areaInSqKm,population,continent,languages,currency,geonameId) from 'countryInfo.txt' null as '';

ALTER TABLE ONLY alternatename
    ADD CONSTRAINT pk_alternatenameid PRIMARY KEY (alternatenameid);

ALTER TABLE ONLY geoname
    ADD CONSTRAINT pk_geonameid PRIMARY KEY (geonameid);

ALTER TABLE ONLY countryinfo
    ADD CONSTRAINT pk_iso_alpha2 PRIMARY KEY (iso_alpha2);

CREATE INDEX index_countryinfo_geonameid ON countryinfo USING hash (geonameid);

CREATE INDEX index_alternatename_geonameid ON alternatename USING hash (geonameid);

SELECT * FROM countryinfo WHERE geonameid=0;

AQ
AS
AX
BV
FO
GF
GU
HM
MP
MQ
NC
PM
RE
SJ
UM
VI
WF

INSERT INTO geoname (geonameid) VALUES (0);

ALTER TABLE ONLY countryinfo
    ADD CONSTRAINT fk_geonameid FOREIGN KEY (geonameid) REFERENCES geoname(geonameid);

AS => geonameid=5880801; fclass=A; fcode=ADMD; name=American Samoa, Territory of
BV => geonameid=3371123; fclass=A; fcode=PCLD; name=Bouvet Island
FO => geonameid=2622320; fclass=A; fcode=ADM1; name=Faroe Islands
GF => geonameid=3381670; fclass=A; fcode=ADM1; name=Department of Guiana
GU => geonameid=4043988; fclass=A; fcode=ADMD; name=Territory of Guam
MP => geonameid=4041467; fclass=A; fcode=ADMD; name=Northern Islands
MQ => geonameid=3570311; fclass=A; fcode=ADM1; name=Department of Martinique
NC => geonameid=2139685; fclass=A; fcode=ADM1; name=Territory of New Caledonia and Dependencies
PM => geonameid=3424932; fclass=A; fcode=ADM1; name=Territorial Collectivity of Saint Pierre and Miquelon
RE => geonameid=935317;  fclass=A; fcode=ADM1; name=Department of Reunion
SJ => geonameid=607072;  fclass=A; fcode=ADM1; name=Svalbard
UM => geonameid=5854968; fclass=A; fcode=ADMD; name=United States Minor Outlying Islands
VI => geonameid=4796775; fclass=A; fcode=ADMD; name=Virgin Islands of the United States
WF => geonameid=4034749; fclass=A; fcode=ADM1; name=Territory of the Wallis and Futuna Islands

AQ => no entry in geoname-table (WHERE country='AQ' AND fclass='A')
AX => no entry in geoname-table (WHERE country='AX')
HM => no entry in geoname-table (WHERE country='HM')