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: postgresql.org/message-id/492F2C5B.4010608%40iol.ie — moooeeeep
– moooeeeep, Commented Aug 23, 2017 at 6:45
Also have a look at this: stackoverflow.com/a/22963085/1025391 — moooeeeep
– moooeeeep, Commented Aug 23, 2017 at 6:49

Dan · Accepted Answer · 2017-08-24 16:32:22Z

1

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.

answered Aug 24, 2017 at 16:32

Dan

1,9211 gold badge16 silver badges20 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

Postgres Query copy to CSV with DateTime

1 Answer 1

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related