If you have used Oracle databases, you are probably familiar with the SPOOL command. In Oracle, you can use the SPOOL command to save query output (to file) that gets generated in SQLPLUS.

Postgres has the ‘psql’ command line tool that is equivalent to Oracle’s SQLPLUS.
In Postgres, you can redirect query output to a file by using the ‘\o’ command on the ‘psql’

prompt. Here is an example, create an input file named print_table_data_to_file.sql
and type the following in it:

\o /my_dir/output/query_result.out

SELECT table_name AS "Tables in abc schema"
FROM   information_schema.tables
WHERE table_schema = 'abc'
ORDER BY table_name;

\qecho '****************************************************************'
\qecho '|'
\qecho '| abc.table1'
\qecho '|'
\qecho '****************************************************************'
\qecho ''

SELECT * FROM abc.table1;

\qecho '****************************************************************'
\qecho '|'
\qecho '| abc.table2'
\qecho '|'
\qecho '****************************************************************'
\qecho ''

SELECT * FROM abc.table2;

\o

The first ‘\o /my_dir/output/query_result.out‘ command tells ‘psql’ to send all subsequent output to the file /my_dir/output/query_result.out

The last ‘\o’ sets the output back to the default (standard out).

You finally execute the content of print_table_data_to_file.sql file like this:

    psql -U user1 -h host1 -d my_db -f print_table_data_to_file.sql

And this will write all the query result to the file query_result.out