COPY command for bulk data loading in PostgreSQL - Cheat Sheet & Quick Revision

Recall & Review

beginner

What is the purpose of the COPY command in PostgreSQL?

The COPY command is used to quickly load large amounts of data into a PostgreSQL table from a file or to export data from a table to a file.

beginner

How do you use the COPY command to load data from a CSV file into a table?

You use: <br> COPY table_name FROM '/path/to/file.csv' WITH (FORMAT csv, HEADER true); <br> This loads CSV data with a header row into the table.

beginner

What is the difference between COPY FROM and COPY TO?

COPY FROM loads data into a table from a file. <br> COPY TO exports data from a table to a file.

intermediate

Can the COPY command read from or write to a client program instead of a file?

Yes, using STDIN and STDOUT, COPY can read data from or write data to the client program, allowing interactive or programmatic data transfer.

intermediate

What permissions are required to use the COPY command with a file path?

The PostgreSQL server process must have read permission on the file for COPY FROM, and write permission for COPY TO. Also, the user must have appropriate table privileges.

Which command loads data from a CSV file into a PostgreSQL table?

A COPY table_name FROM '/path/file.csv' WITH (FORMAT csv, HEADER true);

B INSERT INTO table_name VALUES ('/path/file.csv');

C LOAD DATA INFILE '/path/file.csv' INTO table_name;

D SELECT * FROM '/path/file.csv';

COPY with FROM and FORMAT csv loads CSV data into the table.

What does the HEADER option do in the COPY command?

A Skips the first row of the file because it contains column names.

B Includes the first row as data.

C Specifies the file has no header.

D Encrypts the data during copy.

HEADER true tells COPY to skip the first row as it contains column headers.

Which of these is NOT a valid COPY command usage?

A COPY table_name TO '/path/file.csv' WITH (FORMAT csv);

B COPY table_name FROM STDIN;

C COPY table_name FROM '/path/file.csv' WITH (FORMAT json);

D COPY table_name FROM '/path/file.csv' WITH (FORMAT csv);

PostgreSQL COPY does not support JSON format directly.

What must the PostgreSQL server have to read a file with COPY FROM?

A Read permission on the file.

B Write permission on the file.

C Execute permission on the file.

D No permissions needed.

The server needs read permission to access the file for COPY FROM.

How can you export data from a table to a CSV file?

A COPY table_name TO '/path/file.csv' WITH (FORMAT csv, HEADER true);

B COPY table_name FROM '/path/file.csv';

C INSERT INTO table_name VALUES ('/path/file.csv');

D EXPORT table_name TO CSV;

COPY TO with FORMAT csv and HEADER true exports table data to a CSV file.

Explain how to use the COPY command to load data from a CSV file into a PostgreSQL table.

Think about the command structure and options for CSV files.

Describe the permissions and security considerations when using the COPY command with files.

Consider who needs access to the file and the database.