PostgreSQL (or Postgres) is an object-relational database management system similar to MySQL but supports enhanced functionality and stability. One excellent feature is that you can export a Postgres table to a .CSV file. This can be especially helpful when transferring a table to a different system or importing it to another database application.
In this tutorial, you will learn how to export a Postgres table to a .CSV file using the
- PostgreSQL installed on the system
- An existing database in PostgreSQL
- A terminal window / command line (Ctrl+Alt+T)
Before You Begin with PostgreSQL
Postgres can be installed on Windows, Mac, Linux, and it can even be deployed inside a Docker container. This guide walks you through the procedure using Ubuntu Linux. However, all export commands can be used in any other supported operating system.
If you don’t have Postgres, you can install it by downloading the software from the developer’s website. Install Postgres on Ubuntu from the default repositories by entering the following:
sudo apt-get install postgresql
Once the service starts, you need to create or import a database.
Note: .CSV files are useful for a couple of reasons. First, you can open and read them by any text editor, without a tool that reads metadata. Second, they are versatile, and most database programs can import a .CSV file.
Export Data from Table to .CSV with COPY Command
In psql there are two different commands.
The basic usage of the
<strong>COPY</strong> command is as follows:
COPY db_name TO [/path/to/destination/db_name.csv] DELIMITER ‘,’ CSV HEADER;
<strong>db_name</strong> with the actual name of your database and the
<strong>/path/to/destination</strong> with the actual location you want to store the .CSV file in.
For example, in Windows, you might want to save a copy to C:\tmp\db_name.csv. In Linux, the default path could be /tmp/db_name.csv. Then, you can open the .CSV file and see the content of the table listed in a slightly different format. You can see the difference in the example below.
First, we list the content of a specified table in the psql shell with the command:
SELECT * FROM [table_name]
The output displays the values inside our example table as in the image below:
Now you can compare it to its corresponding .CSV file. The data exported from the previously mentioned table appears in a .CSV file, as seen in the following image:
Export Specific Columns from Table to .CSV
You can use the
<strong>COPY</strong> command to target specific columns:
COPY [db_name](column1,column2,column3) TO [/path/to/destination/db_name_partial.csv] DELIMITER ‘,’ CSV HEADER;
Note: You can omit column names by omitting the
HEADER command. Also,
COPY uses an absolute path. You need to specify the full location where you want to save the .CSV file.
Export Data from Table to .CSV with \copy Command
<strong>\copy</strong> command when working remotely. It allows you to export data from a server to a .CSV file on a local client machine. Use it as follows:
\copy (SELECT * FROM [db_name]) to [/path/to/destination] CSV HEADER
<strong>db_name</strong> with the name of the source database. Replace
<strong>/path/to/destination</strong> with the actual location for the .CSV file. In Windows, you might export to C:tmpdb_name.csv. In Linux, you might export to /tmp/db_name.csv.
<strong>\copy</strong> command only requires elevated privileges for the client system. This is helpful if you don’t have sufficient privileges to run the
COPY command on the server. Also, the
<strong>\copy</strong> command allows you to use a relative path.
For example, you could specify desktop/db_name.csv on a Windows system, and it would save to the current user’s desktop.
You should now be able to export PostgreSQL tables to .CSV using the
COPY commands. You can now import the .CSV to another computer system or database-management tool.