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
The SQL COPY
statement is similar to the pg_dump
and 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.
Prerequisites
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 status
command 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 thegrep
command throughps
to look for any PostgreSQL processes currently running:
1 | ps aux | grep postgres |
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:
1 | psql (PostgreSQL) 11.5 |
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
. The 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:
1 | CREATE DATABASE somedb; |
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:
1 2 3 4 5 | CREATE TABLE csv_tbl( id SERIAL PRIMARY KEY, firstname TEXT, lastname TEXT ); |
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
A 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.
1 2 | COPY TABLE_NAME (col, col2) 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:
1 2 | COPY demo(firstname, lastname) TO 'demo.txt' DELIMITER ' '; |
Copy binary data into Postgres
A 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:
1 2 3 4 5 6 | COPY ( SELECT col1, col2, col3 FROM TABLE_NAME WHERE {condition} ) TO STDOUT WITH BINARY; |
For our example, the command will look like this:
1 2 3 4 5 | COPY ( SELECT firstname, lastname FROM demo WHERE id=50 ) TO STDOUT WITH BINARY; PGCOPY |
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 2 3 | id, str_col, int_col, bool_col 1, "I'm from a CSV file", 112233, true 2, "hello, Postgres", 8765, false |
CSV terminology
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 some_table
:
1 2 3 | COPY some_table FROM '/Users/macuser/postgres-data.csv' 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:
1 2 | CREATE TEMP TABLE temp_tbl AS 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:
1 2 3 | COPY temp_tbl FROM '/Users/macuser/postgres-data.csv' 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:
1 2 3 4 | INSERT INTO some_table (id, str_col, int_col, bool_col) SELECT id, LTRIM(str_col), int_col, bool_col FROM temp_tbl; |
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:
1 | DROP TABLE temp_tbl; |
Conclusion
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