Importing data non-interactively

For the non-interactive psql data import example we'll do a bit more than in the interactive mode. We'll:

• Import the full earthquakes dataset
• Select a subset of earthquakes data mentioned in the previous example and insert it into its own table
• Import another dataset - in this case the Ordnance Survey's POIs

Basically the non-interactive usage of psql means we simply provide it with an SQL to execute. This way we can put together many statements without having to execute them one by one.

Once again we will need the data model prior to loading the data, and then a \COPY command will be used.

If you're still in psql, you can execute a script by simply typing:

\i path\to\the\script.sql

For example:

\i F:/mastering_postgis/chapter02/code/data_import_earthquakes.sql

You should see a similar output:

mastering_postgis-# \i F:/mastering_postgis/chapter02/code/data_import_earthquakes.sql
    CREATE SCHEMA
    psql:F:/mastering_postgis/chapter02/code/data_import_earthquakes.sql:5: NOTICE:  table "earthquakes_csv" does not exist, skipping
    DROP TABLE
    CREATE TABLE
    COPY 25
    psql:F:/mastering_postgis/chapter02/code/data_import_earthquakes.sql:58: NOTICE:  table "earthquakes_csv_subset" does not exist, skipping
    DROP TABLE
    SELECT 25
    mastering_postgis-#

If you quit psql already, type the following command into cmd:

psql -h host -p port -U user -d database -f path\to\the\script.sql

For example:

psql -h localhost -p 5434 -U postgres -d mastering_postgis -f F:\mastering_postgis\chapter02\code\data_import_earthquakes.sql

You should see a similar output:

F:\mastering_postgis\chapter02>psql -h localhost -p 5434 -U postgres -d mastering_postgis -f F:\mastering_postgis\chapter02\code\data_import_earthquakes.sql
    psql:F:/mastering_postgis/chapter02/code/data_import_earthquakes.sql:2: NOTICE:  schema "data_import" already exists, skipping
    CREATE SCHEMA
    DROP TABLE
    CREATE TABLE
    COPY 25
    DROP TABLE
  SELECT 25

The script executed earlier is in the book's code repository under Chapter02/code/ data_import_earthquakes.sql.

Loading OS POI data is now a piece of cake. This dataset is in a bit of a different format though, so it requires slight adjustments. You can review the code in Chapter02/code/ data_import_gb_poi.sql.