PostgreSQL Copy Example
Introduction to COPY
In PostgreSQL, the SQL
COPY command is used to make duplicates of tables, records and other objects, but it’s also useful for transferring data from one format to another. For example, the
COPY command can be used for inserting CSV data into a table as PostgreSQL records. This article will provide several PostgreSQL COPY examples that illustrate how to use this command as part of your database administration.
PostgreSQL COPY statement
COPY statement is similar to the
pg_restore commands that are used for backing up and restoring tables and databases. You can use the
COPY command to move files within a PostgreSQL table or to put the data from a PostgreSQL table into a file.
Before we begin looking at our PostgreSQL COPY examples, we’ll need to review some of the prerequisites for this tutorial:
- First, you’ll need to have PostgreSQL installed and running on your machine. If you’re using Linux, try the
service postgresql statuscommand in a terminal to see if PostgreSQL is installed and running. You can then use CTRL + C to exit the process. On macOS or Linux, you can also pipe the
psto look for any PostgreSQL processes currently running:
You can also use
psql -V to confirm that the psql command-line interface is installed and working; this command returns the utility’s version and installation information.
You should see output that looks like the following:
Connect to the psql command-line interface for PostgreSQL
Once all the prerequisites are in place, you can try connecting to the psql interface. Use the
sudo su - postgres in a terminal followed by
psql --help command can be used to get more information about connecting to a PostgreSQL database with a user.
Create a PostgreSQL database
Here’s the command we’ll use in the psql interface to create a PostgreSQL database:
Now we can connect to our database using the command
\c so we can start entering queries.
Create a PostgreSQL table
Next, we’ll create a table to use in our examples:
id SERIAL PRIMARY KEY,
PostgreSQL copy examples
At this point, we’re ready to dive into some examples of how we can use the SQL
COPY command to duplicate or transfer data.
Copy text file data into Postgres
TEXT file format consists of rows made up of columns that are separated by a delimiter character; the data is then written or read as a text file with one line of a table per row.
FROM / TO 'filename' DELIMITER ' ';
We can use the PostgreSQL COPY command to export a CSV file into our demo table with a
TEXT file format:
'demo.txt' DELIMITER ' ';
Copy binary data into Postgres
BINARY file format is identified as a ‘non-text’ file since all of the data is stored or read as binary format. This can be faster than the text and csv format.
Let’s try using the
COPY command to export a single record of a table in a binary file. Here’s the basic syntax:
SELECT col1, col2, col3
TO STDOUT WITH BINARY;
For our example, the command will look like this:
SELECT firstname, lastname
FROM demo WHERE id=50
) TO STDOUT WITH BINARY;
Insert CSV data into PostgreSQL using COPY
Now that we’ve looked at a couple examples of exporting data, let’s try using
COPY to insert CSV row data into a PostgreSQL table. We’ll use the following two rows of CSV test data to illustrate how you can use
COPY to insert data into Postgres from a CSV file:
1, "I'm from a CSV file", 112233, true
2, "hello, Postgres", 8765, false
When we discuss CSV files, there are a couple of key terms to understand:
DELIMITER– A delimiter is a character that separates each row of the file into columns; in a CSV file, the delimiter is a comma. Delimiters cannot be used in binary format.
HEADER– When a CSV file is created, the header line is the first line of the file that contains the column names.
Use the COPY and FROM keywords to insert Postgres data from a file
The following SQL statement will insert CSV data rows into a table called
DELIMITER ',' CSV HEADER;
NOTE: Be sure to pass the absolute path for the file as a string in your
FROM clause to avoid any errors.
Use COPY to trim whitespace from PostgreSQL table data
One minor issue with using
COPY to insert CSV data is that it will result in leading and trailing whitespace in our string column. Fortunately, there’s a simple solution to this problem. Let’s try to insert our CSV data again, and this time we’ll use a temporary table to store the data. We’ll then use SQL’s
LTRIM function to strip the whitespace from our string column before we insert it into our permanent PostgreSQL table.
NOTE: Make sure to truncate the test table by typing
TRUNCATE some_table; into psql before re-inserting the CSV data so as to avoid any duplicate key errors.
Create a temporary table to store the CSV data
The following SQL statement will create a temporary table for our CSV data:
SELECT * FROM some_table LIMIT 0;
This SQL command will return
SELECT 0 if successful.
Now, let’s try the
COPY command again, except this time we’ll copy the CSV rows to the temporary table:
DELIMITER ',' CSV HEADER;
The SQL statement shown above should return
COPY followed by the number of CSV rows that it copied.
Use LTRIM() to strip the whitespaces from the PostgreSQL table data
Our next example will insert the data from the temporary table into the permanent table. When we execute this
INSERT statement, we must explicitly specify each column header and make sure to pass the string column to the
LTRIM() function in order to strip the whitespaces:
(id, str_col, int_col, bool_col)
SELECT id, LTRIM(str_col), int_col, bool_col
After executing this
INSERT command, the records should be copied from the temporary table into our
some_table PostgreSQL table. Now we’ll use the
SELECT * FROM some_table statement to verify that the CSV data was inserted without the leading and trailing whitespaces.
The command shown below is only required if you need to free up memory or space for the short term; otherwise, the temporary table will be dropped automatically when you end your psql session:
There’s no doubt that the SQL
COPY command can be used for a variety of database administration tasks. Not only can you use the command to duplicate tables and records, but you can also use
COPY to transfer data from one format to another. In this article, we showed you PostgreSQL COPY examples that illustrated how to both import and export data between a CSV file and a PostgreSQL table. With these examples to get you started, you’ll be able to harness the power of the
COPY command in your own PostgreSQL implementation.
Pilot the ObjectRocket Platform Free!
Try Fully-Managed CockroachDB, Elasticsearch, MongoDB, PostgreSQL (Beta) or Redis.Get Started