In this article, we will see how to export PostgreSQL data to csv or excel file. COPY command is used to copy data between a file and a table in PostgreSQL.
Syntax to export data from a table to csv or excel or any other file formats:
COPY { table_name [ ( column_name [, ...] ) ] | ( query ) } TO { 'filename' | STDOUT } [ [ WITH ] ( option [, ...] ) ]
HEADER option specifies that the file contains a header line with the names of each column in the file. On output, the first line contains the column names from the table, and on input, the first line is ignored. This option is allowed only when using CSV format.
How to export PostgreSQL data to csv file examples:
1. Create directory and permissions to Postgres user.
mkdir -p /exports/train_copied
sudo chown postgres:postgres /exports/train_copied
2. Now, copy full table ’emp’ data to csv file in the above created path.
copy emp to '/exports/train_copied/test.csv' DELIMITER ',' CSV HEADER;
3. Now, goto the copy directory and run the command head -5 filename as show below:
4. If we want copy selected(query data)) data of table to csv file run the below command.
copy (select empno, ename, job from emp) TO '/exports/train/emp_query.csv' DELIMITER ',' CSV HEADER;
How to export PostgreSQL data to excel file examples:
1. Now, copy table ’emp’ data to csv file in the above created path.
copy emp to '/exports/train/train_emp.xls';
2. Now, goto the copy directory and run the command more filename as show below:
