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
# 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.
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
which streams data to psql, and then use
in psql to dump the results to
# \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