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
- Create and Prepare Table Structure for CSV File
- How to Copy Postgres Table to CSV File via Command Line
- How to Import CSV to Postgres Table
- Easy Copy and Load CSV Format Files Automatically
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 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;
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 in 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 on 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;
Import and Export CSV to Postgresql with pgAdmin 4
pgAdmin is an open-source tool that helps in the administration and management of the 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:
- 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.
- 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.
Creating a Connection to PostgreSQL and Dropbox
- Click NEW and select Connection in the menu on the left.
- 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.
- Next, create a connection to Dropbox in the same way as described in 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 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 folder your CSV file with exported data will be located to. In our case, it is the Employees folder.
- Click Add new in 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 entered query/command to export data to CSV.As you can see on the sample screenshot below, we select the Products object and apply necessary filter settings. When the task is ready, we save the task.
- Finally, schedule your package for automatic execution at certain days and at specific time. Enjoy the process with no more effort!
Register right now and get 5% off for any Skyvia subscription using this coupon code: SkyviaBlog_2022_5off
Upon reading this article, the readers would get a grip of 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.