COPY command for bulk data loading in PostgreSQL - Mini Project: Build & Apply

Bulk Data Loading with COPY Command in PostgreSQL

📖 Scenario: You work in a small company that collects customer information in CSV files. You want to quickly load this data into a PostgreSQL database table to analyze it.

🎯 Goal: Learn how to use the COPY command in PostgreSQL to load data from a CSV file into a database table efficiently.

📋 What You'll Learn

Create a table named customers with columns id, name, and email

Prepare a CSV file named customers.csv with sample customer data

Write a COPY command to load data from customers.csv into the customers table

Use the correct options in the COPY command to handle CSV format and headers

💡 Why This Matters

🌍 Real World

Loading large amounts of data quickly into a database is common in business for reporting and analysis.

💼 Career

Database administrators and data analysts often use the COPY command to import data efficiently.

1

Create the customers table

Write a SQL statement to create a table called customers with three columns: id as an integer primary key, name as text, and email as text.

PostgreSQL

-- Your code here to create the customers table

Need a hint?

Use CREATE TABLE with the column names and types exactly as specified.

2

Prepare the CSV file path

Create a variable called csv_file_path and set it to the string '/tmp/customers.csv' representing the path to the CSV file.

PostgreSQL

CREATE TABLE customers (
    id INTEGER PRIMARY KEY,
    name TEXT,
    email TEXT
);

-- Your code here to set csv_file_path

Need a hint?

Use \set to define a psql variable for the CSV file path.

3

Write the COPY command to load data

Write a COPY command to load data from the CSV file at :csv_file_path into the customers table. Use the options FORMAT csv and HEADER true to handle the CSV format and header row.

PostgreSQL

CREATE TABLE customers (
    id INTEGER PRIMARY KEY,
    name TEXT,
    email TEXT
);

\set csv_file_path '/tmp/customers.csv'

-- Your code here to write the COPY command

Need a hint?

Use COPY customers FROM :'csv_file_path' WITH (FORMAT csv, HEADER true); to load the CSV data.

4

Verify the data load completion

Add a SQL query to select all rows from the customers table to verify the data was loaded correctly.

PostgreSQL

CREATE TABLE customers (
    id INTEGER PRIMARY KEY,
    name TEXT,
    email TEXT
);

\set csv_file_path '/tmp/customers.csv'

COPY customers FROM :'csv_file_path' WITH (FORMAT csv, HEADER true);

-- Your code here to select all rows from customers

Need a hint?

Use SELECT * FROM customers; to see all the loaded data.