How to import the World Cities database into PostgreSQL?

It is easy to import our World Cities Database into PostgreSQL.  Just follow these steps:

1.  Reformat CSV so that Null values are better understood by PostgreSQL.  In Linux, run this command:

sed -i -e 's/"[\*]*"//g' worldcities.csv

where worldcities.csv is the location of the CSV file.  The file will change in place.  This may take a few minutes as the file is large.

2.  Open the PostgreSQL editor:

psql

3.  Create a table for the data within your database:

CREATE TABLE worldcities (
  city VARCHAR(120),
  city_ascii VARCHAR(120),
  city_alt VARCHAR(1000),
  lat FLOAT,
  lng FLOAT,
  country VARCHAR(120),
  iso2 VARCHAR(2),
  iso3 VARCHAR(3),
  admin_name VARCHAR(120),
  admin_name_ascii VARCHAR(120),
  admin_code VARCHAR(6),
  admin_type VARCHAR(27),
  capital VARCHAR(7),
  density FLOAT,
  population FLOAT,
  population_proper FLOAT,
  ranking INT,
  timezone VARCHAR(120),
  same_name VARCHAR(5),
  id VARCHAR(10)
);

4.  Copy the data from your CSV file into your table:

COPY worldcities(city,city_ascii,city_alt,lat,lng,country,iso2,iso3,admin_name,admin_name_ascii,admin_code,admin_type,capital,density,population,population_proper,ranking,timezone,same_name,id)
FROM 'worldcities.csv'
DELIMITER ','
QUOTE '"'
CSV HEADER;

5.  That's it!  Your data should have been successfully imported and ready to be queried.