PostgreSQL Export table .csv as current date

Question

I've need to export my table as .csv file, but the name has to be current date.

Currently I am using

COPY public."table" TO 'E:\job.csv' DELIMITER ',' CSV HEADER

What should I do? I tried to look for an answer, but without any luck...

Alex · Accepted Answer · 2018-05-08 19:32:18Z

1

You can always run dynamic SQL

DO $func$
BEGIN
  EXECUTE $$
    COPY public."table" TO 'E:\job_$$ || to_char(CURRENT_DATE, 'YYYY_MM_DD') || $$.csv' DELIMITER ',' CSV HEADER;
  $$;
END;
$func$ LANGUAGE plpgsql;

So you're forming the command as a string, and then passing it to EXECUTE. This works as plpgsql, that you can run either inline, as I've shown, or declared as a function:

CREATE OR REPLACE FUNCTION public.export_table()
RETURNS VOID AS $func$
BEGIN
  EXECUTE $$
    COPY public."table" TO 'E:\job_$$ || to_char(CURRENT_DATE, 'YYYY_MM_DD') || $$.csv' DELIMITER ',' CSV HEADER;
  $$;
END;
$func$ LANGUAGE plpgsql;

Then call it using SELECT public.export_table()

answered May 8, 2018 at 19:32

Alex

14.7k5 gold badges45 silver badges60 bronze badges

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

Collectives™ on Stack Overflow

PostgreSQL Export table .csv as current date

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related