1

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.

2 Answers 2

2

I know this is an older post, but I wanted to share what I have puzzled out as I was trying to achieve similar results and only found answers saying it had to be done via an anonymous function, pl/pgsql or similar. This can absolutely be done via plain sql either in psql directly or via a script passed to psql using psql variables. The one thing I cannot work out for you is the proper way to return a date from the OS as I do not have access to a Windows machine. On Linux at least, using a command enclosed in back quotes (`) will embed the results of the command run from the OS in the psql variable as shown below; perhaps there is an equivalent call in Windows that can be substituted and then the rest should work fine using the psql variable for the output path and filename.

\set filename /tmp/DBSizes_as_of_`date +"%Y-%m-%d_%H:%M:%S"`.csv

COPY (
       select datname
             ,pg_database_size(datname) as database_size
             ,pg_size_pretty(pg_database_size(datname)) as database_size_pretty
             ,now() as when
         from pg_database
     )
  TO :'filename'
WITH (
       FORMAT         csv
      ,OIDS           FALSE
      ,DELIMITER      ','
      ,HEADER         TRUE
      ,QUOTE          '"'
      ,ESCAPE         '"'
      ,ENCODING       'UTF8'
    )
;

Results shown from Linux command line are as follows:

# ls -la DBSizes*.csv
-rw-r--r--. 1 postgres postgres 332 Aug 10 17:19 DBSizes_as_of_2022-08-10_17:10:00.csv

# cat DBSizes*.csv
datname,database_size,database_size_pretty,when
postgres,8066535,7877 kB,2022-08-10 17:10:00.697735+00
template_gis,15652327,15 MB,2022-08-10 17:10:00.697735+00
template1,8926695,8717 kB,2022-08-10 17:10:00.697735+00
template0,8058343,7869 kB,2022-08-10 17:10:00.697735+00
redacted,652026762727,607 GB,2022-08-10 17:10:00.697735+00
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 and acknowledge you have read our privacy policy.

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