CODEWITHSUNDEEP
postgresql
Adam Trigg
Adam Trigg

Reputation: 53

Postgres Query copy to CSV with DateTime

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

Upvotes: 0

Views: 1471

Answers (1)

Dan
Dan

Reputation: 1881

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.

Upvotes: 1

Related Questions