The COPY Command

At this point, you are probably familiar with the SELECT statement (covered in Chapter 1, Introduction to SQL for Analytics), which allows us to retrieve data from our database. While this command is useful for small datasets that can be scanned quickly, we will often want to save a large dataset to a file. By saving these datasets to files, we can further process or analyze the data locally using Excel, Python, or R. In order to retrieve these large datasets, we can use the Postgres COPY command, which efficiently transfers data from a database to a file, or from a file to a database.

The COPY statement retrieves data from your database and dumps it into the file format that you choose. For example, consider the following statement:

COPY (SELECT * FROM customers LIMIT 5) TO STDOUT WITH CSV HEADER;

The following is the output of the code:

Figure 4.1: Using COPY to print the results to STDOUT in a CSV file format

This statement returns...