CSV(acronym of Comma Separated Values) is a format/standard supported by various apps, such as google sheets, MS Office, etc. The CSV format is used to save data in text files. The primary use case of CSV files is transferring data from one platform to another, such as exporting data to CSV files or importing data from CSV files. PostgreSQL allows us to import CSV files to Postgres tables or export Postgres tables to CSV files using SQL Shell (psql).

This post will demonstrate how to import or export CSV files to PostgreSQL via SQL Shell or psql.

Importing CSV Using SQL SHELL (psql)

The “\COPY” command is used with the collaboration of the “FROM” clause to import a CSV file to a Postgres table. The COPY command will copy all the data from the targeted file to a Postgres table.

Step 1: Sample CSV File

The below snippet shows the CSV file to be imported into a Postgres table:

Step 2: Sample Table

The following snippet shows the table’s structure in which we will import the CSV file:

Step 3: Import CSV File

Execute the “\COPY” command from SQL Shell to import a CSV file into a Postgres table:

\COPY employee_details(e_id, e_name, e_experience)
FROM 'C:\Users\DELL\Downloads\employee_data.csv'
DELIMITER ','
CSV HEADER;

The “COPY 14” message in the output demonstrates that 14 records have been imported to the “employees_details” table from the CSV file.

Step 4: Verify the Working of \COPY Command

You can verify it via the following command:

SELECT * FROM employees_details;

This way, you can import data from a CSV file to a PostgreSQL table via SQL SHELL.

Exporting CSV Using the SQL SHELL (psql)

In Postgres, the “\COPY” command is used with the collaboration of the “TO” clause to export a Postgres table to a CSV file using psql.

Step 1: Show Sample Table

The following snippet shows the table’s data that we want to export to the CSV file:

Step 2: Export the Table’s Data to CSV File

Execute the “\COPY” command with the “TO” clause to export a Postgres table to a CSV file:

\COPY employee_info(e_id, e_name, e_email)
TO 'C:\Users\DELL\Downloads\employee_data.csv'
DELIMITER ','
CSV HEADER;

The “COPY 6” message in the output demonstrates that six records have been exported to a CSV file named “employee_data.csv”.

Step 4: Verify the Working of \COPY Command

Let’s verify whether the data from the selected table has been exported to a CSV file. To do that, navigate to the directory/location where you exported the selected table:

A CSV file named “employee_data” has been exported to the specified location. Let’s open it to see its content:

The above snippet clarifies that the content from the “employee_info” table has been successfully exported to a CSV file named “employee_data”.

Conclusion

PostgreSQL allows us to import CSV files to Postgres tables or export Postgres tables to CSV files using SQL Shell (psql). The “\COPY” command is used with the collaboration of the “FROM” clause to import a CSV file to a Postgres table. In Postgres, the “\COPY” command is used with the collaboration of the “TO” clause to export a Postgres table to a CSV file using psql. This post demonstrated stepwise instructions for importing CSV to Postgres or exporting Postgres tables to CSV files using SQL Shell(psql).