psql copy hack

fun scripts

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 \copy command.

problem

I wanted to use psql’s client-side \copy command in a script to export some data to a csv file. \copy was preferable here to the SQL COPY because \copy runs on the client and doesn’t require superuser permissions on the database.

The problem is that \copy has some limitations:

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 FORMAT function to build the \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 interpolation!

example

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

prosper

The result should be a csv file at /tmp/customers.csv generated by the SELECT.