Cheatsheet: From CSV to Postgres

Photo by panumas nikhomkhai: https://www.pexels.com/photo/close-up-photo-of-mining-rig-1148820/

I recently came across a case where my original data source is a CSV file and I’d like to import the data into a database so that I can do some basic querying and data visualization. This article is meant to be some kind of cheatsheet with a few commands handy to use.

After you have a docker container that runs Postgresql, there are 3 steps to import your CSV data into the database:

1. Copy local files (csv and ddl files) to a Docker container
2. Create a Postgres database and a table with schema using DDL
3. Import csv data into the table

3 steps to import csv data into a Docker container running Postgres

Step 1: Copy files locally to Docker container

1. First, you’d need the container ID of the container that you’re looking for. Run the command docker ps and you’ll see the container ID as the sample output below:

$ docker ps
CONTAINER ID   IMAGE      COMMAND                  CREATED       STATUS        PORTS                    NAMES
78355353bcfc   postgres   "docker-entrypoint.s…"   3 weeks ago   Up 28 hours   0.0.0.0:5432->5432/tcp   great_kirch

2. Use the docker cp command to copy local files to the Docker container

$ docker cp <filename> <containerID>:<target destination>

For example, if I want to copy create-table.ddl to /usr/share folder inside the Docker container, the command will look like:

$ docker cp create-table.ddl 78355353bcfc:/usr/share

Step 2: Create database and table with schema

1. Using the same container ID you got from the previous step, use the docker exec command to get a bash shell in the container

$ docker exec -it <container id> bash

2. psql is a terminal based frontend to Postgresql. We’re going to use this to perform our postgres related operations. To start:

$ su - postgres
$ psql

3. Create a database called coffeeshop

postgres=# CREATE DATABASE coffeeshop;

4. To create a table called sales, you can create it using the CREATE TABLE command directly on the terminal. The other way is to construct a DDL file and run the file using psql -f command. For the psql -f command, it looks something like below:

$ psql -d "dbname='coffeeshop' user='someuser' password='somepassword'" -f /usr/share/create-table.ddl

Step 3: Import CSV into the table

Now that you have the database table ready, we can use the COPY command within psql.

# COPY sales FROM '/usr/share/sales-toronto.csv' DELIMITER ',' CSV HEADER;
# COPY sales FROM '/usr/share/sales-newyork.csv' DELIMITER ',' CSV HEADER;

You’re all set. Run a SELECT statement on the table to make sure you have the data imported.

Quick psql Command Lookup

A few psql commands that you’d probably need:

• List all database: \l
• Connect to a specific database: \c somedb
• List tables in a database: \dt
• Get the table schema of a table: \d sometablename

References

• Docker command line reference, https://docs.docker.com/engine/reference/commandline/cli/
• Create table tutorial, https://www.tutorialspoint.com/sql_certificate/using_ddl_statements.htm#:~:text=The%20CREATE%20TABLE%20is%20a,table%20in%20its%20own%20schema.