How to Import and Export CSV Files in PostgreSQL: A Full Guide

This article is a complete guide on how to import and export CSV files to the Postgres database. We will look into different methods to import CSV file to PostgreSQL and export Postgres data to CSV. The methods include the command line, i.e., psql, using the user interface of pgAdmin, and the Skyvia tool, which allows you to automate import and export on schedule. First, we will consider the Postgres export to CSV and later focus on the data import to the Postgres database.

Table of contents

1. Sample Table for PostgreSQL CSV Export and Import
2. How to Perform Postgres Export to CSV
   • Method 1: Copy Postgres Table to CSV File via Command Line
   • Method 2: Export Data from PostgreSQL to CSV with pgAdmin 4 
   • Method 3: Exporting Data Using Skyvia
3. How to Import CSV to PostgreSQL Table
   • Method 1: Import from CSV File to PostgreSQL via Command Line Using COPY Statement
   • Method 2: Import CSV to PostgreSQL with pgAdmin 4
   • Method 3: Importing Data to PostgreSQL Using Skyvia
4. Troubleshooting
5. Conclusion 

Sample Table for PostgreSQL CSV Export and Import 

We will use the sample table with employee data to show the scenarios of CSV export and import to PostgreSQL. Here is the structure of the table:

CREATE TABLE employees (
 emp_id SERIAL, 
 first_name VARCHAR(20),
 last_name VARCHAR(20), 
 date_of_joining DATE, 
 email VARCHAR(255), PRIMARY KEY (emp_id) 
); 

How to Perform Postgres Export to CSV

This section will demonstrate several methods for exporting data from PostgreSQL.

Method 1: Copy Postgres Table to CSV File via Command Line

First, we will show how to use the PostgreSQL COPY command to export a PostgreSQL table. You can use the COPY command on the client-side and on the server side. Use it on the client-side to export a PostgreSQL table to CSV and save it on a local computer. Use it on the server-side to copy your data to CSV on the server. 

For both options, the export process from Postgres to CSV using the copy command is pretty straightforward, and we can repeat it to export all tables to CSV.

PSQL \Copy Command for Client-Side Export

Psql \copy command is used when you want to export the data from a Postgres table to a CSV file on a client machine. To use this command, you will need access to the psql prompt. You will understand it more with the following psql copy examples.

To copy the entire table to a CSV file, use \copy. This will copy the contents of a table to the client computer. The file will not contain the headers of the table.

\copy employees to '/var/lib/postgresql/emp.csv' csv; 

You can also export the results of a query instead of copying the entire table.

\copy (select * from employees where first_name='Alex') to '/var/lib/postgresql/emp_alex.csv' csv;

As output, it will show the total number of records that have been copied using psql copy.

Export Postgres Table to CSV with Header 

By default, the result file, exported by the client-side Psql \copy command, has no header. To get a CSV file with headers, we need to add the ‘header’ keyword to the command. It looks like this:

# Complete table 
\copy employees to '/var/lib/postgresql/emp_header.csv' csv header; 

# Specific records using a query 
\copy (select * from employees where first_name='Alex') to '/var/lib/postgresql/emp_alex.csv' csv header;

The above commands allow you to include headers in the result files.

Postgresql Copy Command for Server-Side Export 

Let’s show how to export the data to a CSV file on the server, using the PostgreSQL copy command. This command will copy the contents to a specified location on the server. 

First, you need to connect to the database of the server through a Postgres shell prompt. Then you can use the following Postgres copy example.

# Complete table 
COPY employees TO '/var/lib/postgresql/emp_server.csv' csv header; 

# Specific records using the query 
COPY (select * from employees where first_name='Alex') TO '/var/lib/postgresql/emp_server_alex.csv' csv header;

You can read more about the command in the PostgreSQL documentation.

Method 2: Export Data from PostgreSQL to CSV with pgAdmin 4 

pgAdmin is an open-source tool that helps in the administration and management of Postgres databases and their development. In this section, we will focus on exporting the table contents. 

To export data using pgAdmin, follow the steps below:

1. Right-click the employees table and select Import/Export in the shortcut menu.

2. Specify the path where the file needs to be saved. In the Miscellaneous section, select Headers to export the headers along with the data. Click OK to start the export process. The data will be successfully exported into the file.

Method 3: Exporting Data Using Skyvia

Skyvia is a cloud-based solution that helps you automatically import/export data from PostgreSQL to CSV files. Skyvia allows not only importing/exporting CSV files from a local computer but also automatically loading files to/from file storage services or FTP/SFTP servers. So, we will show loading CSV files between PostgreSQL and Dropbox.

You need to have an account on Skyvia and Dropbox. Both are free to register. 

Creating Connections on Skyvia

After you register on Skyvia, you need to create connections to your PostgreSQL and Dropbox. To create a connection on Skyvia, click NEW and select Connection in the menu on the left.

Then, in the list of connectors, select the data source you want to connect to and configure the required parameters. 

For Dropbox, you just sign in with Dropbox and allow Skyvia to access your data.

For PostgreSQL connection, you need to specify the server address, port, username and password, database and schema. Optionally, you can configure encryption parameters for setting up secure connection.

Creating Integration for Data Export from PostgreSQL

1. Click NEW and select Export under Integration, similar to creating an import integration.
2. Under Source, select the PostgreSQL connection.
3. Move on to Target and select CSV to storage service. Then select the connection to Dropbox you have created, and then select the folder to place the result file with exported data will be located to.

4. Click Add new on the right top of the page to add an Import task. In the task, you specify a table to export.
5. In the Object list, select the table to export. Here, you can also configure filters to export only some of the records, you can clear checkboxes for some of the fields in order not to export them, specify the file name and compression, etc.

6. Finally, you can schedule the export integration (as well as any other integration on Skyvia) for automatic execution on certain days and at specific time. Enjoy the process with no more effort!

How to Import CSV to PostgreSQL Table

So far, we have seen how to export the data to a CSV file. Now we shift our focus to importing data from CSV to Postgres table. There are different methods like using the command-line interface, third-party tools, etc., that you can use for import to PostgreSQL. We will elaborate these methods more below. We won’t show how to create a table from the CSV file in Postgres in our tutorials. The table needs to be created first then we can import the data into the table otherwise you might see an error while importing the data. 

Method 1: Import from CSV File to PostgreSQL via Command Line Using COPY Statement

We can make use of the PostgreSQL copy command to copy from the CSV file and import it to Postgres. The command can import a file with or without a header into the table, provided that the file has the same columns of the same type. While exporting the data, we use the TO keyword in the copy command, whereas while importing the data into a Postgres table, we need to use a FROM keyword. 

Both server-side COPY command and client-side \copy command can be used to import data to PostgreSQL tables. Here are examples of PostgreSQL COPY commands both for the client side and server side.

COPY employee_import FROM '/var/lib/postgresql/employee.csv' csv header; 
\copy employee_import FROM '/var/lib/postgresql/employee.csv' csv header;

Method 2: Import CSV to PostgreSQL with pgAdmin 4  

Let’s show how to import CSV files to Postgres tables with pgAdmin. To import data to PostgreSQL with pgAdmin, perform the following steps:

1. Right-click the table and select Import/Export from the shortcut menu to open the wizard.

2. Click the Import/Export flag button to import the data. Select the file that needs to be imported. Enter the delimiter of the file and enable the header option if the file has a header. Click OK to start the importing process.

3. After clicking OK, a window indicating that the process has been successfully completed.

Method 3: Importing Data to PostgreSQL Using Skyvia

We have described Skyvia and how you connect it to PostgreSQL and Dropbox in the Export section, so let’s get to importing CSV to Postgres immediately.

To create an Import integration on Skyvia, perform the steps below:

1. Click NEW and select Import under Integration.

2. Under Source, select CSV from storage service, and then select the source Dropbox connection.
3. Move on to Target and choose your PostgreSQL connection.

4. Click Add new on the right top of the page to add an Import task. In the task, you specify the file and where and how to load it.

5. Select the file to load data from and click Next.
6. Select the employees table to load data to and click Next.

7. Map source and target columns to each other and click Finish.

Note that Skyvia also offers integration tools for other scenarios than PostgreSQL CSV import and export. It supports over 200 of different cloud apps, databases, and file storage services, and suits for most data integration scenarios.

Troubleshooting  

When importing or exporting data to PostgreSQL, the following common mistakes may occur.

1. Delimiter Is Incorrect

It’s essential to specify the delimiter used in the CSV file correctly. All solutions described, including Skyvia, COPY command, and pgAdmin, support specifying a delimiter.

2. Data Types are Not Matching

You need to make sure that columns of the source file match to the PostgreSQL table columns in case of using COPY command or dbAdmin. Skyvia offers more flexibility, since it allows mapping columns manually and performing type conversions via expression mapping. 

3. Not Enough Privileges

It’s also important to correctly configure privileges on PostgreSQL. The user must have access to the corresponding table, as well as enough privileges for the directory to where the file is saved, and from where it is imported (for the server-side COPY command).

Conclusion

In this blog article we have demonstrated several ways to export and import CSV files to PostgreSQL tables. We demonstrated both standard PostgreSQL tools and third-party no-coding solutions, like Skyvia. 

Skyvia includes a free plan that allows importing/exporting up to 10000 records per month, so such tasks of import/export to PostgreSQL can be performed for free, as with standard tools. Besides, it offers flexible mapping settings that allow you to transform data via different string, mathematical, datetime functions, lookups, support for file storages and FTP, automatic file compression for export, etc.

You are welcome to share with us what kind of method you personally prefer among the ones listed above in the Comments section. You can also suggest other methods, not mentioned in this article.

F.A.Q.