Postgres Copy

Introduction

This tutorial will explain how the Postgres Copy function can be utilized to transfer and acquire data. PostgreSQL contains a simple, but extremely powerful, “copy” command that is designed to copy data and append the new information to an existing data file or table. The copy function can be used to import and export data quickly and easily, moving data between tables and files, or to a table from a file and vice versa. Whether for bulk-data processing, testing purposes or just forwarding data, every developer and data processor should know how to use the Postgres Copy function.

Prerequisites

  • PostgreSQL must be properly installed and configured on the local system to use the Postgres Copy function.

What is Postgres Copy

The Postgres Copy function is highly efficient for transferring and acquiring data from various sources. The most common use of the function are the pg_restore and pg_dump functions used for database backup and recovery purposes.

While not as popular as others utilities, the copy function is highly efficient. The copy utility allows for copying data in and out of database files and supports tabbed-delimited, comma-delimited and binary modes.

Creating a Sample Dataset

This section will explain how to create a sample dataset used for the examples in this tutorial.

  • The following command will create the sample database:
1
CREATE DATABASE residentdb;
  • The following commands will set up the table structure:
1
2
3
4
5
6
7
CREATE TABLE residentInfo_2 (
    id INT PRIMARY KEY,
    name VARCHAR (50),
    lastname VARCHAR (50),
    city VARCHAR (100),
    country VARCHAR (50)
);
  • The following commands will insert records into the database:
1
2
3
4
5
6
7
8
9
10
11
12
 INSERT INTO residentInfo (id, name, lastname, city, country)
VALUES
    (1, 'jason', 'mendez', 'guadalajara', 'mexico'),
    (2, 'miguel', 'triaz', 'juarez', 'mexico'),
    (3, 'robert', 'muega', 'olongapo', 'philippines'),
    (4, 'gina', 'doe', 'los angeles', 'united states'),
    (5, 'shine', 'domingo', 'manila', 'philippines'),
    (6, 'pradeep', 'singh', 'goa', 'india'),
    (7, 'norman', 'dune', 'new york', 'united states'),
    (8, 'chen', 'fan', 'wuhan', 'china'),
    (9, 'ling', 'peng', 'puket', 'thailand'),
    (10, 'lin', 'feng', 'tongcheng', 'china');

The results of inserting the above data should resemble the following table:

1
2
3
4
5
6
7
8
9
10
11
12
13
 id |  name   | lastname |    city     |    country
----+---------+----------+-------------+---------------
  1 | jason   | mendez   | guadalajara | mexico
  2 | miguel  | triaz    | juarez      | mexico
  3 | robert  | muega    | olongapo    | philippines
  4 | gina    | doe      | los angeles | united states
  5 | shine   | domingo  | manila      | philippines
  6 | pradeep | singh    | goa         | india
  7 | norman  | dune     | new york    | united states
  8 | chen    | fan      | wuhan       | china
  9 | ling    | peng     | puket       | thailand
 10 | lin     | feng     | tongcheng   | china
(10 rows)

Postgres COPY Example

This section will explain how to use the COPY utility.

Postgres COPY TO

To export or dump records out of a SELECT query, and be able to create a CSV file that holds all the tables content, execute the following COPY TO command:

1
COPY (-SELECT query here-) TO -desired-file-path-;

Now execute the below query command to use the above code in a real example:

1
COPY (SELECT * FROM residentInfo) TO 'E:/TEST/RESIDENT.CSV' DELIMITER ',' CSV HEADER;

The results should resemble the following image:

The above query will select all of the rows from the residentInfo table and copy the content of those rows to a specific path with a file name of RESIDENT.CSV. Here the file will use the column fields as the header and present it in comma-delimited format.

Open the file using notepad to verify the content. The results should resemble the following image:

Postgres COPY FROM

The previous section explained how to use the COPY TO query. This section will explain how to use the COPY FROM query.

Following is the basic syntax for executing the COPY FROM query:

1
COPY <table_name> FROM <path_of_file> DELIMITER ',' CSV HEADER;

Execute the below COPY FROM query command to use the above code in a real example:

1
COPY residentinfo_2 FROM 'E:/TEST/RESIDENT.CSV' DELIMITER ',' CSV HEADER;

The above code will copy data from the file that was created earlier to the table named residentinfo_2.

Postgres should display following prompt after executing the above code:

1
COPY 10

Now execute a SELECT ALL query against the residentinfo_2 table to verify the COPY FROM operation was successful. The results should resemble the following:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
residentdb=# SELECT * FROM residentinfo_2;
 id |  name   | lastname |    city     |    country
----+---------+----------+-------------+---------------
  1 | jason   | mendez   | guadalajara | mexico
  2 | miguel  | triaz    | juarez      | mexico
  3 | robert  | muega    | olongapo    | philippines
  4 | gina    | doe      | los angeles | united states
  5 | shine   | domingo  | manila      | philippines
  6 | pradeep | singh    | goa         | india
  7 | norman  | dune     | NEW york    | united states
  8 | chen    | fan      | wuhan       | china
  9 | ling    | peng     | puket       | thailand
 10 | lin     | feng     | tongcheng   | china
(10 ROWS)

Conclusion

This tutorial explained how the Postgres Copy function can be utilized for the quick and easy transfer of data. The tutorial provided a detailed explanation of what the Postgres Copy function is and covered how to create a sample dataset. The article also provided a Postgres Copy example, explained the differences between the COPY TO andCOPY FROM queries and explained how to verify the Copy commands were successful. Remember that the Copy utility permits copying data both in and out of database files and supports both tabbed- and comma-delimited, as well as binary, modes.

Pilot the ObjectRocket Platform Free!

Try Fully-Managed CockroachDB, Elasticsearch, MongoDB, PostgreSQL (Beta) or Redis.

Get Started

Keep in the know!

Subscribe to our emails and we’ll let you know what’s going on at ObjectRocket. We hate spam and make it easy to unsubscribe.