Import countries to PostgreSQL from geonames data

country.sql

DROP TABLE "country";

CREATE TABLE "country" (
	"iso" character(2) NOT NULL,
	"iso3" character(3) NOT NULL,
	"iso_numeric" character(3) NOT NULL,
	"fips" character(2),
	"name" character varying(128) NOT NULL,
	"capital" character varying(128),
	"area" float,
	"population" integer,
	"continent" character(2) NOT NULL,
	"tld" character(3),
	"currency_code" character(3),
	"currency_name" character varying(128),
	"phone" character varying(128),
	"postal_code_format" character varying(128),
	"postal_code_regex" character varying(255),
	"languages" character varying(16)[],
	"geonameid" integer,
	"neighbours" character(2)[],
	"equivalent_fips_code" character(2),
	CONSTRAINT country_pkey PRIMARY KEY ("iso"),
	CONSTRAINT country_iso3_key UNIQUE ("iso3"),
	CONSTRAINT country_iso_numeric_key UNIQUE ("iso_numeric")
);

/*

wget http://download.geonames.org/export/dump/countryInfo.txt

# remove comments
sed -i '/^\#/d' countryInfo.txt

# remove ending commas
sed -i 's/,\t/\t/g' countryInfo.txt

# format array values for postgres
awk -F"\t" 'BEGIN {OFS = FS} { gsub("(.*)","{"$16"}", $16); gsub("(.*)","{"$18"}", $18); print }' countryInfo.txt > countryInfo_clean.txt

*/

COPY "country" FROM '/home/ianare/countryInfo_clean.txt' DELIMITER '	' CSV;

For mysql

CREATE TABLE `country`.`country` (
	`iso` varchar(2) NOT NULL,
	`iso3` varchar(3) NOT NULL,
	`iso_numeric` varchar(3) NOT NULL,
	`fips` varchar(2),
	`name` varchar(128) NOT NULL,
	`capital` varchar(128),
	`area` float,
	`population` int(11),
	`continent` varchar(2) NOT NULL,
	`tld` varchar(3),
	`currency_code` varchar(3),
	`currency_name` varchar(128),
	`phone` varchar(128),
	`postal_code_format` varchar(128),
	`postal_code_regex` varchar(255),
	`languages` varchar(16),
	`geonameid` int(11),
	`neighbours` varchar(2),
	`equivalent_fips_code` varchar(2),
	CONSTRAINT country_pkey PRIMARY KEY (`iso`),
	CONSTRAINT country_iso3_key UNIQUE (`iso3`),
	CONSTRAINT country_iso_numeric_key UNIQUE (`iso_numeric`)
)

Аfter import through IntelliJ IDEA