Export to CSV from psql

2
min read

The Commands:

In order to export a table or query to csv use one of the following commands:

For Client-Side Export:

CODE:https://gist.github.com/fronofro/9e2b9189c931999b77f8d589a70ad6d3.js

For Server-Side Export:

CODE:https://gist.github.com/fronofro/ff5b192cd848ddee58e0fe08b7dcbab3.js

Example Relative Path: ‘Desktop/filename.csv’

Example Absolute Path: ‘/Users/matt/Desktop/filename.csv’

Key words at the end of the command:

  • csv: this tells the copy command that the file being created should be a CSV file.
  • header: this tells copy command to include the headers at the top of the document.

CSV Files

Comma Separated Value (CSV) files are a useful format for storing data. Many tools support importing data from CSV files because it is an easy to read format that is plain text and not metadata dependant.

In psql there are two commands that can do this, both slightly different.

The first is the \copy meta-command which is used to generate a client CSV file. This command takes the specified table or query results and writes them to the client’s computer.

The second command, COPY generates a CSV file on the server where the database is running.

The \copy command

The \copy meta-command is used for exporting to a client computer. It is useful for copying a database that may have somewhat restricted access and for creating a personal copy of the data. For example, a user may want to generate a csv so that they can analyse financial data in excel. The format of a \copy to csv is as follows:

CODE:https://gist.github.com/fronofro/9e2b9189c931999b77f8d589a70ad6d3.js

The [Table/Query] section can be filled with a table or query. For example to copy all entries from a table, the table name can be put here. To copy all entries that contain “saw” in their names from the table of tools to a csv, the following commands could be run:

The [Relative Path] is the path from where psql is currently saving files to where you want to save the file. The location that psql is currently saving can be found by using the \! pwd command.

Note: The \! meta-command takes whatever arguments it is given and runs them as a bash command within psql.

The pwd command prints the current working directory. The meta-command \! pwd and \! ls are shown being used below:


This means that if the file name “myTools.csv” is used as the [Relative Path], it will be saved in /Users/matt/ as can be seen below:

The file can also be saved elsewhere by entering a specific relative path. For example, if ‘/Desktop/[Filename].csv’ is entered as the path, the file will be saved to the desktop.

Following the Relative Path in the command is the text ‘csv header;’ This text does two things. The ‘csv’ specifies that the data should be stored in the CSV format. Other possible formats are ‘text’ and ‘Binary.’

The ‘header’ specifies that, when the data is copied to a csv file, the names of each column should be saved on the first line as shown here:

The COPY command

The COPY command also requires the user to specify a Table/Query to copy. Unlike \copy however, the COPY command requires an absolute path. This is because COPY is for copying a database from a server to another location on the same server; not to a client computer.

The \! pwd is very useful for finding the absolute path if you do not know where to save the file. In order to save to the desktop using \copy ‘Desktop/[Filename].csv’ would be used. In order to do this with COPY, ‘/Users/[Username]/Desktop/[Filename].csv’ would need to be used as shown below:

Copying Queries

Summary

  • To copy a table or query to a csv file, use either the \copy command or the COPY command.
  • \copy should be used for a copy to local systems
  • \copy uses a relative path
  • COPY should be used to create a csv on the server’s side.
  • COPY uses an absolute path.

Resources

  1. https://www.postgresql.org/docs/9.2/app-psql.html#APP-PSQL-META-COMMANDS-COPY
  2. https://www.postgresql.org/docs/9.2/sql-copy.html
  3. https://tableplus.io/blog/2018/04/postgresql-how-to-export-table-to-csv-file-with-header.html

Give Feedback on our Google Doc

Chapter topics