Overview

In this article, we will cover a very important technique of working with a PostgreSQL table. Let us take a look at the topics we will cover in this article:

• Export table to CSV file.
• Import CSV file into table

The only prerequisite for this article is to have decent knowledge of SQL table. The following article can help you learn about PostgreSQL tables and if you are already aware of it, feel free skip to the next section of the article: https://www.sqlservercentral.com/articles/a-getting-started-postgresql-tutorial

Export PostgreSQL table to CSV file

Exporting a table to a CSV file is one of the most important and commonly used features in practical world, be it in PostgreSQL or any other database platform. It is absolutely necessary that one working with relational databases must know how to complete this task.

Let us first create a table and enter the some data into it to explore the technique.

CREATE TABLE books(
   id SERIAL PRIMARY KEY,
   book_name VARCHAR,
   author_name VARCHAR,
   genre VARCHAR 
);

INSERT INTO books(book_name, author_name, genre)
VALUES('Tell me your dreams', 'Sidney Sheldon', 'thriller'),
    ('Harry Potter', 'J. K. Rowling', 'fiction'),
    ('James Bond', 'Ian Fleming', 'thriller'),
    ('What Young India Wants', 'Chetan Bhagat', 'non-fiction');

After executing both the queries, the table should like this:

Now let us take a look at the syntax to export the above table as  a csv file,

COPY table_name TO 'path/file_name.csv' CSV HEADER;

\copy books to 'C:/books.csv' CSV HEADER

Note: Make sure that the specified path has read/write permission else 'permission denied' error would be thrown as shown below:

When successfully executed, it will create a csv file named books directly under C:\ drive.

The successfully created CSV file would look like below:

Export PostgreSQL table to CSV file with selective columns

While the above example shows the process to export the entire table to csv file, it is also possible to specify columns that we want to export. Let us take a look at the query:

COPY (SELECT book_name,author_name FROM books) TO 'C:/books.csv' CSV HEADER;

Import CSV file into PostgreSQL table

In the previous section of the article, we saw how we can export a table to a csv file. This section of the article will cover the other way round.

Basic syntax

COPY table_name(col1, col2, colN) 
FROM 'PATH/file_name.csv' DELIMITER ',' CSV HEADER;

As we would be using the same table from the above section, let us first truncate the table for better understanding

Let us now execute the 'COPY FROM' query to import data from the csv file,

COPY books(book_name, author_name, genre) 
FROM 'C:/books1.csv' DELIMITER ',' CSV HEADER;

Here, 'COPY 2' indicates total two records have been imported into the table. Let us do a select to verify the same,

Query Explanation and pre-requisite

Prerequisite is that the table must exist in database prior to executing the import. Secondly, the user must be logged in as superuser in order to execute the COPY FROM command. Sometimes it is seen though user is logged in as superuser, user is still denied import. In those cases it must be ensured that the file is kept in an accessible location and given full rights to be able to to accessed by everyone.

It is important to mention the DELIMITER as well as CSV keywords. The HEADER keyword denotes that the CSV file comprises a header line with column names. When importing data, PostgreSQL neglects the first line as they are the header line of the file.

Import/Export Via PgAdmin

PgAdmin supports import/export of a table to/from a csv file via its interface. Let us take a look at the following images to understand the functionality. Navigate to Schemas->Table->Import/Export Data

Select Import or Export depending on the requirement and specify the file name.

Switch to the Columns tab to select the columns for the import/export operation. By default all the columns are selected.

Conclusion

This article covers an important way of working with a PostgreSQL table. We hope this article will help you to understand and learn the ways of importing/exporting PostgreSQL table to and from CSV file.

What is PostgreSQL?

If you know nothing about PostgreSQL, here's an article on what it is and what businesses need to know.

If you want to start learning about PostgreSQL, Grant Fritchey has a series that can help.