It’s easy to import a CSV file into a database, and export from a database into a CSV file. There are a few different techniques you can use to do so with PostgreSQL.
Create a CSV File
Here is some sample CSV data you can use to create your own CSV file:
Before importing the CSV file into the PostgreSQL database, you will need to create a table that matches the CSV file format. Use the following code to create a Postgres table.
Import CSV Files to PostgreSQL
You can import CSV files to a Postgres table on the server or on a client machine.
Import CSV Files on the Server-Side
To import a CSV file to a PostgreSQL server, use the PostgreSQL COPY command and the FROM keyword. This allows you to copy data from a CSV file to a Postgres table, provided the structure matches. Use the following code to copy the sample CSV file to the employees table:
You can copy the data with or without headers so feel free to omit the last line of code if you don’t need them.
Import CSV Files on the Client Side
On a client machine, use the psql \copy command to import the CSV file. Run the following code on the psql command prompt to copy the sample CSV file to the employees table.
Export CSV Files From PostgreSQL
As with importing, you can also export a CSV file on the server-side or the client-side.
Export CSV Files on the Server-Side
Use the COPY command and the TO keyword to export the data to a CSV file. You need to specify the source table and the path to the destination file. This time, the path must be absolute:
If you only need to export some of the rows from the database, use the SELECT command as follows:
Export CSV Files on the Client Side
To export data on the client-side, use the \copy command and the TO keyword in the psql command prompt:
Using a Database Adapter
You don’t have to use the terminal to import a CSV file. You can import the data through a cursor object using your preferred programming language. For example, if you work with Python, you can connect to the PostgreSQL server and execute the COPY command.
For this, you need to connect Python to Postgres through a database adaptor like psycopg2.
Connect to the database using the following code:
Then create a cursor object and use it to execute the COPY command:
Manage Your PostgreSQL Data With the Convenience of CSV
It’s a simple process to import and export data using PostgreSQL and CSV files. You can use the psql commands on the client or server, or use a database adapter programmatically.
If you’re more of a GUI person, consider using the pgAdmin tool. It is an open-source GUI application that can help you import and export data even more easily.
