Export PostgreSQL table to CSV with headers only if number of rows in table is more than 0

Question

I am exporting the table to CSV with header using COPY command in postgreSQL. Even if the table result is 0 it is creating CSV file only with header. I need CSV file with header only if number of rows is greater than 0.

Answer 1

you can just plpgsql it:

db=# do $$
begin
if (select count(*) from t) > 0 then
copy t to '/tmp/t' with (format csv, header);
end if;
end;
$$
;
DO
db=# \! cat /tmp/t
c
2
db=# truncate table t;
TRUNCATE TABLE
db=# \! rm /tmp/t
db=# do $$
begin
if (select count(*) from t) > 0 then
copy t to '/tmp/t' with (format csv, header);
end if;
end;
$$
;
DO
db=# \! cat /tmp/t
cat: /tmp/t: No such file or directory