To make this chapter a pleasant experience for you, we will compile some sample data that has been taken from the BP energy report, which can be found at http://www.bp.com/en/global/corporate/energy-economics/statistical-review-of-world-energy/downloads.html.

Here is the data structure that will be used:

test=# CREATE TABLE t_oil ( 
  region      text, 
  country     text, 
  year        int, 
  production  int, 
  consumption int 
); 
CREATE TABLE 

The test data can be downloaded from our website using curl directly:

test=# COPY t_oil FROM PROGRAM ' 
  curl https://www.cybertec-postgresql.com/secret/oil_ext.txt ';  
COPY 644 

Like we did in the previous chapter, we can download the file before importing it. On some operating systems, curl is not present by default or has not been installed, so downloading the file before might be an easier option for many people.

There is data for between 1965 and 2010 for 14 nations in 2 regions of the world:

test=# SELECT region, avg(production) FROM  t_oil GROUP BY region; 
    region     |          avg           
---------------+----------------------- 
 Middle East   | 1992.6036866359447005 
 North America | 4541.3623188405797101 
(2 rows)