PostgreSQL Copy Example

Written by Data Pilot
December 09, 2019
  • PostgreSQL
Have a Database Problem? Speak with an Expert for Free
Get Started >>

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 the grep command through ps 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.

Screenshot of a psql PostgreSQL copy example to insert CSV data

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.

Screenshot of psql using COPY to LTRIM whitespace in PostgreSQL string data

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.

Rate 1 Star2 Stars3 Stars4 Stars5 Stars

Pilot the ObjectRocket Platform Free!

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

Get Started
Related Topics:
Additional Information:

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.