Postgres COPY with multiple small chunks In postgres, we can do COPY to export the data into file. But all the data will be saved into a file. But I want to chunk that file into small files. We can do this after the export.
But is there a way to export it during the COPY process?
OR in psycopg2 can we do this?
You can use a WHERE condition:
COPY (SELECT * FROM tab WHERE id % 3 = 0) TO '/dir/file1.csv' (FORMAT 'csv');
COPY (SELECT * FROM tab WHERE id % 3 = 1) TO '/dir/file2.csv' (FORMAT 'csv');
COPY (SELECT * FROM tab WHERE id % 3 = 2) TO '/dir/file3.csv' (FORMAT 'csv');

Here id stands for any numerical column. You can also use other data types and split up the data in other ways with an appropriate WHERE condition.