How to export table as CSV with headings on Postgresql?

In PostgreSQL, you can use the built-in COPY command to export table data to CSV format, including column headers. Below, I will provide a detailed explanation of the steps and commands.

Step 1: Open the PostgreSQL Command-Line Tool

First, log in to the PostgreSQL database using the psql command-line tool, which is a terminal client for PostgreSQL.

bash
psql -U username -d database_name

Here, username is your database username, and database_name is the name of the database you are working with.

Step 2: Use the COPY Command

In the psql command-line interface, use the COPY command to export table data to a CSV file. To include column headers, specify the HEADER option.

sql
COPY table_name TO '/path/to/your/folder/filename.csv' DELIMITER ',' CSV HEADER;

Here, table_name is the name of the table you want to export, and /path/to/your/folder/filename.csv is the path and filename where you want to save the CSV file.

• DELIMITER ',' specifies that fields are separated by commas.
• CSV indicates the output format should be CSV.
• HEADER is a critical option that ensures the CSV file includes column headers as the first line.

Notes

1. Ensure you have sufficient permissions to execute the COPY command. If not, you may need assistance from a database administrator.
2. The file path must be accessible by the database server. If using a remote server, verify the path is valid on the server.
3. For large tables, the COPY command may take time to execute; consider performance and network bandwidth impacts during execution.

Example

Assume there is a table named employees that you want to export to /home/user/data/employees.csv. The command is:

sql
COPY employees TO '/home/user/data/employees.csv' DELIMITER ',' CSV HEADER;

This command creates a CSV file containing all data from the employees table, with column headers as the first line.

By following these steps, you can easily export table data from PostgreSQL to a CSV file with headers, which is suitable for data analysis, reporting, or any other use case requiring table data.