Loading Data - Docker¶

Let us understand how you can take care of loading data into Postgres Database running using Docker Container.

Make sure database is created along with the user with right permissions. Also the user who want to use COPY command need to have pg_read_server_files role assigned.
- Create file with sample data
- Copy file into Docker container
- Connect to Database
- Create the table
- Run COPY Command to copy the data.

Prepare Data

We need to create file with sample data and copy the files into the container.

Sample File In this case data is added to users.csv under ~/sms_db.

user_first_name,user_last_name,user_email_id,user_role,created_dt
Gordan,Bradock,gbradock0@barnesandnoble.com,A,2020-01-10
Tobe,Lyness,tlyness1@paginegialle.it,U,2020-02-10
Addie,Mesias,amesias2@twitpic.com,U,2020-03-05
Corene,Kohrsen,ckohrsen3@buzzfeed.com,U,2020-04-15
Darill,Halsall,dhalsall4@intel.com,U,2020-10-10

Copy data

docker cp ~/sms_db/users.csv itv_pg:/tmp

Create Database

Here are the steps to create database.

Connect to database as super user postgres

docker exec -it itv_pg psql -U postgres

Create the database with right permissions.

CREATE DATABASE itversity_sms_db;
CREATE USER itversity_sms_user WITH PASSWORD 'sms_password';
GRANT ALL ON DATABASE itversity_sms_db TO itversity_sms_user;
GRANT pg_read_server_files TO itversity_sms_user;

Exit using \q

Connect to Database

Use this command to connect to the newly created database.

psql -U itversity_sms_user \
  -h localhost \
  -p 5433 \
  -d itversity_sms_db \
  -W

Create Table

Here is the script to create the table.

CREATE TABLE users (
    user_id SERIAL PRIMARY KEY,
    user_first_name VARCHAR(30) NOT NULL,
    user_last_name VARCHAR(30) NOT NULL,
    user_email_id VARCHAR(50) NOT NULL,
    user_email_validated BOOLEAN DEFAULT FALSE,
    user_password VARCHAR(200),
    user_role VARCHAR(1) NOT NULL DEFAULT 'U', --U and A
    is_active BOOLEAN DEFAULT FALSE,
    created_dt DATE DEFAULT CURRENT_DATE
);

Load Data

Here are the steps to load and validate the data using psql.

Load data using COPY Command

COPY users(user_first_name, user_last_name, 
    user_email_id, user_role, created_dt
) FROM '/tmp/users.csv'
DELIMITER ','
CSV HEADER;

Validate by running queries

SELECT * FROM users;

Mastering SQL using Postgresql

Loading Data - Docker¶