Variable substitution in psql \copy

Question

is possible in PSQL console export file with current date on the end of the file name? The name of the exported file should be like this table_20140710.csv is it possible to do this dynamically? - the format of the date can be different than the above it isn't so much important.

This is example what i mean:

\set curdate current_date
\copy (SELECT * FROM table) To 'C:/users/user/desktop/table_ ' || :curdate  || '.csv' WITH DELIMITER AS ';' CSV HEADER

Magnetograph

Answer 1

Dynamically build the \copy command and store it in a file. Then execute it with \i

First set tuples only output

\t

Set the output to a file

\o 'C:/users/user/desktop/copy_command.txt'

Build the \copy command

select format(
    $$\copy (select * from the_table) To 'C:/users/user/desktop/table_%s.csv' WITH DELIMITER AS ';' CSV HEADER$$
    , current_date
);

Restore the output to stdout

\o

Execute the generated command from the file

\i 'C:/users/user/desktop/copy_command.txt'

Ferrite

Answer 2

The exception of the \copy meta command not expanding variables is (meanwhile) documented

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.

To workaround you can build, store and execute the command in multiple steps (similar to the solution Clodoaldo Neto has given):

\set filename 'my fancy dynamic name'
\set command '\\copy (SELECT * FROM generate_series(1, 5)) to ' :'filename'
:command

With this, you need to double (escape) any \ and ' in the embedded meta command. Keep in mind that \set concatenates all further arguments to the second one, so quote spaces between the arguments. You can show the command before execution (:command) with \echo :command.

As an alternative to the local \set command, you could also build the command server side with SQL (the best way depends on where the dynamic content is originating):

SELECT '\copy (SELECT * FROM generate_series(1, 5)) to ''' || :'filename' || '''' AS command \gset

Shipping

Answer 3

An alternative to using the psql's \copy command suggested in the postgresql manual is the use of the serverside copy command piping the output to stdout.

Another way to obtain the same result as \copy ... to is to use the SQL COPY ... TO STDOUT command and terminate it with \g filename or \g |program. Unlike \copy, this method allows the command to span multiple lines; also, variable interpolation and backquote expansion can be used.

For the given example it would look like this:

COPY (SELECT * FROM table) TO STDOUT WITH DELIMITER AS ';' CSV HEADER \g 'table_':curdate'.csv'

Or, arguably, friendlier to the reader spread over multiple lines:

COPY (SELECT * FROM table) TO STDOUT 
WITH DELIMITER AS ';' CSV HEADER 
\g 'table_':curdate'.csv'

Bibb