Querying CSV Files Using Postgres
Tags: postgres
Published: 2023-09-04 04:54:33

---
title: Querying CSV Files Using Postgres
published: true
description: 
tags: postgres
# cover_image: https://direct_url_to_image.jpg
# Use a ratio of 100:42 for best results.
# published_at: 2023-09-04 03:07 +0000
---

While reading [PostgreSQL: Up and Running](https://www.amazon.com/PostgreSQL-Running-Practical-Advanced-Database/dp/1491963417), I noticed a few features where it is easy to import data from a CSV file into postgres. However, the example in the book required a complex `CREATE TABLE` query that was specific to the CSV file that they were loading.

I figured it would be cool if we could load an arbitrary CSV files, and automatically create a table based on the first line of the CSV file which often contains the column names for the data included in the file.

# Sample Dataset

To test this out, I used a free data set ([Electric Vehicle Population Data](https://catalog.data.gov/dataset/electric-vehicle-population-data)) from the State of Washington which contains data about registered electric vehicles in the state.

You can easily grab this data set with the following shell command:
```bash
wget \
  https://data.wa.gov/api/views/f6w7-q2d2/rows.csv?accessType=DOWNLOAD \
  -O Electric_Vehicle_Population_Data.csv
```

# Load Column Data

This CSV file contains the expected header row
```plaintext
VIN (1-10),County,City,State,Postal Code,Model Year,Make,Model,Electric Vehicle Type,Clean Alternative Fuel Vehicle (CAFV) Eligibility,Electric Range,Base MSRP,Legislative District,DOL Vehicle ID,Vehicle Location,Electric Utility,2020 Census Tract
```

In order to generate a `CREATE TABLE` command to create a table based on these columns, we first need to load the column names into PostgreSQL.

However, since we don't care about persisting this information past the task of creating the table of electric vehicle data, we can load it into a temporary table.

From a `psql` prompt, I created this temporary table and loaded it with data with the following commands
```sql
CREATE TEMP TABLE "csv_columns" ( "name" TEXT );
\copy "csv_columns" FROM PROGRAM 'head -n 1 Electric_Vehicle_Population_Data.csv | sed ''s/,/\n/g'''
```

This command reads the first line of our csv file, and replaces the commands with newlines. The copy command takes each line of our output, and puts it into our `csv_columns` table.

# Loading CSV Data

With psql, we can construct a query with a `SELECT` statement, based on our columns table, and execute that query with the `\gexec` command.

We employ the built in functions `array_to_string` which is like `strings.Join` in main languages, and `array_agg` which aggregates values into a single value.

We can create the table based on the loaded column information using the following command
```sql
SELECT
    'CREATE TABLE "public"."electric_vehicles" (' ||
        array_to_string(array_agg('"' || "name" || '" TEXT'), ',') ||
    ');'
FROM "csv_columns" \gexec
```

This effectively executes the SQL query
```sql
CREATE TABLE "public"."electric_vehicles" (
    "VIN (1-10)" TEXT,
    "County" TEXT,
    "City" TEXT,
    "State" TEXT,
    "Postal Code" TEXT),
    "Model Year" TEXT,
    "Make" TEXT,
    "Model" TEXT,
    "Electric Vehicle Type" TEXT,
    "Clean Alternative Fuel Vehicle (CAFV) Eligibility" TEXT,
    "Electric Range" TEXT,
    "Base MSRP" TEXT,
    "Legislative District" TEXT,
    "DOL Vehicle ID" TEXT,
    "Vehicle Location" TEXT,
    "Electric Utility" TEXT,
    "2020 Census Tract" TEXT
);
```

Once we have our table, we can load the CSV data with the copy command.

```sql
\copy "public"."electric_vehicles" FROM 'Electric_Vehicle_Population_Data.csv' CSV
```

# Querying CSV Data in PostgreSQL

We can now query our CSV data as a SQL table, for example, this gets the top 5 makes of electric vehicles registered in Washington State.

```sql
SELECT
    "Make",
    count(*) AS "Count"
FROM "public"."electric_vehicles"
GROUP BY "Make" 
ORDER BY "Count" DESC
LIMIT 5;
```

Which outputs

```plaintext
   Make    | Count
-----------+-------
 TESLA     | 65552
 NISSAN    | 13317
 CHEVROLET | 11816
 FORD      |  7307
 BMW       |  6209
(5 rows)
```

# Conclusions

I found this a really interesting way to learn PostgreSQL better, and perhaps a way I can grok CSV data in the future.

# Code

Here is a full `psql` script to load and query the Electric Vehicle Population Data CSV file. I named it `create_ev_dataset.sql`, and execute it with `psql -f create_ev_dataset.sql`. It assumes the CSV is in the directory that you ran `psql` from.

```sql
-- create a temporary table to load the column names into
-- this table only exists for the duration of the connection
CREATE TEMP TABLE "csv_columns" ( "name" TEXT );

-- split the first line of the csv file to get the column names on individual lines, and copy them into our temporary table
\copy "csv_columns" FROM PROGRAM 'head -n 1 Electric_Vehicle_Population_Data.csv | sed ''s/,/\n/g'''

-- use our temporary table of column names to construct a table to store csv data in
SELECT
    'CREATE TABLE "public"."electric_vehicles" (' ||
        array_to_string(array_agg('"' || "name" || '" TEXT'), ',') ||
    ');'
FROM "csv_columns" \gexec

-- copy csv data into the table
\copy "public"."electric_vehicles" FROM 'Electric_Vehicle_Population_Data.csv' CSV

-- query csv data using postgres
SELECT
    "Make",
    count(*) AS "Count"
FROM "public"."electric_vehicles"
GROUP BY "Make" 
ORDER BY "Count" DESC
LIMIT 5;

```