create table geoname (
         geonameid       int,
         name            varchar(200),
         asciiname        varchar(200),
         alternatenames  varchar(6000),
         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,
         isColloquial    boolean,
         isHistoric    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,isColloquial,isHistoric) 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')

 CREATE TABLE countryinfo
 (
   iso_alpha2 character(2) NOT NULL,
   iso_alpha3 character(3),
   iso_numeric integer,
   fips_code character varying(3),
   name character varying(200),
   capital character varying(200),
   areainsqkm double precision,
   population integer,
   continent character(2),
   languages character varying(200),
   currencycode character(3),
   geonameid integer,
   neighbours character varying,
   tld character varying,
   currencyname character varying,
   postalcodeformat character varying,
   postalcoderegex character varying,
   phone character varying,
   equivalentfipscode character varying,
   CONSTRAINT countryinfo_pkey PRIMARY KEY (iso_alpha2)
 )