CSV files simplify the process of storing data. As the format is row- and column-based, CSV translates well to a relational database.

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:

        id,firstname,lastname,email
1,Riannon,Pulsifer,Riannon.Pulsifer@example.com
2,Moyna,Palocz,Moyna.Palocz@example.com
3,Roslyn,Bearnard,Roslyn.Bearnard@example.com
4,Gloria,Aldric,Gloria.Aldric@example.com
5,Felice,Greenwald,Felice.Greenwald@example.com

    

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.

        CREATE TABLE employees(id int NOT NULL,
                       firstname char(20), 
                       lastname char(20),
                       email char(50));

    

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:

        COPY employees(id,firstname,lastname,email)
FROM '/tmp/sample.csv'
DELIMITER ','
CSV HEADER;

    

The path in the above example is absolute. You can use a path relative to your Postgres data directory if you prefer.

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.

        \copy employees FROM '/tmp/sample.csv' DELIMITER ',' CSV HEADER;

    

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:

        COPY employees TO '/tmp/employees.csv' CSV HEADER;

    

If you only need to export some of the rows from the database, use the SELECT command as follows:

        COPY (SELECT * FROM employees where firstname='Moyna') TO '/tmp/employees-moyna.csv' CSV HEADER;

    

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:

        \copy employees TO 'path-to-file.csv' CSV header;

    

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.

        pip install psycopg2
    

Connect to the database using the following code:

        import psycopg2;
 
connection = psycopg.connect(
    database="employee_db",
    host="localhost"
    user="postgres",
    password="password",	
)

    

Then create a cursor object and use it to execute the COPY command:

        cursor = connection.cursor()
 
cursor.execute(
	"COPY employees(id,firstname,lastname,email)
	FROM '/tmp/sample.csv'
	DELIMITER ','
	CSV HEADER;"
)
 
connection.close()
    

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.