I recently wrote a bunch of Makefiles and
psql scripts for a data
migration job and learned a cool trick along the way.
This shows how I used the string
FORMAT function to get SQL interpolation into a
I wanted to use
in a script to export some data to a csv file.
\copy was preferable here to
\copy runs on the client and doesn’t require superuser
permissions on the database.
The problem is that
\copy has some limitations:
no variable expansion
the full query argument to
\copymust fit on 1 line
From the docs:
Unlike most other meta-commands, the entire remainder of the line is always taken to be the arguments of \copy, and neither variable interpolation nor backquote expansion are performed in the arguments.
If I want to use all my nice DRY variables I can’t use
\copy. And if I want to
pass a query to copy, the query has to get shoved onto a single line! That’s bad
because all but the simplest queries on 1 line are a pain to read, share, and maintain.
FORMAT to the rescue
The trick is to use the
function to build
\copy query and then write the result to a file. Then the file can be
invoked as a command itself and hey presto there’s a copy command with variable
a little contrived
--- example variable \set my_table 'customers' \set output_file '/tmp/customers.csv' --- set tuple only output to that the formatted \copy command we build --- doesnt have the default column and table formatting which would --- prevent it from executing \t --- here's the copy command we want to run but cannot because --- limitation on variable substitution: --- \copy --- select c.cid, c.name, c.account_group --- from :my_table --- to :'output_file' CSV HEADER --- --- We use FORMAT to build it. select format( '\copy select c.cid, c.name, c.account_group from %s to %s CSV HEADER', :'my_table', :'output_file' ); --- save the result to a file /tmp/copy_command.sql \g '/tmp/copy_command.sql' --- execute the file \i /tmp/copy_command.sql
The result should be a csv file at /tmp/customers.csv generated by the SELECT.