Search⌘ K
Home
Courses
Transferring Data with ETL

Exercise: The COPY Command

Learn how to load transformed data into a PostgreSQL database using the COPY command. This lesson guides you through creating a table and function to automate loading CSV data in an ETL pipeline, including truncating the table before each load and verifying the insertion.

We'll cover the following...

In this exercise, we’ll practice using the COPY command to load data into a PostgreSQL database. Our task is to create a table named cars and a function that loads a CSV file into it.

This function will be a part of the load step in an ETL pipeline that uses full loads. Therefore, we need to truncate the table each time we load new data.

Create a table

The CSV file called cars.csv contains a million records, and has the following format:

C++
ProductID,Company,CarName,Price,Quantity	
1,Toyota,Camry,89915.37,7
2,Tesla,Model S,34397.88,6
3,Volvo,XC60,88356.36,9
4,Ferrari,SF90488,19463.69,7
5,Mazda,CX-5,69653.82,4
6,Toyota,Camry,41087.01,1
7,Tesla,Model S,71948.6,10
8,Jeep,Wrangler,73004.64,2
9,Toyota,Camry,52439.4,7

Our first step is to create a table to store this data. Create a table with matching ...