COPY command for bulk data loading in PostgreSQL - Practice Problems & Coding Challenges

Challenge - 5 Problems

❓ query_result

intermediate

Output of COPY command with CSV file

Given a CSV file named data.csv with the following content:

id,name,age
1,Alice,30
2,Bob,25
3,Charlie,35

and a PostgreSQL table persons(id INT, name TEXT, age INT), what will be the result of running this command?

COPY persons FROM '/tmp/data.csv' CSV HEADER;

PostgreSQL

COPY persons FROM '/tmp/data.csv' CSV HEADER;

A 3 rows copied.

B Error: missing permissions to read file.

C 0 rows copied.

D Syntax error near 'CSV HEADER'.

💡 Hint

The CSV HEADER option tells PostgreSQL to skip the first line as column names.

The COPY command reads the CSV file and inserts 3 rows into the table. The HEADER option skips the first line which contains column names.

📝 Syntax

intermediate

Identify syntax error in COPY command

Which COPY command syntax is correct to load data from a CSV file into a table employees?

A COPY employees FROM '/path/to/file.csv' HEADER CSV DELIMITER ',';

B COPY employees FROM '/path/to/file.csv' CSV DELIMITER ',' HEADER;

C COPY employees FROM '/path/to/file.csv' DELIMITER ',' HEADER CSV;

D COPY employees FROM '/path/to/file.csv' DELIMITER ',' CSV HEADER;

💡 Hint

The order of options matters in COPY command syntax.

The correct syntax places CSV and HEADER options after DELIMITER, in the order: DELIMITER ',' CSV HEADER.

❓ optimization

advanced

Best practice for fast bulk loading with COPY

You want to load a large CSV file into a PostgreSQL table as fast as possible. Which practice will improve COPY command performance?

A Use COPY with FORMAT 'binary' instead of CSV.

B Run COPY inside a transaction with autocommit enabled.

C Disable indexes and constraints before COPY, then re-enable them after.

D Load data row by row using INSERT statements inside COPY.

💡 Hint

Indexes and constraints slow down inserts during bulk loading.

Disabling indexes and constraints before bulk loading reduces overhead, speeding up COPY. Rebuilding indexes after is more efficient.

🔧 Debug

advanced

Troubleshoot COPY command error

You run this command:

COPY sales FROM '/data/sales.csv' CSV;

and get the error:

ERROR: could not open file "/data/sales.csv": Permission denied

What is the most likely cause?

A The PostgreSQL server process does not have read permission on the file.

B The file path is incorrect or file does not exist.

C The CSV file is malformed and cannot be read.

D The COPY command syntax is invalid.

💡 Hint

Check file permissions and ownership for the PostgreSQL server user.

The error indicates a permission problem accessing the file, not a syntax or file content issue.

🧠 Conceptual

expert

Understanding COPY FROM STDIN usage

Which statement about the COPY ... FROM STDIN command in PostgreSQL is true?

A It automatically creates the target table if it does not exist.

B It allows streaming data from the client application into the database.

C It reads data only from a server-side file specified by a path.

D It can only be used with binary format, not CSV.

💡 Hint

STDIN means standard input from the client, not a file on the server.

COPY FROM STDIN streams data from the client to the server, useful for programmatic bulk loading.

Practice

1. What is the main purpose of the COPY command in PostgreSQL?

easy

A. To delete data from a table

B. To create a new table in the database

C. To quickly load or export large amounts of data between a file and a table

D. To update existing rows in a table

Solution

1. Step 1: Understand the function of COPY

   The COPY command is designed to transfer data efficiently between a file and a PostgreSQL table.

2. Step 2: Compare with other commands

   Unlike CREATE, UPDATE, or DELETE, COPY focuses on bulk data loading or exporting, not modifying table structure or individual rows.

3. Final Answer:

   To quickly load or export large amounts of data between a file and a table -> Option C

4. Quick Check:

   COPY = bulk data transfer [OK]

Hint: COPY is for fast bulk data load/export, not table changes [OK]

Common Mistakes:

• Confusing COPY with CREATE TABLE
• Thinking COPY updates rows
• Assuming COPY deletes data

2. Which of the following is the correct syntax to copy data from a CSV file into a PostgreSQL table named employees?

easy

A. COPY employees TO '/path/to/file.csv' WITH CSV HEADER;

B. COPY employees FROM '/path/to/file.csv' WITH CSV HEADER;

C. COPY employees FROM '/path/to/file.csv' DELIMITER ';';

D. COPY employees FROM '/path/to/file.csv' FORMAT JSON;

Solution

1. Step 1: Identify direction of COPY

   To load data into a table, use COPY ... FROM. COPY employees FROM '/path/to/file.csv' WITH CSV HEADER; and C use FROM, B uses TO which exports data.

2. Step 2: Check format and options

   COPY employees FROM '/path/to/file.csv' WITH CSV HEADER; uses WITH CSV HEADER which is correct for CSV files with headers. COPY employees FROM '/path/to/file.csv' DELIMITER ';'; uses DELIMITER ';' but no CSV keyword, which is less clear. COPY employees FROM '/path/to/file.csv' FORMAT JSON; uses FORMAT JSON which is invalid for COPY.

3. Final Answer:

   COPY employees FROM '/path/to/file.csv' WITH CSV HEADER; -> Option B

4. Quick Check:

   Load CSV with header = COPY ... FROM ... WITH CSV HEADER [OK]

Hint: Use COPY ... FROM for loading, WITH CSV HEADER for CSV files [OK]

Common Mistakes:

• Using COPY ... TO instead of FROM to load data
• Missing WITH CSV HEADER for CSV files with headers
• Using wrong format like JSON in COPY

3. Given the table products(id INT, name TEXT, price NUMERIC), what will be the result of this command?

COPY products FROM '/tmp/products.csv' WITH CSV HEADER;

Assuming /tmp/products.csv contains:

id,name,price
1,Pen,1.20
2,Notebook,2.50
3,Eraser,0.80

medium

A. No rows inserted because HEADER skips all data

B. Error because price is numeric but CSV has strings

C. Only 2 rows inserted because of header misread

D. 3 rows inserted into products table

Solution

1. Step 1: Understand COPY with CSV HEADER

   The HEADER option tells COPY to skip the first line (column names) and load the rest as data rows.

2. Step 2: Check data types and rows

   The CSV data matches the table columns and types correctly: id as int, name as text, price as numeric. All 3 data rows will be inserted.

3. Final Answer:

   3 rows inserted into products table -> Option D

4. Quick Check:

   CSV HEADER skips first line, 3 data lines inserted [OK]

Hint: CSV HEADER skips first line; all data rows load if types match [OK]

Common Mistakes:

• Thinking HEADER skips all rows
• Assuming numeric columns reject string input
• Confusing COPY direction or syntax

4. You run this command:

COPY customers FROM '/data/customers.csv' WITH CSV;

But get an error: ERROR: could not open file "/data/customers.csv": Permission denied
What is the most likely cause?

medium

A. The PostgreSQL server process lacks permission to read the file

B. The CSV file format is incorrect

C. The COPY command syntax is wrong

D. The customers table does not exist

Solution

1. Step 1: Analyze the error message

   The error says "Permission denied" when opening the file, indicating a file access issue.

2. Step 2: Understand file permissions in COPY

   PostgreSQL server must have OS-level read permission on the file. If it lacks permission, this error occurs.

3. Final Answer:

   The PostgreSQL server process lacks permission to read the file -> Option A

4. Quick Check:

   Permission denied = file access issue [OK]

Hint: Permission denied means PostgreSQL can't read the file [OK]

Common Mistakes:

• Assuming syntax error causes permission denied
• Thinking CSV format causes file access error
• Ignoring file permissions on server

5. You want to load data from a CSV file into a table but skip rows where the age column is empty or NULL. Which approach using COPY and PostgreSQL features is best?

hard

A. Use COPY to load all data into a staging table, then insert filtered rows into the target table

B. Use COPY with WHERE clause to skip rows with empty age

C. Use COPY with an option to ignore NULL values in age column

D. Modify the CSV file manually to remove rows with empty age before COPY

Solution

1. Step 1: Understand COPY limitations

   COPY does not support filtering rows or WHERE clauses during import.

2. Step 2: Use staging table for filtering

   Load all data into a temporary or staging table, then run an INSERT INTO ... SELECT ... WHERE age IS NOT NULL to filter rows.

3. Step 3: Consider manual CSV editing

   While manual editing works, it is inefficient and error-prone for large files.

4. Final Answer:

   Use COPY to load all data into a staging table, then insert filtered rows into the target table -> Option A

5. Quick Check:

   COPY can't filter; use staging table + filter [OK]

Hint: COPY can't filter; load then filter with SQL [OK]

Common Mistakes:

• Expecting COPY to filter rows directly
• Looking for non-existent COPY options to ignore NULLs
• Relying on manual CSV edits for large data