0

I have a query and want the output in a .csv file with today's date and time in the name (I am using PostgreSQL v12).

COPY 
(SELECT * FROM mytable)
TO 'C:\Users\me\mylocation\filename_date_time.csv' CSV HEADER

where date_time is YYYY-MM-DD-h_m_s (or similar)

I have seen some solutions to this using bash. For various reasons I am on a Windows machine with no bash.

I would like to do this using psql please.

0

You have to use dynamic SQL, for example with a DO statement:

DO
$$DECLARE
   datestr text := to_char(current_timestamp, 'YYYY-MM-DD-HH24_MI_SS');
BEGIN
   EXECUTE format(
              'COPY '
              '(SELECT * FROM mytable) '
              'TO %L CSV HEADER',
              'C:\Users\me\mylocation\' || datestr || '.csv'
           );
END;$$;

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.