Export PostgreSQL Table to csv via Command Line

This article is focusing on showing how to export a single table from PostgreSQL database server. Exporting PostgreSQL table in this context of article means copying records exist in that table. The destination of the record copying process is a csv file. The process of copying those records is done using a command executed in a command line.

Off course there are several things need to be done as a prerequisite or preparation before the command for exporting records in a single table from PostgreSQL Database server can be carried out. Below are those steps :

1. Confirming the status of PostgreSQL Database Server whether it is currently active or not. Just check the following article titled ‘Check PostgreSQL Service Status’ in this link to check it how to do it.

2. Connecting to PostgreSQL Database Server by accessing its PostgreSQL Command Console. Use the correct username which has a privilege for connecting to the database where the table located. See the article titled ‘PostgreSQL Database Access from Command Line in Linux’ in this link for more information or in this link which is an article titled ‘Access PostgreSQL without Password’ for entering PostgreSQL Command Console without having to supply for any password.

3. After successfully entering PostgreSQL Command Console, try to execute the following command syntax to export or to copy all the records inside the table to a csv file :

copy mytable to '/tmp/myfile.csv' with (format csv, header);

Below is an example of the above command execution inside PostgreSQL Command Console :

[user@hostname ~]# psql -U myuser mydb
psql (9.4.5)
Type "help" for help.

mydb=# copy table_test to '/tmp/file-dump-record.csv' with (format csv, header);
COPY 2384
mydb=#

As it can be seen in the above directly after executing the command, it is displaying ‘COPY 2384’ which is representing the number of the record successfully copied. The result of the file will be in a csv file where every record is in a row and every column is separated by a comma. And furthermore, the first row will show the header or the name of the column associated with the value of the column in the next row.