PostgreSQL Copy

Introduction

If you have a file containing data you’d like to import into PostgreSQL, you may be wondering what the best method is for bringing that data into your database. PostgreSQL makes this process simple with the help of the COPY command, which can also be used to reverse the process and to copy data from a table to file. In this article, we’ll show you how to use the PostgreSQL COPY command, and we’ll see how it can be used to copy data both to and from a PostgreSQL table.

Prerequisite

Before you begin, make sure that the following software is installed and set up on your machine:

• PostgreSQL 11

PostgreSQL COPY

The PostgreSQL COPY command enables us to transfer files to virtually anywhere. With this PostgreSQL utility, we can copy files both into and out of the database. The command supports different file formats such as comma-delimited, binary and tab-delimited.

Sample Data Set

We’ll be using the following sample dataset in our examples:

Shown below is the structure of our sample person table:

PostgreSQL COPY Example

In this section, we’ll show you several examples of how to use the PostgreSQL COPY utility.

PostgreSQL COPY FROM

Our first example will use the COPY utility to copy the contents of a compatible format file to a database table.

We can see the basic syntax of the COPY FROM command below:

Let’s assume we have a CSV file that we’d like to copy into our person table, and this file has the following location: C:\temp\ObjectRocket\PostgreSQL\person_list.csv.

We can use the following command:

Right after executing this command, PostgreSQL will respond with the following message:

We can verify that we were able to copy all the records from the CSV file into our person table with a simple SELECT statement:

PostgreSQL COPY TO

In this section, we’ll do the opposite of the operation we performed in our previous examples. This time, we’re going to copy, or export, table records based on the results of a SELECT statement and create a CSV file containing this data.

Shown below is the basic form of the COPY TO command:

Let’s use this command to copy the contents of our person table into a file:

The command shown above selects all the rows in the person table and copies the results to a file with the specified path and the filename of person_list_copy_to.csv.

The output will look like this:

We can verify that we were able to create the file by navigating to the specified path. We should see something like this:

The contents of the file will look like the following:

Conclusion

Copying data between database tables and files is a common task when you’re working with a PostgreSQL database. Fortunately, it’s easy to get the job done with the help of the PostgreSQL COPY utility. In this article, we showed you how to use the COPY command to copy data between a file and a PostgreSQL table. If you’ve followed along with the examples in this tutorial, you’ll be prepared to use this command to copy data from and to your own PostgreSQL tables.