Complete Guide on How to Import and Export CSV Files to PostgreSQL

This article is a complete guide for you to import and export CSV files to the Postgres database. We will look into different methods to perform this task. Every method is illustrated with examples and images to better understand it for readers. Some methods use a command-line interface that is more compatible with the database administrators, while others use graphical user interfaces and third-party tools that are easy to understand for non-technical audiences as well.

After reading this article, you will be able to import CSV file to PostgreSQL and export Postgres data to CSV. A CSV (Comma-separated values) file generally refers to a file where the columns and their corresponding values are separated by a specific character like a comma.

You can import and export data, using different methods such as command line i.e. psql, using the user interface of pgadmin, and doing it automatically at the scheduled intervals with the help of the Skyvia tool. First, we will consider the Postgres export to csv and later focus on the data import to the Postgres database.

Table of contents

1. Create and Prepare Table Structure for CSV File
2. How to Copy Postgres Table to CSV File via Command Line
   • PSQL \Copy Command for Client-Side Export
   • Export Postgres Table to CSV with Header
   • Postgresql Copy Command for Server-Side Export
3. How to Import CSV to Postgres Table
   • Import from CSV File to Postgresql by Command Line Using COPY Statement
   • Import and Export CSV to Postgresql with pgAdmin 4
4. Easy Copy and Load CSV Format Files Automatically
5. Conclusion

Create and Prepare Table Structure for CSV File

To get started with the activity, let us create a table in Postgres that will be used to export to csv. We will create a table named employees containing its details.

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)
);

We will use this table to import and export the contents. You can download a sample csv file here to use it.

How to Copy Postgres Table to CSV File via Command Line

This section will guide you on how to export data from PostgreSQL to CSV. The data can be exported with or without headers. It can be done from the client-side as well as the server-side. The COPY command can help you to export a table to CSV. It will read the contents of the table and export as CSV. 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.

There are two different variants of the command, one for the client-side and the other for the server-side. When we are using the command for the client-side import/export, it will export a PostgreSQL table to CSV and save it on the client computer. While using the server-side command, it will run on the server and copy to CSV on the server end. Let us consider the copy query in the below sections.

PSQL \Copy Command for Client-Side Export

Psql \copy command is used when you want to export the data from 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 as a CSV file. The file will not contain the headers of the table.

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

You can also write 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

In the above section, we exported the table data without headers. Now we will export the data including the headers. We will use the copy command to export CSV with headers.

To get the headers, we need to add the ‘header’ keyword after the ‘csv’ keyword in 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;

After executing the above commands, you will be able to export the table to CSV with headers.

Postgresql Copy Command for Server-Side Export

This section will instruct on how to export the data to a CSV file in the server, using the PostgreSQL copy command. It will copy the contents to a specified location on the server.

To do this, you need to connect to the database of the server through a Postgres shell prompt. Look at the below Postgres copy example for a better understanding of how to do it.

# 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;

The difference between the two commands, i.e. COPY and /copy is that the former is server-based and the latter is client-based. We demonstrated copy command examples for both, client-based as well as server-based exports.

How to Import CSV to Postgres 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., to import CSV to PostgreSQL. We will consider it more in the below sections. By using these methods, we cannot create a table from the CSV file in Postgres. The table will need to be created first then we can import the data into the table otherwise you might see an error while importing the data.

Import from CSV File to Postgresql by 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. We can import the data into the table with or without headers provided the CSV should be in the same format. 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.

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

Using the above commands, one will be able to copy from a CSV file, using the PostgreSQL command line.

Import and Export CSV to Postgresql 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 creating a table, importing and exporting the table contents.

To create a table in pgAdmin, follow the below steps:

1. In the schema section, select a table and right-click to see options related to tables. Hover on the Create option, then click on Table to open the wizard for creating a table.

2. Enter table-specific details like table name, columns, etc. Click on Save to create the table.

To import CSV to PostgreSQL with pgAdmin, follow the below steps:

• After creating the table, right-click on the table to see the options. Choose the Import/Export option to open the wizard.

• Click on 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 consists of the headers. Click on OK to start the importing process.

• After clicking on OK, you will notice a popup showing that the process has been successfully completed. This process demonstrates how you can import CSV into PostgreSQL, using pgAdmin 4.

To export CSV using pgAdmin, follow the below steps:

• Open the wizard by clicking on the Import/Export option shown by right-clicking the Table.

• 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 on OK to start the exporting process. The data will be successfully exported into the file from the table.

Easily Copy and Automatically Load CSV Format Files with Skyvia

Skyvia is a cloud-based solution that helps you automatically import/export data from PostgreSQL to CSV files. In this section, first, we will focus on how to create a connection to PostgreSQL and Dropbox and, second, how to create a package to repeatedly execute data export from PostgreSQL to CSV files in your Dropbox file storage.

Before starting to use this solution, you need an account on Skyvia and Dropbox. Both are free to register.

Creating a Connection to PostgreSQL and Dropbox

1. Click NEW and select Connection in the menu on the left.

2. In the Select Connector page, select PostgreSQL. In the opened window, specify required PostgreSQL parameters as shown below and click Create Connection at the bottom left of the window.

3. Next, create a connection to Dropbox in the same way as described in the steps above. But it is much easier to do, you simply sign in to your Dropbox account via OAuth 2.

Creating a Package for Data Export from PostgreSQL

• Click NEW and select Export under Integration, i.e., you repeat almost the same actions as in Step 1 above.
• In the open package editor window, under Source in the drop-down list, select the PostgreSQL connection created a bit earlier.
• Move on to Target and click CSV to storage service. In the drop-down list, select Dropbox, and below, select the folder in your CSV file with exported data will be located to. In our case, it is the Employees folder.

• Click Add new on the right top of the page to add a task where you can specify editor mode on how to export data – simply selecting an object you need in PostgreSQL or executing the entered query/command to export data to CSV. As you can see on the sample screenshot below, we select the Products object and apply the necessary filter settings. When the task is ready, we save the task.

• Finally, schedule your package for automatic execution on certain days and at specific time. Enjoy the process with no more effort!

Conclusion

Upon reading this article, the readers would get a grip on importing and exporting data from/to the CSV file using multiple methodologies. Import and export of data become a crucial task for many organizations as they need to import data from different locations for analytical purposes. For such tasks, it is better to opt for scheduled automated jobs that can be performed without any human intervention.

In the Comments section, share with us what kind of method you personally prefer to use amongst the ones listed above. You can also suggest some other methods, which you know to import/export the Postgres table from/to a CSV file.