Importing a CSV file into a dockerized Postgres.

Scenario:

If you find yourself in need of copying data from one database to another or importing data from a .csv file to your dockerized Postgres, then this might help you!

1. Download or prepare your data into a CSV format.

A CSV is a comma-separated values file, which allows data to be saved in a tabular format. CSVs look like a garden-variety spreadsheet but with a .csv extension. CSV files can be used with almost any spreadsheet program, such as Microsoft Excel or Google Spreadsheets.

From another PSQL table

a. Using psql: \copy table_name to 'filename.csv' csv header

b. Using SQL queries: COPY table_name TO 'file_name.csv' DELIMITER ',' CSV HEADER;

c. Creating a table dump from an existing dockerized Postgres:

CONTAINER = "name"
DB = "dbName"
TABLE = "tableName"
FILE ="file.csv"

Then run sudo docker exec -u postgres${CONTAINER} psql -d{DB} -c "COPY ${TABLE} TO STDOUT WITH CSV HEADER" > ${FILE}

d. You can also use some GUI like TablePlus and more.

2. Copy your .csv file to your container.

We choose to copy the file from localhost to the container because docker is like a VM running on top of your computer.

Understanding Docker.

i. Set your path to where your .csv file is hosted locally.

ii. Set your path to where you want your file to be copied in the container.

iii. log in to your docker container.

iv. Use this command to copy your file.

sudo docker cp /home/(name)/(folder_name)/(file_name)(container_id):/(to_the_place_you_want_the_file_to_be)

Here, I prefer copying the file to the Postgres data folder since it’s a database related file. You can delete this file later from that folder since you won’t be using it again.

3. IMPORT TO A POSTGRES TABLE

If you already have a database with a table that has the same fields as your .csv file then:

a. Import your .csv file to your existing empty table in Postgres.

i. Run docker -exec -it ${container_id} sh connect to the container that hosts your Postgres.

ii. Run psql -U postgres enter the Postgres shell.

iii. \c ${database_name} connect to your database.

iv. COPY ${table_name} FROM '${path to your .csv file on the container}' DELIMITER ',' CSV HEADER;

Sometimes, if the table you are importing to doesn’t have the same columns like the one you are importing, you might have to specify the columns you want to import from your .csv file.

b. First create a table with similar fields as your .csv file.

Go back to the step where you copied the local .csv file to var/lib/postgresql/data/

i. Now we are going to write a query to create our table in a separate file eg: table.sql

CREATE TABLE (tableName) IF NOT EXISTS(
      ID INT PRIMARY KEY     NOT NULL,
      FIELD      TEXT,
      FIELD      TEXT,
      FIELD        TEXT,
    );
remember to add relationship and protect your data if you have to

Ref: https://www.postgresqltutorial.com/postgresql-create-table/

ii. copy our .sql file to your var/lib/postgresql/data/

using this cmd

sudo docker cp /home/(name)/(folder_name)/(file_name)(container_id):/(to_the_place_you_want_the_file_to_be)

After this, you will have to run a cmd to create your table first then import your .csv file with the same field as the table et voila.