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.