Save PostgreSQL Result to a File

1. Overview

In PostgreSQL, we sometimes need to export data to a file for different purposes, such as analysis, reporting, sharing, or backups. To achieve this task, we have several possible approaches in PostgreSQL.

In this tutorial, we’ll discuss three primary approaches for saving query results to a file using the command line interface for PostgreSQL:

• the \o command
• command line redirection
• using COPY TO SQL

To demonstrate, we’ll use the Baeldung University database schema as a working example.

2. Using \o Command

To begin with, we can use the \o command to quickly redirect the output of psql to a file, followed by the specified path. Additionally, we run this command inside the psql terminal, making it instrumental for ad-hoc queries during interactive sessions. Of course, we should have the permission for the provided directory or path. Furthermore, if no path is specified, the file is created in the current working directory from which psql is launched.

For instance, let’s say we want to store SQL data in the file sql_result.txt. For this purpose, we first execute the \o command:

university=# \o sql_result.txt

Afterward, we can execute the query to retrieve data from the table. To illustrate, let’s store all records in the Student table:

SELECT * from Student;

Once we finish writing to the file, we reset the output back to the terminal by supplying no parameter to \o:

university=# \o

At this point, to confirm the output, let’s print the sql_result.txt file via the cat command:

$ cat /var/lib/postgresql/sql_result.txt
  id  |      name       | national_id | birth_date | enrollment_date | graduation_date | gpa  
------+-----------------+-------------+------------+-----------------+-----------------+------
 1001 | John Liu        |   123345566 | 2001-04-05 | 2020-01-15      | 2024-06-15      |    4
 1003 | Rita Ora        |   132345166 | 2001-01-14 | 2020-01-15      | 2024-06-15      |  4.2
 1007 | Philip Lose     |   321345566 | 2001-06-15 | 2020-01-15      | 2024-06-15      |  3.8
 1010 | Samantha Prabhu |  3217165566 | 2001-03-21 | 2020-01-15      | 2024-06-15      |  4.9
 1011 | Vikas Jain      |   321345662 | 2001-07-18 | 2020-01-15      |                 |  3.3
...
 1710 | Roni Roto       |  2678910631 | 2002-03-11 | 2021-01-15      | 2025-06-15      | 4.44
 1707 | Piu Liu         |  2101368101 | 2002-03-14 | 2021-01-15      |                 | 2.99
 1717 | Param Mohan     |  1023456545 | 2002-05-15 | 2021-01-15      | 2025-06-15      | 2.75
 1719 | Siren Lobo      |   189091342 | 2002-06-17 | 2021-01-15      |                 |     
 1721 | Vini Puh        |  1312091343 | 2002-05-13 | 2021-01-15      | 2025-06-15      | 3.64
(25 rows)

Consequently, we can see that all Student records are saved in the file sql_result.txt.

3. Using Command Line Redirection

As another option, we can run a query from the shell and use the redirection operator > to send the output to a file. This method is handy for automating tasks or working outside the psql shell.

First, let’s take a look at the syntax query for this redirection approach:

$ psql -d database_name -c "SELECT_QUERY" > file_path

In this command, we input the three parameters:

• database_name
• SELECT_QUERY
• file_path

For example, to export all rows from the Course table in the university database, we can run psql:

$ psql -d university -c "SELECT * from Course;" > /var/lib/postgresql/course_data.txt

Thus, we saved all Course data to the specified path: /var/lib/postgresql/course_data.txt.

4. Using COPY TO SQL

Lastly, we can use the COPY TO SQL operation, which enables us to export an entire table or query result directly to a file. This is useful for generating structured files such as CSVs or exporting large datasets.

First, let’s take a look at the syntax of the command:

COPY table_name TO 'file_path' DELIMITER ',' CSV HEADER;

In this syntax, we specify the required parameters:

• table_name
• file_path

For instance, let’s export the content of the Department to the res.csv file:

COPY Department TO '/var/lib/postgresql/res.csv' DELIMITER ',' CSV HEADER;

After this, we can see that all data rows from the Department table are copied to the file path /var/lib/postgresql/res.csv.

5. Conclusion

In this article, we learned how to save a PostgreSQL result to a file using three different techniques.

First, we saw the \o command, useful for quick export during interactive sessions. After that, we understood command line redirection as an approach that helps with automating tasks from the terminal. Finally, we discussed COPY TO SQL for efficiently exporting data in a structured form, like CSV files.