PostgreSQL: How to Export SQL Result to File

Table of Contents

I will introduce you several way to output sql result into a file with using psql.

Environment

I checked in the following environment.

psql 9.6.4
PostgreSQL server 9.3.1

In psql Executing

Here are the ways to set output file in psql.

Output Once

I use this method frequently.

First, execute the sql you want to do. Next, write g and output file, like g /tmp/output.txt, end press enter. Then, the result will be written into the file.

If you execute only g, the SQL will be re-executed and the result will be shown on the console, standard output.

SELECT 1;
g /tmp/output.txt

1 2	SELECT 1; g /tmp/output.txt

Output to File Forever

With this method, the result will be output into the file forever, such as you see in the console. New result will be added to the file, time to time.

Type as o /tmp/output.txt. Then output is set to file, so the result isn’t shown on the console.

o /tmp/output.txt
SELECT 1;

1 2	o /tmp/output.txt SELECT 1;

If you want to write output into the file and see on the console, type o | cat > /tmp/output.txt 2>&1.

o | cat > /tmp/output.txt 2>&1
SELECT 1;

1 2	o \| cat > /tmp/output.txt 2>&1 SELECT 1;

If you type only o and press enter, the standard output is set.

COPY

Use COPY or copy to output to the file. With this method, you can output the result as CSV or TSV or other format.

Example

Copy (Select * From something) To '/tmp/test.csv' With CSV DELIMITER ',';
copy (Select * From something) To '/tmp/output.csv' With CSV

1 2	Copy (Select * From something) To '/tmp/test.csv' With CSV DELIMITER ','; copy (Select * From something) To '/tmp/output.csv' With CSV

Second example has no semi-colon. Meta command beginning ends with newline character.

Look at PostgreSQL document for other options.

In Shell

You can execute SQL in shell with command psql and redirect it to file.

psql -c 'SELECT * FROM something;'

1	psql -c 'SELECT * FROM something;'

The script passed with option -c will be executed, so you can write COPY command in it.

The Life