Variable substitution in psql \copy

command-line, dynamic-sql, postgresql, psql, variable-substitution

Solution

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'

Problem

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 ```

Original source