On 20th of October 2025, Masahiko Sawada committed patch:
Support COPY TO for partitioned tables. Previously, COPY TO command didn't support directly specifying partitioned tables so users had to use COPY (SELECT ...) TO variant. This commit adds direct COPY TO support for partitioned tables, improving both usability and performance. Performance tests show it's faster than the COPY (SELECT ...) TO variant as it avoids the overheads of query processing and sending results to the COPY TO command. When used with partitioned tables, COPY TO copies the same rows as SELECT * FROM table. Row-level security policies of the partitioned table are applied in the same way as when executing COPY TO on a plain table. Author: jian he <jian.universality@gmail.com> Reviewed-by: vignesh C <vignesh21@gmail.com> Reviewed-by: David Rowley <dgrowleyml@gmail.com> Reviewed-by: Melih Mutlu <m.melihmutlu@gmail.com> Reviewed-by: Kirill Reshke <reshkekirill@gmail.com> Reviewed-by: Atsushi Torikoshi <torikoshia@oss.nttdata.com> Reviewed-by: Álvaro Herrera <alvherre@kurilemu.de> Reviewed-by: Masahiko Sawada <sawada.mshk@gmail.com> Reviewed-by: Chao Li <li.evan.chao@gmail.com> Discussion: https://postgr.es/m/CACJufxEZt%2BG19Ors3bQUq-42-61__C%3Dy5k2wk%3DsHEFRusu7%3DiQ%40mail.gmail.com
Great. Another limitation removed.
So, previously one couldn't easily COPY partitioned tables somewhere:
=$ copy plans to '/dev/null'; ERROR: cannot copy from partitioned table "plans" HINT: Try the COPY (SELECT ...) TO variant.
I could have done it partition at a time:
=$ copy plans_part_13 to '/dev/null'; COPY 37022 Time: 1797.280 ms (00:01.797)
I could do the thing suggested in HINT:
=$ copy (select * from plans) to '/dev/null'; COPY 1861681 Time: 86868.842 ms (01:26.869)
But now, thanks to this new patch, we can actually copy data out of partitioned tables, without need for any workarounds. And it's should be a bit faster than copy from select. To test I ran:
- copy plans to ‘/dev/null';
- copy (select * from plans) to ‘/dev/null';
each three times, and considered best time only. Copy directly from table was taking, in my case, 45890.169ms. Copy from select, surprisingly, 45436.088ms. So ~ 400ms less.
I think I can blame random fluctuations on this, or perhaps non-standard data distribution – the plans table has only 1.8 million rows, but is spread across 50 partitions, with total size of ~ 7.5GB.
Anyway, this looks like great addition, so thanks a lot to everyone involved.