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 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:
1 2 3 4 5 6 7 8 | id | name | lastname | age ----+-------+----------+----- 1 | matt | dawson | 13 2 | john | johnson | 21 3 | fred | flint | 23 4 | drake | gray | 20 5 | jess | dust | 13 (5 rows) |
Shown below is the structure of our sample person
table:
1 2 3 4 5 6 7 8 9 | Table "public.person" Column | Type | Collation | Nullable | Default ----------+-----------------------+-----------+----------+--------- id | integer | | not null | name | character varying(50) | | | lastname | character varying(50) | | | age | integer | | | Indexes: "person_pkey" PRIMARY KEY, btree (id) |
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:
1 | COPY TableName FROM PathOfFile DELIMITER ',' CSV HEADER; |
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:
1 | COPY person FROM 'C:\temp\ObjectRocket\PostgreSQL\person_list.csv' DELIMITER ',' CSV HEADER; |
Right after executing this command, PostgreSQL will respond with the following message:
1 | COPY 5 |
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:
1 2 3 4 5 6 7 8 9 10 11 12 13 14 | persondb=# SELECT * FROM person; id | name | lastname | age ----+--------+-----------+----- 1 | matt | dawson | 13 2 | john | johnson | 21 3 | fred | flint | 23 4 | drake | gray | 20 5 | jess | dust | 13 6 | rosana | del torro | 15 7 | Yeshua | Israel | 18 8 | Kate | Winston | 19 9 | Harley | Price | 19 10 | Jenny | Dean | 18 (10 ROWS) |
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:
1 | COPY <SELECT QUERY> TO <file path>; |
Let’s use this command to copy the contents of our person
table into a file:
1 | COPY (SELECT * FROM person) TO 'C:\temp\ObjectRocket\PostgreSQL\person_list_copy_to.csv' DELIMITER ',' CSV HEADER; |
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:
1 | COPY 10 |
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.
Pilot the ObjectRocket Platform Free!
Try Fully-Managed CockroachDB, Elasticsearch, MongoDB, PostgreSQL (Beta) or Redis.
Get Started