Importing CSV files into Postgres tables or exporting tables from PostgreSQL into CSV files are very common. In PostgreSQL, you can import a CSV file to a table using the \COPY statement(CLI) or pgAdmin(GUI).

In this write-up, we will learn various ways to import a CSV file to a Postgres table. So, let’s begin.

How to Use the \COPY Command in PostgreSQL?

You can use the \COPY statement to import a CSV(comma separated values) file to a Postgres table. Make sure that the column’s order should be the same in the CSV file and Postgres table. Following will be the syntax of the COPY statement for importing CSV files to the PostgreSQL table:

\COPY tab_name(col_list) FROM 'file_path' CSV HEADER;

Example:

We have created a table named author_details whose structure is as follows:

SELECT * FROM author_details;

The author_details table has two columns: a_id and author_name. We have created a CSV file that contains the following records:

Our CSV file is located in C:\Windows\Temp\authorInfo.csv. So, we will specify this path in the COPY statement to import the targeted CSV file:

\COPY author_details(a_id, author_name) 
FROM 'C:\Windows\Temp\authorInfo.csv' DELIMITER ',' CSV HEADER;

The above snippet shows that three records have been copied to the targeted Postgres table, i.e., the author_ details table. Let’s run the SELECT command to verify the working of \COPY command:

SELECT * FROM author_details;

The CSV file data has been successfully imported to the author_details table.

Importing CSV File to a Postgres Table Using pgAdmin4?

We have already created a table named author_data. Let’s execute the below statement to see the table’s structure:

SELECT * FROM author_data;

Now, to import the desired CSV file, right click on the targeted table and select the “Import/Export Data”:

Clicking on the Import data will open the following window:

Firstly, select the Import option, specify the CSV file’s address, select the file format, and specify a delimiter. Now switch to the “Columns” window:

You will see the columns’ names in the columns to import tab. By default, Postgres will import all the columns; however, you can specify the columns of your choice. Clicking on the OK button will lead you to the following window:

Congratulations you have successfully imported the CSV file’s data into the desired Postgres table. You can verify the table’s records using the SELECT query as follows:

SELECT * FROM author_data;

This is how you can import a CSV file using pgAdmin.

Conclusion

In PostgreSQL, you can import a CSV file to a table using various methods such as the \COPY statement(CLI) or pgAdmin(GUI). When importing a CSV file into a Postgres table, the columns should have the same order. This post taught us several methods to import a CSV file to a PostgreSQL table using practical examples.