Importing Data from CSV Files Using the `COPY` Command

Hey folks! Imagine you’ve got an Excel table with 10,000 rows, and your boss says: "Upload this to the database!" Hmm... if you try to insert all that by hand, well... you’ll probably need more than just SQL skills—you’ll need a vacation. 😅

Luckily, PostgreSQL has a command that works like a teleporter from CSV to your database—yep, it’s COPY. It loads data instantly, in bulk, and without any fuss. Today we’ll figure out how COPY works, how to prep your data, and what to do if your file suddenly has a “floating comma” or some weird character. Let’s go!

Basics of the COPY Command

COPY is a PostgreSQL command that lets you move data between a table and the file system. It’s super useful for bulk loading data into your database or exporting it out.

Example COPY syntax for loading data into a table:

COPY table_name FROM 'path/to/your/file.csv' DELIMITER ',' CSV HEADER;

Main parameters:

• FROM 'path/to/your/file.csv': sets the path to your CSV file.
• DELIMITER ',': tells which character separates your columns (here, it’s a comma).
• CSV HEADER: tells PostgreSQL that the first row in your CSV is a header (column names).

The COPY command runs directly on the server, which makes it blazing fast for big data loads.

Difference Between COPY and \COPY

Sometimes you might get a bit confused between COPY and \COPY.

• COPY: runs on the server side. Used for loading files that are on the server.
• \COPY: a client-side command available in psql. Loads data from your local computer.

If you’re working on your own machine, you’ll probably use \COPY. We’ll talk about it a bit later.

Example of Using COPY

Let’s jump right into an example to see how it works in practice.

Step 1: Prepping the Table

Imagine we’re creating a table to store info about students:

CREATE TABLE students (
    student_id SERIAL PRIMARY KEY,
    first_name VARCHAR(50),
    last_name VARCHAR(50),
    date_of_birth DATE,
    grade FLOAT
);

Step 2: Prepping the CSV File

Your CSV file format should match the table. Here’s an example students.csv file:

first_name,last_name,date_of_birth,grade
John,Doe,2001-05-15,85.5
Jane,Smith,2000-12-22,90.0
Alice,Johnson,2002-03-10,78.0
Bob,Brown,2001-08-30,NULL

Heads up: the column order in your CSV should match the table, and data types should line up (like, dates should be in YYYY-MM-DD format).

Step 3: Loading the Data

To load the students.csv file into the students table, use this command:

COPY students (first_name, last_name, date_of_birth, grade)
FROM '/path/to/your/students.csv'
DELIMITER ',' 
CSV HEADER;

What’s going on here?

• We specify the table name students and the columns we’re loading data into.
• The file path points to where your CSV lives.
• The , delimiter says the data is comma-separated.
• The CSV HEADER parameter tells PostgreSQL the first row has column names.

Result:

After running the command, the data from your file gets loaded into the table, and you can check it right away:

SELECT * FROM students;

Limitations and Gotchas

To avoid errors, make sure your CSV file meets these requirements:

1. No hidden characters or spaces in the header row.
2. Use the right encoding: PostgreSQL usually wants UTF-8.
3. The delimiter matches the DELIMITER parameter you set.

Common COPY Load Errors:

Data structure mismatch. For example, if you try to load a text value into a numeric field:

ERROR: invalid input syntax for type numeric: "abc"

To dodge these issues, make sure your data matches the expected type.

File path error.

If your file is outside the PostgreSQL server’s accessible directory, you’ll get an error like:

ERROR: could not open file "/path/to/your/file.csv" for reading: Permission denied

The fix is to set the right file permissions.

How Do You Check That Data Loaded Correctly?

After loading data, it’s a good idea to make sure everything went fine. For example:

Check the total row count:

SELECT COUNT(*) FROM students;

Find empty values (like if the grade field has NULL):

SELECT * FROM students WHERE grade IS NULL;

Handy Tips for Working with COPY

Log your errors. If you want to write errors to a separate file, use the LOG ERRORS parameter. (PostgreSQL version 12+).

Disable indexes and triggers.

When loading big data sets, you can temporarily turn off indexes:

ALTER TABLE students DISABLE TRIGGER ALL;

Use transactions.

This lets you load data “atomically”—either it all loads, or it rolls back if there’s an error:

BEGIN;
COPY students FROM '/path/to/your/file.csv' CSV HEADER;
COMMIT;

Real-World Use

Knowing how to use the COPY command isn’t just for DB admins—devs can save a ton of time with it too. Imagine you get a huge data dump from some third-party API: first, you save it as a CSV file, then you quickly load it into PostgreSQL—and you’re ready to analyze. Or maybe your coworkers ask you to move their old database into a new PostgreSQL system. In these cases, COPY is a real lifesaver.

CSV files pop up in real projects all the time. For example:

• You’re loading sales stats into an analytics system.
• You’re importing a user list from an external CRM.
• You’re moving data from Excel to PostgreSQL without any crazy workarounds.

That’s it for our intro to the COPY command. In the next lecture, we’ll talk about how to prep your tables for data loading and design their structure so everything runs fast and reliably. For now, don’t be afraid to experiment—COPY really opens up some awesome ways to work with big data sets.