How to save query result to a file in PostgreSQL | PostgreSQL Tutorials and Solutions

In this article, we will see how to save query result to a file in PostgreSQL. We can save query result to txt, csv,.. files.

We have three methods to save/export query results to a file in PostgreSQL:

1) copy command
2) from Linux shell using psql tool.
3) \o method.

Method1: Using PostgreSQL copy command:

copy is PostgreSQL built-in command. To run copy command user must be superuser or a member of the pg_write_server_files role.

Examples:

1. Export/save query result of table student to txt file.

copy (select * from student) to '/var/lib/postgresql/out.txt';

How to save query result to a file in PostgreSQL

2. Export/save query result of table student to csv file.

copy (select * from student) to '/var/lib/postgresql/out.csv' With CSV DELIMITER ',' HEADER;

Method2: From Linux shell using psql tool.

We can also save/export query result from Linux shell using psql tool with below syntax:

psql -h dblocation -p port -U user -d dbname -F  -c "SELECT * FROM TABLE" > outfile.txt

Now we are going to export student table query result to student.txt file.

psql -h localhost -p 5432 -U james -d r2schools -c "select * from student" >student.txt

Method 3: Using \o:

With this method, we can run more than one query output to a single file.

Syntax:

db=#\o out.txt  --> Output file name
db=#query       --> Queries
db=#\o          --> ( set back the default output behavior ) or Turn off \o

Example:

r2schools=# \o /var/lib/postgresql/emp_table.txt
r2schools=# select * from emp;
r2schools=# \dt+ emp
r2schools=# \o

The above output contains select query and emp table information.