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

Answer 1

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) the \ 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

Answer 2

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'