Multiline psql copy command

13 Nov 2021

This blog entry has a great tip on how to do multiline copy commands in psql!

I wanted to explore this further, so I'm going to get into more detail here.

First, let's make a test table and populate it with data:

# create temporary table t (
    i int,
    d text);
CREATE TABLE

# insert into t (i, d)
         values (1, 'one'),
                (2, 'two'),
                (3, 'three');
INSERT 0 3

What's really cool is that to terminate a command and send it to the server, you can use \g instead of a semicolon! (More details here.)

Behold:

# select * from t;
┌───┬───────┐
│ i │   d   │
├───┼───────┤
│ 1 │ one   │
│ 2 │ two   │
│ 3 │ three │
└───┴───────┘
(3 rows)

# select * from t \g
┌───┬───────┐
│ i │   d   │
├───┼───────┤
│ 1 │ one   │
│ 2 │ two   │
│ 3 │ three │
└───┴───────┘
(3 rows)

But unlike a semicolon, \g takes arguments. One argument is an output file!

Check this out:

# select * from t \g foo.txt
  [[ no output to psql; output goes to foo.txt ]]

# \q

$ cat foo.txt
┌───┬───────┐
│ i │   d   │
├───┼───────┤
│ 1 │ one   │
│ 2 │ two   │
│ 3 │ three │
└───┴───────┘
(3 rows)

Let's also remember that the SQL copy command is allowed to be multiline, whereas psql's \copy command has to all be on one line.

The SQL copy command has a downside in that any file it writes must be on the server, whereas psql's \copy streams the copy results to us locally where we can output to a file on our client machine. But the SQL copy command can use stdout as its "server-side" output file, making it stream the copy results to the client (psql) after all!

If we combine everything, we can have a multi-line SQL copy command output to stdout, which streams data to psql, and then use \g my_output_file.txt in psql to dump the results to my_output_file.txt.

So this:

# \copy (select i, d from t where i > 1) to /home/mwood/t.txt
COPY 2

ends up being the same as this:

# copy (
select i,
       d
  from t
 where i > 1) to stdout \g q.txt
COPY 2

except, of course, the second form allows us to use multiline formatting of our SQL statement!

Here is proof that the output files are the same:

$ cat t.txt
2       two
3       three

$ cat q.txt
2       two
3       three