Exporting and Importing Data: Mastering the COPY Command

Previously in this course, we covered using sequences for IDs to manage primary key generation. While managing individual records is perfect for daily operations, real-world engineering often requires moving large volumes of information between systems. In this lesson, we add the ability to perform bulk data migration using the COPY command.

Understanding the COPY Command

In PostgreSQL, the COPY command is the high-performance tool for moving data between tables and files. Unlike INSERT statements, which parse and validate every single row individually, COPY streams data directly into or out of the table. This makes it the standard choice for data migration tasks, such as loading daily sales reports or backing up data for analysis.

When working with external systems, the CSV (Comma-Separated Values) format is the lingua franca of data exchange. PostgreSQL’s COPY command is highly configurable, allowing you to specify delimiters, headers, and quote characters to match almost any input file.

Exporting Data to a File

To export data from your store's products table, you use the COPY ... TO syntax. This generates a file on the server's filesystem.

SQL
-- Export the products table to a CSV file
COPY products TO '/tmp/products_export.csv' WITH (FORMAT CSV, HEADER);

Note: The COPY command runs with the permissions of the PostgreSQL server user. If you are running PostgreSQL on your local machine, ensure the user account has write access to the destination directory (like /tmp/).

Importing CSV Data

Importing is where COPY truly shines. Let’s say you have a new batch of items to add to your inventory in a file named new_products.csv. To ensure integrity, we use the FROM variant.

SQL
-- Import data from a CSV file into the products table
COPY products(name, price, stock_quantity) 
FROM '/tmp/new_products.csv' 
WITH (FORMAT CSV, HEADER);

By explicitly naming the columns (name, price, stock_quantity), we prevent errors if the CSV structure doesn't perfectly match the table's column order. If your input file contains header rows, the HEADER option tells PostgreSQL to skip the first line during the import process.

Hands-on Exercise: Backing up your Store

For this exercise, we will perform a full backup of our customers table and then clear it to test a restoration.

1. Export: Run COPY customers TO '/tmp/customers_backup.csv' WITH (FORMAT CSV, HEADER); in your SQL tool.
2. Verify: Check your file system to ensure the file exists. You can use your terminal knowledge from piping commands together to cat the file and inspect its contents.
3. Clear: Run TRUNCATE TABLE customers; (this empties the table).
4. Import: Restore the data: COPY customers FROM '/tmp/customers_backup.csv' WITH (FORMAT CSV, HEADER);
5. Confirm: Run SELECT * FROM customers; to verify all your data returned safely.

Common Pitfalls

• Server-Side vs. Client-Side: The standard COPY command (as shown above) runs on the server. If you are using a cloud-hosted database, you cannot write to the server's disk. In those cases, use the \copy meta-command in psql, which acts as a client-side wrapper and streams data through your local machine instead.
• Permissions: A frequent error is "permission denied." Remember that the PostgreSQL service user needs to be able to read/write the file path you provide.
• Data Types: Ensure the CSV data strictly matches the table's constraints. If you try to import a string into an integer column or violate a NOT NULL constraint, the entire transaction will fail and roll back, protecting your database from partial, corrupt imports.

FAQ

Can I export only a subset of data? Yes. You can use COPY (SELECT * FROM products WHERE price > 50) TO '/tmp/expensive_items.csv' WITH (FORMAT CSV);. You can execute any valid SELECT query inside the parentheses.

Does COPY work with other formats? Yes, it supports TEXT and BINARY formats in addition to CSV.

What if my CSV file uses semicolons instead of commas? Add the DELIMITER option: WITH (FORMAT CSV, DELIMITER ';').

Recap

We have moved beyond individual record management to bulk data handling. You now know how to leverage the COPY command to export tables and import external CSV files, which is critical for system integration and data migrations. You have also seen how COPY integrates with your existing schema to maintain data integrity.

Up next: We will secure our database environment by learning about database security basics.