Postgres Query copy to CSV with DateTime

Question

I have a simple query that i run inside a Postgres SQL Query that runs a bunch of queries then extracts it to CSV, which [without the bunch of queries above it] is

COPY (SELECT * FROM public.view_report_teamlist_usertable_wash) 
   TO 'd:/sf/Reports/view_report_teamlist_usertable_wash.csv' 
   DELIMITER ','
   CSV HEADER encoding 'UTF8' FORCE QUOTE *;

Can I alter the above at all to append the date/time [now()] to the filename? ie. 'd:/sf/Reports/view_report_teamlist_usertable_wash_2017-08-23 14:30:28.288912+10.csv'

I have googled it many times but only come up with solutions that runs it from a command line

You may need to write a pl/pgsql function for this: https://www.postgresql.org/message-id/492F2C5B.4010608%40iol.ie — moooeeeep, Aug 23 '17 at 06:45
Also have a look at this: https://stackoverflow.com/a/22963085/1025391 — moooeeeep, Aug 23 '17 at 06:49

score 1 · Answer 1 · answered Aug 24 '17 at 16:32

If you want to use it once or not regularly, you could use postgres DO. But, if you use the script regularly, you should write a PL.

Either way, it should be like this:

DO $$
DECLARE variable text;
BEGIN
variable := to_char(NOW(), 'YYYY-MM-DD_HH24:MI:SS');
EXECUTE format ('COPY (SELECT * FROM public.view_report_teamlist_usertable_wash) 
   TO ''d:/sf/Reports/view_report_teamlist_usertable_wash_%s.csv''
   DELIMITER '',''
   CSV HEADER encoding ''UTF8'' FORCE QUOTE *',--  // %s will be replaced by string variable
        variable -- File name
    );
END $$;
-- // NOTE the '' for escaping '

EDIT: DO runs inline with other queries.

Postgres Query copy to CSV with DateTime

1 Answers1