The COPY Command
At this point, you are probably pretty familiar with the SELECT statement (covered in Chapter 2, The Basics of 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.
Getting Started with COPY
The COPY statement retrieves data from your database and dumps it in the file format of your choosing. For example, take the following statement:
COPY (SELECT * FROM customers LIMIT 5) TO STDOUT WITH CSV HEADER;
Figure 6.1: Using COPY to print results to STDOUT in a CSV file format
This statement returns five rows from...