speedup COPY TO for partitioned table.

From eaf3869c4fb5fdacba5efd562f73ca06a0251ac4 Mon Sep 17 00:00:00 2001
From: jian he <jian.universality@gmail.com>
Date: Fri, 7 Mar 2025 18:39:56 +0800
Subject: [PATCH v2 1/1] support "COPY partitioned_table TO"

drop table if exists pp;
CREATE TABLE pp (id  INT, val int ) PARTITION BY RANGE (id);
create table pp_1 (val int, id int);
create table pp_2 (val int, id int);
ALTER TABLE pp ATTACH PARTITION pp_1 FOR VALUES FROM (1) TO (5);
ALTER TABLE pp ATTACH PARTITION pp_2 FOR VALUES FROM (5) TO (10);
insert into pp select g, 10 + g from generate_series(1,9) g;
copy pp to stdout(header);

the above case is much slower (around 25% some case) than
``COPY (select * from pp) to stdout(header);``
but this is still a new feature, since master does not
support ``COPY (partitioned_table)``.

discussion: https://postgr.es/m/CACJufxEZt+G19Ors3bQUq-42-61__C=y5k2wk=sHEFRusu7=iQ@mail.gmail.com
---
 src/backend/commands/copyto.c       | 80 ++++++++++++++++++++++++++---
 src/test/regress/expected/copy2.out | 16 ++++++
 src/test/regress/sql/copy2.sql      | 11 ++++
 3 files changed, 101 insertions(+), 6 deletions(-)

diff --git a/src/backend/commands/copyto.c b/src/backend/commands/copyto.c
index 84a3f3879a8..966b6741530 100644
--- a/src/backend/commands/copyto.c
+++ b/src/backend/commands/copyto.c
@@ -19,6 +19,8 @@
 #include <sys/stat.h>
 
 #include "access/tableam.h"
+#include "access/table.h"
+#include "catalog/pg_inherits.h"
 #include "commands/copyapi.h"
 #include "commands/progress.h"
 #include "executor/execdesc.h"
@@ -82,6 +84,7 @@ typedef struct CopyToStateData
 	List	   *attnumlist;		/* integer list of attnums to copy */
 	char	   *filename;		/* filename, or NULL for STDOUT */
 	bool		is_program;		/* is 'filename' a program to popen? */
+	List	   *partitions;		/* oid list of partition oid for copy to */
 	copy_data_dest_cb data_dest_cb; /* function for writing data */
 
 	CopyFormatOptions opts;
@@ -643,6 +646,8 @@ BeginCopyTo(ParseState *pstate,
 		PROGRESS_COPY_COMMAND_TO,
 		0
 	};
+	List	   *children = NIL;
+	List	   *scan_oids = NIL;
 
 	if (rel != NULL && rel->rd_rel->relkind != RELKIND_RELATION)
 	{
@@ -670,11 +675,19 @@ BeginCopyTo(ParseState *pstate,
 					 errmsg("cannot copy from sequence \"%s\"",
 							RelationGetRelationName(rel))));
 		else if (rel->rd_rel->relkind == RELKIND_PARTITIONED_TABLE)
-			ereport(ERROR,
-					(errcode(ERRCODE_WRONG_OBJECT_TYPE),
-					 errmsg("cannot copy from partitioned table \"%s\"",
-							RelationGetRelationName(rel)),
-					 errhint("Try the COPY (SELECT ...) TO variant.")));
+		{
+			children = find_all_inheritors(RelationGetRelid(rel),
+										   AccessShareLock,
+										   NULL);
+			foreach_oid(childreloid, children)
+			{
+				char		relkind = get_rel_relkind(childreloid);
+				if (RELKIND_HAS_PARTITIONS(relkind))
+					continue;
+
+				scan_oids = lappend_oid(scan_oids, childreloid);
+			}
+		}
 		else
 			ereport(ERROR,
 					(errcode(ERRCODE_WRONG_OBJECT_TYPE),
@@ -710,6 +723,7 @@ BeginCopyTo(ParseState *pstate,
 		cstate->rel = rel;
 
 		tupDesc = RelationGetDescr(cstate->rel);
+		cstate->partitions = list_copy(scan_oids);
 	}
 	else
 	{
@@ -1066,7 +1080,61 @@ DoCopyTo(CopyToState cstate)
 
 	cstate->routine->CopyToStart(cstate, tupDesc);
 
-	if (cstate->rel)
+	/*
+	 * if COPY TO source table is a partitioned table, then open each
+	 * partition and process each individual partition.
+	 */
+	if (cstate->rel && cstate->rel->rd_rel->relkind == RELKIND_PARTITIONED_TABLE)
+	{
+		processed = 0;
+
+		foreach_oid(scan_oid, cstate->partitions)
+		{
+			TupleTableSlot *slot;
+			TableScanDesc scandesc;
+			Relation		scan_rel;
+			TupleDesc		scan_tupdesc;
+			AttrMap    		*map;
+			TupleTableSlot  *root_slot = NULL;
+			TupleTableSlot  *original_slot = NULL;
+
+			scan_rel = table_open(scan_oid, AccessShareLock);
+			scan_tupdesc = RelationGetDescr(scan_rel);
+			map = build_attrmap_by_name_if_req(tupDesc, scan_tupdesc, false);
+
+			scandesc = table_beginscan(scan_rel, GetActiveSnapshot(), 0, NULL);
+			slot = table_slot_create(scan_rel, NULL);
+
+			while (table_scan_getnextslot(scandesc, ForwardScanDirection, slot))
+			{
+				CHECK_FOR_INTERRUPTS();
+
+				/* Deconstruct the tuple ... */
+				if (map != NULL)
+				{
+					original_slot = slot;
+					root_slot = MakeSingleTupleTableSlot(tupDesc, &TTSOpsBufferHeapTuple);
+					slot = execute_attr_map_slot(map, slot, root_slot);
+				}
+				else
+					slot_getallattrs(slot);
+
+				/* Format and send the data */
+				CopyOneRowTo(cstate, slot);
+
+				pgstat_progress_update_param(PROGRESS_COPY_TUPLES_PROCESSED,
+											 ++processed);
+
+				if (original_slot != NULL)
+					ExecDropSingleTupleTableSlot(original_slot);
+			};
+
+			ExecDropSingleTupleTableSlot(slot);
+			table_endscan(scandesc);
+			table_close(scan_rel, AccessShareLock);
+		}
+	}
+	else if (cstate->rel)
 	{
 		TupleTableSlot *slot;
 		TableScanDesc scandesc;
diff --git a/src/test/regress/expected/copy2.out b/src/test/regress/expected/copy2.out
index 64ea33aeae8..dcd97ae45b7 100644
--- a/src/test/regress/expected/copy2.out
+++ b/src/test/regress/expected/copy2.out
@@ -929,3 +929,19 @@ truncate copy_default;
 -- DEFAULT cannot be used in COPY TO
 copy (select 1 as test) TO stdout with (default '\D');
 ERROR:  COPY DEFAULT cannot be used with COPY TO
+-- COPY TO with partitioned table
+CREATE TABLE pp (id  INT, val int ) PARTITION BY RANGE (id);
+create table pp_1 (val int, id int);
+create table pp_2 (val int, id int);
+ALTER TABLE pp ATTACH PARTITION pp_1 FOR VALUES FROM (1) TO (3);
+ALTER TABLE pp ATTACH PARTITION pp_2 FOR VALUES FROM (3) TO (7);
+insert into pp select g, 10 + g from generate_series(1,6) g;
+copy pp to stdout(header);
+id	val
+1	11
+2	12
+3	13
+4	14
+5	15
+6	16
+DROP TABLE PP;
diff --git a/src/test/regress/sql/copy2.sql b/src/test/regress/sql/copy2.sql
index 45273557ce0..56d7c1ffc8f 100644
--- a/src/test/regress/sql/copy2.sql
+++ b/src/test/regress/sql/copy2.sql
@@ -707,3 +707,14 @@ truncate copy_default;
 
 -- DEFAULT cannot be used in COPY TO
 copy (select 1 as test) TO stdout with (default '\D');
+
+-- COPY TO with partitioned table
+CREATE TABLE pp (id  INT, val int ) PARTITION BY RANGE (id);
+create table pp_1 (val int, id int);
+create table pp_2 (val int, id int);
+ALTER TABLE pp ATTACH PARTITION pp_1 FOR VALUES FROM (1) TO (3);
+ALTER TABLE pp ATTACH PARTITION pp_2 FOR VALUES FROM (3) TO (7);
+insert into pp select g, 10 + g from generate_series(1,6) g;
+copy pp to stdout(header);
+
+DROP TABLE PP;
\ No newline at end of file
-- 
2.34.1

From f7376da47f51e385c5496b0cf7eb52e5340a39b9 Mon Sep 17 00:00:00 2001
From: jian he <jian.universality@gmail.com>
Date: Tue, 11 Mar 2025 20:51:30 +0800
Subject: [PATCH v3 1/1] support "COPY partitioned_table TO"

drop table if exists pp;
CREATE TABLE pp (id  INT, val int ) PARTITION BY RANGE (id);
create table pp_1 (val int, id int);
create table pp_2 (val int, id int);
ALTER TABLE pp ATTACH PARTITION pp_1 FOR VALUES FROM (1) TO (5);
ALTER TABLE pp ATTACH PARTITION pp_2 FOR VALUES FROM (5) TO (10);
insert into pp select g, 10 + g from generate_series(1,9) g;
copy pp to stdout(header);

the above case is much slower (around 25% some case) than
``COPY (select * from pp) to stdout(header);``
but this is still a new feature, since master does not
support ``COPY (partitioned_table)``.

discussion: https://postgr.es/m/CACJufxEZt+G19Ors3bQUq-42-61__C=y5k2wk=sHEFRusu7=iQ@mail.gmail.com
---
 doc/src/sgml/ref/copy.sgml          |  8 +--
 src/backend/commands/copyto.c       | 80 ++++++++++++++++++++++++++---
 src/test/regress/expected/copy2.out | 16 ++++++
 src/test/regress/sql/copy2.sql      | 11 ++++
 4 files changed, 105 insertions(+), 10 deletions(-)

diff --git a/doc/src/sgml/ref/copy.sgml b/doc/src/sgml/ref/copy.sgml
index df093da97c5..f86e0b7ec35 100644
--- a/doc/src/sgml/ref/copy.sgml
+++ b/doc/src/sgml/ref/copy.sgml
@@ -521,15 +521,15 @@ COPY <replaceable class="parameter">count</replaceable>
 
    <para>
     <command>COPY TO</command> can be used only with plain
-    tables, not views, and does not copy rows from child tables
-    or child partitions.  For example, <literal>COPY <replaceable
+    tables, not views, and does not copy rows from child tables,
+    however <command>COPY TO</command> can be used with partitioned tables.
+    For example, in a table inheritance hierarchy, <literal>COPY <replaceable
     class="parameter">table</replaceable> TO</literal> copies
     the same rows as <literal>SELECT * FROM ONLY <replaceable
     class="parameter">table</replaceable></literal>.
     The syntax <literal>COPY (SELECT * FROM <replaceable
     class="parameter">table</replaceable>) TO ...</literal> can be used to
-    dump all of the rows in an inheritance hierarchy, partitioned table,
-    or view.
+    dump all of the rows in an inheritance hierarchy, or view.
    </para>
 
    <para>
diff --git a/src/backend/commands/copyto.c b/src/backend/commands/copyto.c
index 84a3f3879a8..966b6741530 100644
--- a/src/backend/commands/copyto.c
+++ b/src/backend/commands/copyto.c
@@ -19,6 +19,8 @@
 #include <sys/stat.h>
 
 #include "access/tableam.h"
+#include "access/table.h"
+#include "catalog/pg_inherits.h"
 #include "commands/copyapi.h"
 #include "commands/progress.h"
 #include "executor/execdesc.h"
@@ -82,6 +84,7 @@ typedef struct CopyToStateData
 	List	   *attnumlist;		/* integer list of attnums to copy */
 	char	   *filename;		/* filename, or NULL for STDOUT */
 	bool		is_program;		/* is 'filename' a program to popen? */
+	List	   *partitions;		/* oid list of partition oid for copy to */
 	copy_data_dest_cb data_dest_cb; /* function for writing data */
 
 	CopyFormatOptions opts;
@@ -643,6 +646,8 @@ BeginCopyTo(ParseState *pstate,
 		PROGRESS_COPY_COMMAND_TO,
 		0
 	};
+	List	   *children = NIL;
+	List	   *scan_oids = NIL;
 
 	if (rel != NULL && rel->rd_rel->relkind != RELKIND_RELATION)
 	{
@@ -670,11 +675,19 @@ BeginCopyTo(ParseState *pstate,
 					 errmsg("cannot copy from sequence \"%s\"",
 							RelationGetRelationName(rel))));
 		else if (rel->rd_rel->relkind == RELKIND_PARTITIONED_TABLE)
-			ereport(ERROR,
-					(errcode(ERRCODE_WRONG_OBJECT_TYPE),
-					 errmsg("cannot copy from partitioned table \"%s\"",
-							RelationGetRelationName(rel)),
-					 errhint("Try the COPY (SELECT ...) TO variant.")));
+		{
+			children = find_all_inheritors(RelationGetRelid(rel),
+										   AccessShareLock,
+										   NULL);
+			foreach_oid(childreloid, children)
+			{
+				char		relkind = get_rel_relkind(childreloid);
+				if (RELKIND_HAS_PARTITIONS(relkind))
+					continue;
+
+				scan_oids = lappend_oid(scan_oids, childreloid);
+			}
+		}
 		else
 			ereport(ERROR,
 					(errcode(ERRCODE_WRONG_OBJECT_TYPE),
@@ -710,6 +723,7 @@ BeginCopyTo(ParseState *pstate,
 		cstate->rel = rel;
 
 		tupDesc = RelationGetDescr(cstate->rel);
+		cstate->partitions = list_copy(scan_oids);
 	}
 	else
 	{
@@ -1066,7 +1080,61 @@ DoCopyTo(CopyToState cstate)
 
 	cstate->routine->CopyToStart(cstate, tupDesc);
 
-	if (cstate->rel)
+	/*
+	 * if COPY TO source table is a partitioned table, then open each
+	 * partition and process each individual partition.
+	 */
+	if (cstate->rel && cstate->rel->rd_rel->relkind == RELKIND_PARTITIONED_TABLE)
+	{
+		processed = 0;
+
+		foreach_oid(scan_oid, cstate->partitions)
+		{
+			TupleTableSlot *slot;
+			TableScanDesc scandesc;
+			Relation		scan_rel;
+			TupleDesc		scan_tupdesc;
+			AttrMap    		*map;
+			TupleTableSlot  *root_slot = NULL;
+			TupleTableSlot  *original_slot = NULL;
+
+			scan_rel = table_open(scan_oid, AccessShareLock);
+			scan_tupdesc = RelationGetDescr(scan_rel);
+			map = build_attrmap_by_name_if_req(tupDesc, scan_tupdesc, false);
+
+			scandesc = table_beginscan(scan_rel, GetActiveSnapshot(), 0, NULL);
+			slot = table_slot_create(scan_rel, NULL);
+
+			while (table_scan_getnextslot(scandesc, ForwardScanDirection, slot))
+			{
+				CHECK_FOR_INTERRUPTS();
+
+				/* Deconstruct the tuple ... */
+				if (map != NULL)
+				{
+					original_slot = slot;
+					root_slot = MakeSingleTupleTableSlot(tupDesc, &TTSOpsBufferHeapTuple);
+					slot = execute_attr_map_slot(map, slot, root_slot);
+				}
+				else
+					slot_getallattrs(slot);
+
+				/* Format and send the data */
+				CopyOneRowTo(cstate, slot);
+
+				pgstat_progress_update_param(PROGRESS_COPY_TUPLES_PROCESSED,
+											 ++processed);
+
+				if (original_slot != NULL)
+					ExecDropSingleTupleTableSlot(original_slot);
+			};
+
+			ExecDropSingleTupleTableSlot(slot);
+			table_endscan(scandesc);
+			table_close(scan_rel, AccessShareLock);
+		}
+	}
+	else if (cstate->rel)
 	{
 		TupleTableSlot *slot;
 		TableScanDesc scandesc;
diff --git a/src/test/regress/expected/copy2.out b/src/test/regress/expected/copy2.out
index 64ea33aeae8..dcd97ae45b7 100644
--- a/src/test/regress/expected/copy2.out
+++ b/src/test/regress/expected/copy2.out
@@ -929,3 +929,19 @@ truncate copy_default;
 -- DEFAULT cannot be used in COPY TO
 copy (select 1 as test) TO stdout with (default '\D');
 ERROR:  COPY DEFAULT cannot be used with COPY TO
+-- COPY TO with partitioned table
+CREATE TABLE pp (id  INT, val int ) PARTITION BY RANGE (id);
+create table pp_1 (val int, id int);
+create table pp_2 (val int, id int);
+ALTER TABLE pp ATTACH PARTITION pp_1 FOR VALUES FROM (1) TO (3);
+ALTER TABLE pp ATTACH PARTITION pp_2 FOR VALUES FROM (3) TO (7);
+insert into pp select g, 10 + g from generate_series(1,6) g;
+copy pp to stdout(header);
+id	val
+1	11
+2	12
+3	13
+4	14
+5	15
+6	16
+DROP TABLE PP;
diff --git a/src/test/regress/sql/copy2.sql b/src/test/regress/sql/copy2.sql
index 45273557ce0..56d7c1ffc8f 100644
--- a/src/test/regress/sql/copy2.sql
+++ b/src/test/regress/sql/copy2.sql
@@ -707,3 +707,14 @@ truncate copy_default;
 
 -- DEFAULT cannot be used in COPY TO
 copy (select 1 as test) TO stdout with (default '\D');
+
+-- COPY TO with partitioned table
+CREATE TABLE pp (id  INT, val int ) PARTITION BY RANGE (id);
+create table pp_1 (val int, id int);
+create table pp_2 (val int, id int);
+ALTER TABLE pp ATTACH PARTITION pp_1 FOR VALUES FROM (1) TO (3);
+ALTER TABLE pp ATTACH PARTITION pp_2 FOR VALUES FROM (3) TO (7);
+insert into pp select g, 10 + g from generate_series(1,6) g;
+copy pp to stdout(header);
+
+DROP TABLE PP;
\ No newline at end of file
-- 
2.34.1

From a2db87abfe0e1a4dda0ace47c65a9778f29fe5f2 Mon Sep 17 00:00:00 2001
From: jian he <jian.universality@gmail.com>
Date: Fri, 28 Mar 2025 11:01:52 +0800
Subject: [PATCH v4 1/1] support COPY partitioned_table TO

CREATE TABLE pp (id  INT, val int ) PARTITION BY RANGE (id);
create table pp_1 (val int, id int);
create table pp_2 (val int, id int);
ALTER TABLE pp ATTACH PARTITION pp_1 FOR VALUES FROM (1) TO (5);
ALTER TABLE pp ATTACH PARTITION pp_2 FOR VALUES FROM (5) TO (10);
insert into pp select g, 10 + g from generate_series(1,9) g;
copy pp to stdout(header);

the above case is much slower (around 25% some case) than
``COPY (select * from pp) to stdout(header);``,
because of column remaping.  but this is still a new
feature, since master does not support ``COPY (partitioned_table)``.

reivewed by: vignesh C <vignesh21@gmail.com>
reivewed by: David Rowley <dgrowleyml@gmail.com>
reivewed by: Melih Mutlu <m.melihmutlu@gmail.com>

discussion: https://postgr.es/m/CACJufxEZt+G19Ors3bQUq-42-61__C=y5k2wk=sHEFRusu7=iQ@mail.gmail.com
commitfest entry: https://commitfest.postgresql.org/patch/5467/
---
 doc/src/sgml/ref/copy.sgml          |  8 +--
 src/backend/commands/copyto.c       | 89 +++++++++++++++++++++++++++--
 src/test/regress/expected/copy2.out | 16 ++++++
 src/test/regress/sql/copy2.sql      | 11 ++++
 4 files changed, 114 insertions(+), 10 deletions(-)

diff --git a/doc/src/sgml/ref/copy.sgml b/doc/src/sgml/ref/copy.sgml
index df093da97c5..f86e0b7ec35 100644
--- a/doc/src/sgml/ref/copy.sgml
+++ b/doc/src/sgml/ref/copy.sgml
@@ -521,15 +521,15 @@ COPY <replaceable class="parameter">count</replaceable>
 
    <para>
     <command>COPY TO</command> can be used only with plain
-    tables, not views, and does not copy rows from child tables
-    or child partitions.  For example, <literal>COPY <replaceable
+    tables, not views, and does not copy rows from child tables,
+    however <command>COPY TO</command> can be used with partitioned tables.
+    For example, in a table inheritance hierarchy, <literal>COPY <replaceable
     class="parameter">table</replaceable> TO</literal> copies
     the same rows as <literal>SELECT * FROM ONLY <replaceable
     class="parameter">table</replaceable></literal>.
     The syntax <literal>COPY (SELECT * FROM <replaceable
     class="parameter">table</replaceable>) TO ...</literal> can be used to
-    dump all of the rows in an inheritance hierarchy, partitioned table,
-    or view.
+    dump all of the rows in an inheritance hierarchy, or view.
    </para>
 
    <para>
diff --git a/src/backend/commands/copyto.c b/src/backend/commands/copyto.c
index 84a3f3879a8..0973dc9c14b 100644
--- a/src/backend/commands/copyto.c
+++ b/src/backend/commands/copyto.c
@@ -19,6 +19,8 @@
 #include <sys/stat.h>
 
 #include "access/tableam.h"
+#include "access/table.h"
+#include "catalog/pg_inherits.h"
 #include "commands/copyapi.h"
 #include "commands/progress.h"
 #include "executor/execdesc.h"
@@ -82,6 +84,7 @@ typedef struct CopyToStateData
 	List	   *attnumlist;		/* integer list of attnums to copy */
 	char	   *filename;		/* filename, or NULL for STDOUT */
 	bool		is_program;		/* is 'filename' a program to popen? */
+	List	   *partitions;		/* oid list of partition oid for copy to */
 	copy_data_dest_cb data_dest_cb; /* function for writing data */
 
 	CopyFormatOptions opts;
@@ -643,6 +646,8 @@ BeginCopyTo(ParseState *pstate,
 		PROGRESS_COPY_COMMAND_TO,
 		0
 	};
+	List	   *children = NIL;
+	List	   *scan_oids = NIL;
 
 	if (rel != NULL && rel->rd_rel->relkind != RELKIND_RELATION)
 	{
@@ -670,11 +675,28 @@ BeginCopyTo(ParseState *pstate,
 					 errmsg("cannot copy from sequence \"%s\"",
 							RelationGetRelationName(rel))));
 		else if (rel->rd_rel->relkind == RELKIND_PARTITIONED_TABLE)
-			ereport(ERROR,
-					(errcode(ERRCODE_WRONG_OBJECT_TYPE),
-					 errmsg("cannot copy from partitioned table \"%s\"",
-							RelationGetRelationName(rel)),
-					 errhint("Try the COPY (SELECT ...) TO variant.")));
+		{
+			children = find_all_inheritors(RelationGetRelid(rel),
+										   AccessShareLock,
+										   NULL);
+			foreach_oid(childreloid, children)
+			{
+				char		 relkind = get_rel_relkind(childreloid);
+
+				if (relkind == RELKIND_FOREIGN_TABLE)
+					ereport(ERROR,
+							errcode(ERRCODE_WRONG_OBJECT_TYPE),
+							errmsg("cannot copy from foreign table \"%s\"",
+									RelationGetRelationName(rel)),
+							errdetail("partition \"%s\" is a foreign table", RelationGetRelationName(rel)),
+							errhint("Try the COPY (SELECT ...) TO variant."));
+
+				if (RELKIND_HAS_PARTITIONS(relkind))
+					continue;
+
+				scan_oids = lappend_oid(scan_oids, childreloid);
+			}
+		}
 		else
 			ereport(ERROR,
 					(errcode(ERRCODE_WRONG_OBJECT_TYPE),
@@ -710,6 +732,7 @@ BeginCopyTo(ParseState *pstate,
 		cstate->rel = rel;
 
 		tupDesc = RelationGetDescr(cstate->rel);
+		cstate->partitions = list_copy(scan_oids);
 	}
 	else
 	{
@@ -1066,7 +1089,61 @@ DoCopyTo(CopyToState cstate)
 
 	cstate->routine->CopyToStart(cstate, tupDesc);
 
-	if (cstate->rel)
+	/*
+	 * if COPY TO source table is a partitioned table, then open each
+	 * partition and process each individual partition.
+	 */
+	if (cstate->rel && cstate->rel->rd_rel->relkind == RELKIND_PARTITIONED_TABLE)
+	{
+		processed = 0;
+
+		foreach_oid(scan_oid, cstate->partitions)
+		{
+			TupleTableSlot *slot;
+			TableScanDesc scandesc;
+			Relation		scan_rel;
+			TupleDesc		scan_tupdesc;
+			AttrMap    		*map;
+			TupleTableSlot  *root_slot = NULL;
+			TupleTableSlot  *original_slot = NULL;
+
+			scan_rel = table_open(scan_oid, AccessShareLock);
+			scan_tupdesc = RelationGetDescr(scan_rel);
+			map = build_attrmap_by_name_if_req(tupDesc, scan_tupdesc, false);
+
+			scandesc = table_beginscan(scan_rel, GetActiveSnapshot(), 0, NULL);
+			slot = table_slot_create(scan_rel, NULL);
+
+			while (table_scan_getnextslot(scandesc, ForwardScanDirection, slot))
+			{
+				CHECK_FOR_INTERRUPTS();
+
+				/* Deconstruct the tuple ... */
+				if (map != NULL)
+				{
+					original_slot = slot;
+					root_slot = MakeSingleTupleTableSlot(tupDesc, &TTSOpsBufferHeapTuple);
+					slot = execute_attr_map_slot(map, slot, root_slot);
+				}
+				else
+					slot_getallattrs(slot);
+
+				/* Format and send the data */
+				CopyOneRowTo(cstate, slot);
+
+				pgstat_progress_update_param(PROGRESS_COPY_TUPLES_PROCESSED,
+											 ++processed);
+
+				if (original_slot != NULL)
+					ExecDropSingleTupleTableSlot(original_slot);
+			};
+
+			ExecDropSingleTupleTableSlot(slot);
+			table_endscan(scandesc);
+			table_close(scan_rel, AccessShareLock);
+		}
+	}
+	else if (cstate->rel)
 	{
 		TupleTableSlot *slot;
 		TableScanDesc scandesc;
diff --git a/src/test/regress/expected/copy2.out b/src/test/regress/expected/copy2.out
index 64ea33aeae8..dcd97ae45b7 100644
--- a/src/test/regress/expected/copy2.out
+++ b/src/test/regress/expected/copy2.out
@@ -929,3 +929,19 @@ truncate copy_default;
 -- DEFAULT cannot be used in COPY TO
 copy (select 1 as test) TO stdout with (default '\D');
 ERROR:  COPY DEFAULT cannot be used with COPY TO
+-- COPY TO with partitioned table
+CREATE TABLE pp (id  INT, val int ) PARTITION BY RANGE (id);
+create table pp_1 (val int, id int);
+create table pp_2 (val int, id int);
+ALTER TABLE pp ATTACH PARTITION pp_1 FOR VALUES FROM (1) TO (3);
+ALTER TABLE pp ATTACH PARTITION pp_2 FOR VALUES FROM (3) TO (7);
+insert into pp select g, 10 + g from generate_series(1,6) g;
+copy pp to stdout(header);
+id	val
+1	11
+2	12
+3	13
+4	14
+5	15
+6	16
+DROP TABLE PP;
diff --git a/src/test/regress/sql/copy2.sql b/src/test/regress/sql/copy2.sql
index 45273557ce0..56d7c1ffc8f 100644
--- a/src/test/regress/sql/copy2.sql
+++ b/src/test/regress/sql/copy2.sql
@@ -707,3 +707,14 @@ truncate copy_default;
 
 -- DEFAULT cannot be used in COPY TO
 copy (select 1 as test) TO stdout with (default '\D');
+
+-- COPY TO with partitioned table
+CREATE TABLE pp (id  INT, val int ) PARTITION BY RANGE (id);
+create table pp_1 (val int, id int);
+create table pp_2 (val int, id int);
+ALTER TABLE pp ATTACH PARTITION pp_1 FOR VALUES FROM (1) TO (3);
+ALTER TABLE pp ATTACH PARTITION pp_2 FOR VALUES FROM (3) TO (7);
+insert into pp select g, 10 + g from generate_series(1,6) g;
+copy pp to stdout(header);
+
+DROP TABLE PP;
\ No newline at end of file
-- 
2.34.1

From a4c643ac3a9f40bbdf07dcacc38527ef6e86f1bc Mon Sep 17 00:00:00 2001
From: jian he <jian.universality@gmail.com>
Date: Fri, 28 Mar 2025 11:05:53 +0800
Subject: [PATCH v5 1/1] support COPY partitioned_table TO

CREATE TABLE pp (id  INT, val int ) PARTITION BY RANGE (id);
create table pp_1 (val int, id int);
create table pp_2 (val int, id int);
ALTER TABLE pp ATTACH PARTITION pp_1 FOR VALUES FROM (1) TO (5);
ALTER TABLE pp ATTACH PARTITION pp_2 FOR VALUES FROM (5) TO (10);
insert into pp select g, 10 + g from generate_series(1,9) g;
copy pp to stdout(header);

the above case is much slower (around 25% some case) than
``COPY (select * from pp) to stdout(header);``,
because of column remaping.  but this is still a new
feature, since master does not support ``COPY (partitioned_table)``.

reivewed by: vignesh C <vignesh21@gmail.com>
reivewed by: David Rowley <dgrowleyml@gmail.com>
reivewed by: Melih Mutlu <m.melihmutlu@gmail.com>

discussion: https://postgr.es/m/CACJufxEZt+G19Ors3bQUq-42-61__C=y5k2wk=sHEFRusu7=iQ@mail.gmail.com
commitfest entry: https://commitfest.postgresql.org/patch/5467/
---
 doc/src/sgml/ref/copy.sgml          |  8 +--
 src/backend/commands/copyto.c       | 89 +++++++++++++++++++++++++++--
 src/test/regress/expected/copy2.out | 16 ++++++
 src/test/regress/sql/copy2.sql      | 11 ++++
 4 files changed, 114 insertions(+), 10 deletions(-)

diff --git a/doc/src/sgml/ref/copy.sgml b/doc/src/sgml/ref/copy.sgml
index df093da97c5..f86e0b7ec35 100644
--- a/doc/src/sgml/ref/copy.sgml
+++ b/doc/src/sgml/ref/copy.sgml
@@ -521,15 +521,15 @@ COPY <replaceable class="parameter">count</replaceable>
 
    <para>
     <command>COPY TO</command> can be used only with plain
-    tables, not views, and does not copy rows from child tables
-    or child partitions.  For example, <literal>COPY <replaceable
+    tables, not views, and does not copy rows from child tables,
+    however <command>COPY TO</command> can be used with partitioned tables.
+    For example, in a table inheritance hierarchy, <literal>COPY <replaceable
     class="parameter">table</replaceable> TO</literal> copies
     the same rows as <literal>SELECT * FROM ONLY <replaceable
     class="parameter">table</replaceable></literal>.
     The syntax <literal>COPY (SELECT * FROM <replaceable
     class="parameter">table</replaceable>) TO ...</literal> can be used to
-    dump all of the rows in an inheritance hierarchy, partitioned table,
-    or view.
+    dump all of the rows in an inheritance hierarchy, or view.
    </para>
 
    <para>
diff --git a/src/backend/commands/copyto.c b/src/backend/commands/copyto.c
index 84a3f3879a8..0973dc9c14b 100644
--- a/src/backend/commands/copyto.c
+++ b/src/backend/commands/copyto.c
@@ -19,6 +19,8 @@
 #include <sys/stat.h>
 
 #include "access/tableam.h"
+#include "access/table.h"
+#include "catalog/pg_inherits.h"
 #include "commands/copyapi.h"
 #include "commands/progress.h"
 #include "executor/execdesc.h"
@@ -82,6 +84,7 @@ typedef struct CopyToStateData
 	List	   *attnumlist;		/* integer list of attnums to copy */
 	char	   *filename;		/* filename, or NULL for STDOUT */
 	bool		is_program;		/* is 'filename' a program to popen? */
+	List	   *partitions;		/* oid list of partition oid for copy to */
 	copy_data_dest_cb data_dest_cb; /* function for writing data */
 
 	CopyFormatOptions opts;
@@ -643,6 +646,8 @@ BeginCopyTo(ParseState *pstate,
 		PROGRESS_COPY_COMMAND_TO,
 		0
 	};
+	List	   *children = NIL;
+	List	   *scan_oids = NIL;
 
 	if (rel != NULL && rel->rd_rel->relkind != RELKIND_RELATION)
 	{
@@ -670,11 +675,28 @@ BeginCopyTo(ParseState *pstate,
 					 errmsg("cannot copy from sequence \"%s\"",
 							RelationGetRelationName(rel))));
 		else if (rel->rd_rel->relkind == RELKIND_PARTITIONED_TABLE)
-			ereport(ERROR,
-					(errcode(ERRCODE_WRONG_OBJECT_TYPE),
-					 errmsg("cannot copy from partitioned table \"%s\"",
-							RelationGetRelationName(rel)),
-					 errhint("Try the COPY (SELECT ...) TO variant.")));
+		{
+			children = find_all_inheritors(RelationGetRelid(rel),
+										   AccessShareLock,
+										   NULL);
+			foreach_oid(childreloid, children)
+			{
+				char		 relkind = get_rel_relkind(childreloid);
+
+				if (relkind == RELKIND_FOREIGN_TABLE)
+					ereport(ERROR,
+							errcode(ERRCODE_WRONG_OBJECT_TYPE),
+							errmsg("cannot copy from foreign table \"%s\"",
+									RelationGetRelationName(rel)),
+							errdetail("partition \"%s\" is a foreign table", RelationGetRelationName(rel)),
+							errhint("Try the COPY (SELECT ...) TO variant."));
+
+				if (RELKIND_HAS_PARTITIONS(relkind))
+					continue;
+
+				scan_oids = lappend_oid(scan_oids, childreloid);
+			}
+		}
 		else
 			ereport(ERROR,
 					(errcode(ERRCODE_WRONG_OBJECT_TYPE),
@@ -710,6 +732,7 @@ BeginCopyTo(ParseState *pstate,
 		cstate->rel = rel;
 
 		tupDesc = RelationGetDescr(cstate->rel);
+		cstate->partitions = list_copy(scan_oids);
 	}
 	else
 	{
@@ -1066,7 +1089,61 @@ DoCopyTo(CopyToState cstate)
 
 	cstate->routine->CopyToStart(cstate, tupDesc);
 
-	if (cstate->rel)
+	/*
+	 * if COPY TO source table is a partitioned table, then open each
+	 * partition and process each individual partition.
+	 */
+	if (cstate->rel && cstate->rel->rd_rel->relkind == RELKIND_PARTITIONED_TABLE)
+	{
+		processed = 0;
+
+		foreach_oid(scan_oid, cstate->partitions)
+		{
+			TupleTableSlot *slot;
+			TableScanDesc scandesc;
+			Relation		scan_rel;
+			TupleDesc		scan_tupdesc;
+			AttrMap    		*map;
+			TupleTableSlot  *root_slot = NULL;
+			TupleTableSlot  *original_slot = NULL;
+
+			scan_rel = table_open(scan_oid, AccessShareLock);
+			scan_tupdesc = RelationGetDescr(scan_rel);
+			map = build_attrmap_by_name_if_req(tupDesc, scan_tupdesc, false);
+
+			scandesc = table_beginscan(scan_rel, GetActiveSnapshot(), 0, NULL);
+			slot = table_slot_create(scan_rel, NULL);
+
+			while (table_scan_getnextslot(scandesc, ForwardScanDirection, slot))
+			{
+				CHECK_FOR_INTERRUPTS();
+
+				/* Deconstruct the tuple ... */
+				if (map != NULL)
+				{
+					original_slot = slot;
+					root_slot = MakeSingleTupleTableSlot(tupDesc, &TTSOpsBufferHeapTuple);
+					slot = execute_attr_map_slot(map, slot, root_slot);
+				}
+				else
+					slot_getallattrs(slot);
+
+				/* Format and send the data */
+				CopyOneRowTo(cstate, slot);
+
+				pgstat_progress_update_param(PROGRESS_COPY_TUPLES_PROCESSED,
+											 ++processed);
+
+				if (original_slot != NULL)
+					ExecDropSingleTupleTableSlot(original_slot);
+			};
+
+			ExecDropSingleTupleTableSlot(slot);
+			table_endscan(scandesc);
+			table_close(scan_rel, AccessShareLock);
+		}
+	}
+	else if (cstate->rel)
 	{
 		TupleTableSlot *slot;
 		TableScanDesc scandesc;
diff --git a/src/test/regress/expected/copy2.out b/src/test/regress/expected/copy2.out
index 64ea33aeae8..dcd97ae45b7 100644
--- a/src/test/regress/expected/copy2.out
+++ b/src/test/regress/expected/copy2.out
@@ -929,3 +929,19 @@ truncate copy_default;
 -- DEFAULT cannot be used in COPY TO
 copy (select 1 as test) TO stdout with (default '\D');
 ERROR:  COPY DEFAULT cannot be used with COPY TO
+-- COPY TO with partitioned table
+CREATE TABLE pp (id  INT, val int ) PARTITION BY RANGE (id);
+create table pp_1 (val int, id int);
+create table pp_2 (val int, id int);
+ALTER TABLE pp ATTACH PARTITION pp_1 FOR VALUES FROM (1) TO (3);
+ALTER TABLE pp ATTACH PARTITION pp_2 FOR VALUES FROM (3) TO (7);
+insert into pp select g, 10 + g from generate_series(1,6) g;
+copy pp to stdout(header);
+id	val
+1	11
+2	12
+3	13
+4	14
+5	15
+6	16
+DROP TABLE PP;
diff --git a/src/test/regress/sql/copy2.sql b/src/test/regress/sql/copy2.sql
index 45273557ce0..ba984388248 100644
--- a/src/test/regress/sql/copy2.sql
+++ b/src/test/regress/sql/copy2.sql
@@ -707,3 +707,14 @@ truncate copy_default;
 
 -- DEFAULT cannot be used in COPY TO
 copy (select 1 as test) TO stdout with (default '\D');
+
+-- COPY TO with partitioned table
+CREATE TABLE pp (id  INT, val int ) PARTITION BY RANGE (id);
+create table pp_1 (val int, id int);
+create table pp_2 (val int, id int);
+ALTER TABLE pp ATTACH PARTITION pp_1 FOR VALUES FROM (1) TO (3);
+ALTER TABLE pp ATTACH PARTITION pp_2 FOR VALUES FROM (3) TO (7);
+insert into pp select g, 10 + g from generate_series(1,6) g;
+copy pp to stdout(header);
+
+DROP TABLE PP;
-- 
2.34.1

From 3036d31163ffea4c0a605d9411bc46af3b1b6394 Mon Sep 17 00:00:00 2001
From: jian he <jian.universality@gmail.com>
Date: Sat, 29 Mar 2025 14:32:30 +0800
Subject: [PATCH v6 1/1] support COPY partitioned_table TO

CREATE TABLE pp (id  INT, val int ) PARTITION BY RANGE (id);
create table pp_1 (val int, id int);
create table pp_2 (val int, id int);
ALTER TABLE pp ATTACH PARTITION pp_1 FOR VALUES FROM (1) TO (5);
ALTER TABLE pp ATTACH PARTITION pp_2 FOR VALUES FROM (5) TO (10);
insert into pp select g, 10 + g from generate_series(1,9) g;
copy pp to stdout(header);

the above case is much slower (around 25% some case) than
``COPY (select * from pp) to stdout(header);``,
because of column remaping.  but this is still a new
feature, since master does not support ``COPY (partitioned_table)``.

reivewed by: vignesh C <vignesh21@gmail.com>
reivewed by: David Rowley <dgrowleyml@gmail.com>
reivewed by: Melih Mutlu <m.melihmutlu@gmail.com>

discussion: https://postgr.es/m/CACJufxEZt+G19Ors3bQUq-42-61__C=y5k2wk=sHEFRusu7=iQ@mail.gmail.com
commitfest entry: https://commitfest.postgresql.org/patch/5467/
---
 doc/src/sgml/ref/copy.sgml          |   8 +-
 src/backend/commands/copyto.c       | 135 ++++++++++++++++++++++------
 src/test/regress/expected/copy2.out |  16 ++++
 src/test/regress/sql/copy2.sql      |  11 +++
 4 files changed, 139 insertions(+), 31 deletions(-)

diff --git a/doc/src/sgml/ref/copy.sgml b/doc/src/sgml/ref/copy.sgml
index df093da97c5..f86e0b7ec35 100644
--- a/doc/src/sgml/ref/copy.sgml
+++ b/doc/src/sgml/ref/copy.sgml
@@ -521,15 +521,15 @@ COPY <replaceable class="parameter">count</replaceable>
 
    <para>
     <command>COPY TO</command> can be used only with plain
-    tables, not views, and does not copy rows from child tables
-    or child partitions.  For example, <literal>COPY <replaceable
+    tables, not views, and does not copy rows from child tables,
+    however <command>COPY TO</command> can be used with partitioned tables.
+    For example, in a table inheritance hierarchy, <literal>COPY <replaceable
     class="parameter">table</replaceable> TO</literal> copies
     the same rows as <literal>SELECT * FROM ONLY <replaceable
     class="parameter">table</replaceable></literal>.
     The syntax <literal>COPY (SELECT * FROM <replaceable
     class="parameter">table</replaceable>) TO ...</literal> can be used to
-    dump all of the rows in an inheritance hierarchy, partitioned table,
-    or view.
+    dump all of the rows in an inheritance hierarchy, or view.
    </para>
 
    <para>
diff --git a/src/backend/commands/copyto.c b/src/backend/commands/copyto.c
index 84a3f3879a8..facf87eb344 100644
--- a/src/backend/commands/copyto.c
+++ b/src/backend/commands/copyto.c
@@ -19,6 +19,8 @@
 #include <sys/stat.h>
 
 #include "access/tableam.h"
+#include "access/table.h"
+#include "catalog/pg_inherits.h"
 #include "commands/copyapi.h"
 #include "commands/progress.h"
 #include "executor/execdesc.h"
@@ -82,6 +84,7 @@ typedef struct CopyToStateData
 	List	   *attnumlist;		/* integer list of attnums to copy */
 	char	   *filename;		/* filename, or NULL for STDOUT */
 	bool		is_program;		/* is 'filename' a program to popen? */
+	List	   *partitions;		/* oid list of partition oid for copy to */
 	copy_data_dest_cb data_dest_cb; /* function for writing data */
 
 	CopyFormatOptions opts;
@@ -116,6 +119,8 @@ static void CopyOneRowTo(CopyToState cstate, TupleTableSlot *slot);
 static void CopyAttributeOutText(CopyToState cstate, const char *string);
 static void CopyAttributeOutCSV(CopyToState cstate, const char *string,
 								bool use_quote);
+static void CopyThisRelTo(CopyToState cstate, Relation rel,
+						  Relation root_rel, uint64 *processed);
 
 /* built-in format-specific routines */
 static void CopyToTextLikeStart(CopyToState cstate, TupleDesc tupDesc);
@@ -643,6 +648,8 @@ BeginCopyTo(ParseState *pstate,
 		PROGRESS_COPY_COMMAND_TO,
 		0
 	};
+	List	   *children = NIL;
+	List	   *scan_oids = NIL;
 
 	if (rel != NULL && rel->rd_rel->relkind != RELKIND_RELATION)
 	{
@@ -670,11 +677,28 @@ BeginCopyTo(ParseState *pstate,
 					 errmsg("cannot copy from sequence \"%s\"",
 							RelationGetRelationName(rel))));
 		else if (rel->rd_rel->relkind == RELKIND_PARTITIONED_TABLE)
-			ereport(ERROR,
-					(errcode(ERRCODE_WRONG_OBJECT_TYPE),
-					 errmsg("cannot copy from partitioned table \"%s\"",
-							RelationGetRelationName(rel)),
-					 errhint("Try the COPY (SELECT ...) TO variant.")));
+		{
+			children = find_all_inheritors(RelationGetRelid(rel),
+										   AccessShareLock,
+										   NULL);
+			foreach_oid(childreloid, children)
+			{
+				char		 relkind = get_rel_relkind(childreloid);
+
+				if (relkind == RELKIND_FOREIGN_TABLE)
+					ereport(ERROR,
+							errcode(ERRCODE_WRONG_OBJECT_TYPE),
+							errmsg("cannot copy from foreign table \"%s\"",
+									RelationGetRelationName(rel)),
+							errdetail("partition \"%s\" is a foreign table", RelationGetRelationName(rel)),
+							errhint("Try the COPY (SELECT ...) TO variant."));
+
+				if (RELKIND_HAS_PARTITIONS(relkind))
+					continue;
+
+				scan_oids = lappend_oid(scan_oids, childreloid);
+			}
+		}
 		else
 			ereport(ERROR,
 					(errcode(ERRCODE_WRONG_OBJECT_TYPE),
@@ -710,6 +734,7 @@ BeginCopyTo(ParseState *pstate,
 		cstate->rel = rel;
 
 		tupDesc = RelationGetDescr(cstate->rel);
+		cstate->partitions = list_copy(scan_oids);
 	}
 	else
 	{
@@ -1066,35 +1091,28 @@ DoCopyTo(CopyToState cstate)
 
 	cstate->routine->CopyToStart(cstate, tupDesc);
 
-	if (cstate->rel)
+	/*
+	 * if COPY TO source table is a partitioned table, then open each
+	 * partition and process each individual partition.
+	 */
+	if (cstate->rel && cstate->rel->rd_rel->relkind == RELKIND_PARTITIONED_TABLE)
 	{
-		TupleTableSlot *slot;
-		TableScanDesc scandesc;
-
-		scandesc = table_beginscan(cstate->rel, GetActiveSnapshot(), 0, NULL);
-		slot = table_slot_create(cstate->rel, NULL);
-
 		processed = 0;
-		while (table_scan_getnextslot(scandesc, ForwardScanDirection, slot))
+		foreach_oid(scan_oid, cstate->partitions)
 		{
-			CHECK_FOR_INTERRUPTS();
+			Relation		scan_rel;
 
-			/* Deconstruct the tuple ... */
-			slot_getallattrs(slot);
+			scan_rel = table_open(scan_oid, AccessShareLock);
 
-			/* Format and send the data */
-			CopyOneRowTo(cstate, slot);
+			CopyThisRelTo(cstate, scan_rel, cstate->rel, &processed);
 
-			/*
-			 * Increment the number of processed tuples, and report the
-			 * progress.
-			 */
-			pgstat_progress_update_param(PROGRESS_COPY_TUPLES_PROCESSED,
-										 ++processed);
+			table_close(scan_rel, AccessShareLock);
 		}
-
-		ExecDropSingleTupleTableSlot(slot);
-		table_endscan(scandesc);
+	}
+	else if (cstate->rel)
+	{
+		processed = 0;
+		CopyThisRelTo(cstate, cstate->rel, NULL, &processed);
 	}
 	else
 	{
@@ -1113,6 +1131,69 @@ DoCopyTo(CopyToState cstate)
 	return processed;
 }
 
+/*
+ * rel: the relation to be copied to.
+ * root_rel: if not null, then the COPY TO partitioned rel.
+ * processed: number of tuple processed.
+*/
+static void
+CopyThisRelTo(CopyToState cstate, Relation rel, Relation root_rel, uint64 *processed)
+{
+	TupleTableSlot *slot;
+	TableScanDesc scandesc;
+	AttrMap    		*map	= NULL;
+	TupleTableSlot  *root_slot = NULL;
+	TupleTableSlot  *original_slot = NULL;
+	TupleDesc		scan_tupdesc;
+	TupleDesc 		rootdesc	= NULL;
+
+	scan_tupdesc = RelationGetDescr(rel);
+	scandesc = table_beginscan(rel, GetActiveSnapshot(), 0, NULL);
+	slot = table_slot_create(rel, NULL);
+
+	/*
+	 * partition's rowtype might differ from the root table's.  We must
+	 * convert it back to the root table's rowtype as we are export
+	 * partitioned table data here.
+	*/
+	if (root_rel != NULL)
+	{
+		rootdesc = RelationGetDescr(root_rel);
+		map = build_attrmap_by_name_if_req(rootdesc, scan_tupdesc, false);
+	}
+
+	while (table_scan_getnextslot(scandesc, ForwardScanDirection, slot))
+	{
+		CHECK_FOR_INTERRUPTS();
+
+		/* Deconstruct the tuple ... */
+		if (map != NULL)
+		{
+			original_slot = slot;
+			root_slot = MakeSingleTupleTableSlot(rootdesc, &TTSOpsBufferHeapTuple);
+			slot = execute_attr_map_slot(map, slot, root_slot);
+		}
+		else
+			slot_getallattrs(slot);
+
+		/* Format and send the data */
+		CopyOneRowTo(cstate, slot);
+
+		/*
+		 * Increment the number of processed tuples, and report the
+		 * progress.
+		 */
+		pgstat_progress_update_param(PROGRESS_COPY_TUPLES_PROCESSED,
+									 ++(*processed));
+
+		if (original_slot != NULL)
+			ExecDropSingleTupleTableSlot(original_slot);
+	}
+
+	ExecDropSingleTupleTableSlot(slot);
+	table_endscan(scandesc);
+}
+
 /*
  * Emit one row during DoCopyTo().
  */
diff --git a/src/test/regress/expected/copy2.out b/src/test/regress/expected/copy2.out
index 64ea33aeae8..dcd97ae45b7 100644
--- a/src/test/regress/expected/copy2.out
+++ b/src/test/regress/expected/copy2.out
@@ -929,3 +929,19 @@ truncate copy_default;
 -- DEFAULT cannot be used in COPY TO
 copy (select 1 as test) TO stdout with (default '\D');
 ERROR:  COPY DEFAULT cannot be used with COPY TO
+-- COPY TO with partitioned table
+CREATE TABLE pp (id  INT, val int ) PARTITION BY RANGE (id);
+create table pp_1 (val int, id int);
+create table pp_2 (val int, id int);
+ALTER TABLE pp ATTACH PARTITION pp_1 FOR VALUES FROM (1) TO (3);
+ALTER TABLE pp ATTACH PARTITION pp_2 FOR VALUES FROM (3) TO (7);
+insert into pp select g, 10 + g from generate_series(1,6) g;
+copy pp to stdout(header);
+id	val
+1	11
+2	12
+3	13
+4	14
+5	15
+6	16
+DROP TABLE PP;
diff --git a/src/test/regress/sql/copy2.sql b/src/test/regress/sql/copy2.sql
index 45273557ce0..ba984388248 100644
--- a/src/test/regress/sql/copy2.sql
+++ b/src/test/regress/sql/copy2.sql
@@ -707,3 +707,14 @@ truncate copy_default;
 
 -- DEFAULT cannot be used in COPY TO
 copy (select 1 as test) TO stdout with (default '\D');
+
+-- COPY TO with partitioned table
+CREATE TABLE pp (id  INT, val int ) PARTITION BY RANGE (id);
+create table pp_1 (val int, id int);
+create table pp_2 (val int, id int);
+ALTER TABLE pp ATTACH PARTITION pp_1 FOR VALUES FROM (1) TO (3);
+ALTER TABLE pp ATTACH PARTITION pp_2 FOR VALUES FROM (3) TO (7);
+insert into pp select g, 10 + g from generate_series(1,6) g;
+copy pp to stdout(header);
+
+DROP TABLE PP;
-- 
2.34.1

From 4e501b7a8e67cffbf6432bdb43985b21d2b635b8 Mon Sep 17 00:00:00 2001
From: jian he <jian.universality@gmail.com>
Date: Sun, 30 Mar 2025 21:47:12 +0800
Subject: [PATCH v7 1/1] support COPY partitioned_table TO

CREATE TABLE pp (id  INT, val int ) PARTITION BY RANGE (id);
create table pp_1 (val int, id int);
create table pp_2 (val int, id int);
ALTER TABLE pp ATTACH PARTITION pp_1 FOR VALUES FROM (1) TO (5);
ALTER TABLE pp ATTACH PARTITION pp_2 FOR VALUES FROM (5) TO (10);
insert into pp select g, 10 + g from generate_series(1,9) g;
copy pp to stdout(header);

the above case is much slower (around 25% in some case) than
``COPY (select * from pp) to stdout(header);``,
because of column remaping.  but this is still a new
feature, since master does not support ``COPY (partitioned_table)``.

reivewed by: vignesh C <vignesh21@gmail.com>
reivewed by: David Rowley <dgrowleyml@gmail.com>
reivewed by: Melih Mutlu <m.melihmutlu@gmail.com>

discussion: https://postgr.es/m/CACJufxEZt+G19Ors3bQUq-42-61__C=y5k2wk=sHEFRusu7=iQ@mail.gmail.com
commitfest entry: https://commitfest.postgresql.org/patch/5467/
---
 doc/src/sgml/ref/copy.sgml          |   8 +-
 src/backend/commands/copyto.c       | 143 ++++++++++++++++++++++------
 src/test/regress/expected/copy2.out |  16 ++++
 src/test/regress/sql/copy2.sql      |  11 +++
 4 files changed, 147 insertions(+), 31 deletions(-)

diff --git a/doc/src/sgml/ref/copy.sgml b/doc/src/sgml/ref/copy.sgml
index df093da97c5..f86e0b7ec35 100644
--- a/doc/src/sgml/ref/copy.sgml
+++ b/doc/src/sgml/ref/copy.sgml
@@ -521,15 +521,15 @@ COPY <replaceable class="parameter">count</replaceable>
 
    <para>
     <command>COPY TO</command> can be used only with plain
-    tables, not views, and does not copy rows from child tables
-    or child partitions.  For example, <literal>COPY <replaceable
+    tables, not views, and does not copy rows from child tables,
+    however <command>COPY TO</command> can be used with partitioned tables.
+    For example, in a table inheritance hierarchy, <literal>COPY <replaceable
     class="parameter">table</replaceable> TO</literal> copies
     the same rows as <literal>SELECT * FROM ONLY <replaceable
     class="parameter">table</replaceable></literal>.
     The syntax <literal>COPY (SELECT * FROM <replaceable
     class="parameter">table</replaceable>) TO ...</literal> can be used to
-    dump all of the rows in an inheritance hierarchy, partitioned table,
-    or view.
+    dump all of the rows in an inheritance hierarchy, or view.
    </para>
 
    <para>
diff --git a/src/backend/commands/copyto.c b/src/backend/commands/copyto.c
index 84a3f3879a8..b75bbfea6a4 100644
--- a/src/backend/commands/copyto.c
+++ b/src/backend/commands/copyto.c
@@ -19,6 +19,8 @@
 #include <sys/stat.h>
 
 #include "access/tableam.h"
+#include "access/table.h"
+#include "catalog/pg_inherits.h"
 #include "commands/copyapi.h"
 #include "commands/progress.h"
 #include "executor/execdesc.h"
@@ -82,6 +84,7 @@ typedef struct CopyToStateData
 	List	   *attnumlist;		/* integer list of attnums to copy */
 	char	   *filename;		/* filename, or NULL for STDOUT */
 	bool		is_program;		/* is 'filename' a program to popen? */
+	List	   *partitions;		/* oid list of partition oid for copy to */
 	copy_data_dest_cb data_dest_cb; /* function for writing data */
 
 	CopyFormatOptions opts;
@@ -116,6 +119,8 @@ static void CopyOneRowTo(CopyToState cstate, TupleTableSlot *slot);
 static void CopyAttributeOutText(CopyToState cstate, const char *string);
 static void CopyAttributeOutCSV(CopyToState cstate, const char *string,
 								bool use_quote);
+static void CopyThisRelTo(CopyToState cstate, Relation rel,
+						  Relation root_rel, uint64 *processed);
 
 /* built-in format-specific routines */
 static void CopyToTextLikeStart(CopyToState cstate, TupleDesc tupDesc);
@@ -643,6 +648,8 @@ BeginCopyTo(ParseState *pstate,
 		PROGRESS_COPY_COMMAND_TO,
 		0
 	};
+	List	   *children = NIL;
+	List	   *scan_oids = NIL;
 
 	if (rel != NULL && rel->rd_rel->relkind != RELKIND_RELATION)
 	{
@@ -670,11 +677,35 @@ BeginCopyTo(ParseState *pstate,
 					 errmsg("cannot copy from sequence \"%s\"",
 							RelationGetRelationName(rel))));
 		else if (rel->rd_rel->relkind == RELKIND_PARTITIONED_TABLE)
-			ereport(ERROR,
-					(errcode(ERRCODE_WRONG_OBJECT_TYPE),
-					 errmsg("cannot copy from partitioned table \"%s\"",
-							RelationGetRelationName(rel)),
-					 errhint("Try the COPY (SELECT ...) TO variant.")));
+		{
+			children = find_all_inheritors(RelationGetRelid(rel),
+										   AccessShareLock,
+										   NULL);
+
+			foreach_oid(childreloid, children)
+			{
+				char		 relkind = get_rel_relkind(childreloid);
+
+				if (relkind == RELKIND_FOREIGN_TABLE)
+				{
+					char	   *relation_name;
+
+					relation_name = get_rel_name(childreloid);
+					ereport(ERROR,
+							errcode(ERRCODE_WRONG_OBJECT_TYPE),
+							errmsg("cannot copy from foreign table \"%s\"", relation_name),
+							errdetail("Partition \"%s\" is a foreign table in the partitioned table \"%s.%s\"",
+									  relation_name, RelationGetRelationName(rel),
+									  get_namespace_name(rel->rd_rel->relnamespace)),
+							errhint("Try the COPY (SELECT ...) TO variant."));
+				}
+
+				if (RELKIND_HAS_PARTITIONS(relkind))
+					continue;
+
+				scan_oids = lappend_oid(scan_oids, childreloid);
+			}
+		}
 		else
 			ereport(ERROR,
 					(errcode(ERRCODE_WRONG_OBJECT_TYPE),
@@ -710,6 +741,7 @@ BeginCopyTo(ParseState *pstate,
 		cstate->rel = rel;
 
 		tupDesc = RelationGetDescr(cstate->rel);
+		cstate->partitions = list_copy(scan_oids);
 	}
 	else
 	{
@@ -1066,35 +1098,28 @@ DoCopyTo(CopyToState cstate)
 
 	cstate->routine->CopyToStart(cstate, tupDesc);
 
-	if (cstate->rel)
+	/*
+	 * if COPY TO source table is a partitioned table, then open each
+	 * partition and process each individual partition.
+	 */
+	if (cstate->rel && cstate->rel->rd_rel->relkind == RELKIND_PARTITIONED_TABLE)
 	{
-		TupleTableSlot *slot;
-		TableScanDesc scandesc;
-
-		scandesc = table_beginscan(cstate->rel, GetActiveSnapshot(), 0, NULL);
-		slot = table_slot_create(cstate->rel, NULL);
-
 		processed = 0;
-		while (table_scan_getnextslot(scandesc, ForwardScanDirection, slot))
+		foreach_oid(scan_oid, cstate->partitions)
 		{
-			CHECK_FOR_INTERRUPTS();
+			Relation		scan_rel;
 
-			/* Deconstruct the tuple ... */
-			slot_getallattrs(slot);
+			scan_rel = table_open(scan_oid, AccessShareLock);
 
-			/* Format and send the data */
-			CopyOneRowTo(cstate, slot);
+			CopyThisRelTo(cstate, scan_rel, cstate->rel, &processed);
 
-			/*
-			 * Increment the number of processed tuples, and report the
-			 * progress.
-			 */
-			pgstat_progress_update_param(PROGRESS_COPY_TUPLES_PROCESSED,
-										 ++processed);
+			table_close(scan_rel, AccessShareLock);
 		}
-
-		ExecDropSingleTupleTableSlot(slot);
-		table_endscan(scandesc);
+	}
+	else if (cstate->rel)
+	{
+		processed = 0;
+		CopyThisRelTo(cstate, cstate->rel, NULL, &processed);
 	}
 	else
 	{
@@ -1113,6 +1138,70 @@ DoCopyTo(CopyToState cstate)
 	return processed;
 }
 
+/*
+ * rel: the relation to be copied to.
+ * root_rel: if not null, then the COPY partitioned relation to destination.
+ * processed: number of tuple processed.
+*/
+static void
+CopyThisRelTo(CopyToState cstate, Relation rel, Relation root_rel,
+			  uint64 *processed)
+{
+	TupleTableSlot *slot;
+	TableScanDesc scandesc;
+	AttrMap    		*map	= NULL;
+	TupleTableSlot  *root_slot = NULL;
+	TupleTableSlot  *original_slot = NULL;
+	TupleDesc		scan_tupdesc;
+	TupleDesc 		rootdesc	= NULL;
+
+	scan_tupdesc = RelationGetDescr(rel);
+	scandesc = table_beginscan(rel, GetActiveSnapshot(), 0, NULL);
+	slot = table_slot_create(rel, NULL);
+
+	/*
+	 * partition's rowtype might differ from the root table's.  We must
+	 * convert it back to the root table's rowtype as we are export
+	 * partitioned table data here.
+	*/
+	if (root_rel != NULL)
+	{
+		rootdesc = RelationGetDescr(root_rel);
+		map = build_attrmap_by_name_if_req(rootdesc, scan_tupdesc, false);
+	}
+
+	while (table_scan_getnextslot(scandesc, ForwardScanDirection, slot))
+	{
+		CHECK_FOR_INTERRUPTS();
+
+		/* Deconstruct the tuple ... */
+		if (map != NULL)
+		{
+			original_slot = slot;
+			root_slot = MakeSingleTupleTableSlot(rootdesc, &TTSOpsBufferHeapTuple);
+			slot = execute_attr_map_slot(map, slot, root_slot);
+		}
+		else
+			slot_getallattrs(slot);
+
+		/* Format and send the data */
+		CopyOneRowTo(cstate, slot);
+
+		/*
+		 * Increment the number of processed tuples, and report the
+		 * progress.
+		 */
+		pgstat_progress_update_param(PROGRESS_COPY_TUPLES_PROCESSED,
+									 ++(*processed));
+
+		if (original_slot != NULL)
+			ExecDropSingleTupleTableSlot(original_slot);
+	}
+
+	ExecDropSingleTupleTableSlot(slot);
+	table_endscan(scandesc);
+}
+
 /*
  * Emit one row during DoCopyTo().
  */
diff --git a/src/test/regress/expected/copy2.out b/src/test/regress/expected/copy2.out
index 64ea33aeae8..dcd97ae45b7 100644
--- a/src/test/regress/expected/copy2.out
+++ b/src/test/regress/expected/copy2.out
@@ -929,3 +929,19 @@ truncate copy_default;
 -- DEFAULT cannot be used in COPY TO
 copy (select 1 as test) TO stdout with (default '\D');
 ERROR:  COPY DEFAULT cannot be used with COPY TO
+-- COPY TO with partitioned table
+CREATE TABLE pp (id  INT, val int ) PARTITION BY RANGE (id);
+create table pp_1 (val int, id int);
+create table pp_2 (val int, id int);
+ALTER TABLE pp ATTACH PARTITION pp_1 FOR VALUES FROM (1) TO (3);
+ALTER TABLE pp ATTACH PARTITION pp_2 FOR VALUES FROM (3) TO (7);
+insert into pp select g, 10 + g from generate_series(1,6) g;
+copy pp to stdout(header);
+id	val
+1	11
+2	12
+3	13
+4	14
+5	15
+6	16
+DROP TABLE PP;
diff --git a/src/test/regress/sql/copy2.sql b/src/test/regress/sql/copy2.sql
index 45273557ce0..ba984388248 100644
--- a/src/test/regress/sql/copy2.sql
+++ b/src/test/regress/sql/copy2.sql
@@ -707,3 +707,14 @@ truncate copy_default;
 
 -- DEFAULT cannot be used in COPY TO
 copy (select 1 as test) TO stdout with (default '\D');
+
+-- COPY TO with partitioned table
+CREATE TABLE pp (id  INT, val int ) PARTITION BY RANGE (id);
+create table pp_1 (val int, id int);
+create table pp_2 (val int, id int);
+ALTER TABLE pp ATTACH PARTITION pp_1 FOR VALUES FROM (1) TO (3);
+ALTER TABLE pp ATTACH PARTITION pp_2 FOR VALUES FROM (3) TO (7);
+insert into pp select g, 10 + g from generate_series(1,6) g;
+copy pp to stdout(header);
+
+DROP TABLE PP;
-- 
2.34.1

From b27371ca4ff132e7d2803406f9e3f371c51c96df Mon Sep 17 00:00:00 2001
From: jian he <jian.universality@gmail.com>
Date: Tue, 1 Apr 2025 08:56:22 +0800
Subject: [PATCH v8 1/1] support COPY partitioned_table TO

CREATE TABLE pp (id  INT, val int ) PARTITION BY RANGE (id);
create table pp_1 (val int, id int);
create table pp_2 (val int, id int);
ALTER TABLE pp ATTACH PARTITION pp_1 FOR VALUES FROM (1) TO (5);
ALTER TABLE pp ATTACH PARTITION pp_2 FOR VALUES FROM (5) TO (10);
insert into pp select g, 10 + g from generate_series(1,9) g;
copy pp to stdout(header);

the above case is much slower (around 25% in some case) than
``COPY (select * from pp) to stdout(header);``,
because of column remaping.  but this is still a new
feature, since master does not support ``COPY (partitioned_table)``.

reivewed by: vignesh C <vignesh21@gmail.com>
reivewed by: David Rowley <dgrowleyml@gmail.com>
reivewed by: Melih Mutlu <m.melihmutlu@gmail.com>
reivewed by: Kirill Reshke <reshkekirill@gmail.com>

discussion: https://postgr.es/m/CACJufxEZt+G19Ors3bQUq-42-61__C=y5k2wk=sHEFRusu7=iQ@mail.gmail.com
commitfest entry: https://commitfest.postgresql.org/patch/5467/
---
 doc/src/sgml/ref/copy.sgml          |   8 +-
 src/backend/commands/copyto.c       | 143 ++++++++++++++++++++++------
 src/test/regress/expected/copy2.out |  20 ++++
 src/test/regress/sql/copy2.sql      |  17 ++++
 4 files changed, 157 insertions(+), 31 deletions(-)

diff --git a/doc/src/sgml/ref/copy.sgml b/doc/src/sgml/ref/copy.sgml
index df093da97c5..f86e0b7ec35 100644
--- a/doc/src/sgml/ref/copy.sgml
+++ b/doc/src/sgml/ref/copy.sgml
@@ -521,15 +521,15 @@ COPY <replaceable class="parameter">count</replaceable>
 
    <para>
     <command>COPY TO</command> can be used only with plain
-    tables, not views, and does not copy rows from child tables
-    or child partitions.  For example, <literal>COPY <replaceable
+    tables, not views, and does not copy rows from child tables,
+    however <command>COPY TO</command> can be used with partitioned tables.
+    For example, in a table inheritance hierarchy, <literal>COPY <replaceable
     class="parameter">table</replaceable> TO</literal> copies
     the same rows as <literal>SELECT * FROM ONLY <replaceable
     class="parameter">table</replaceable></literal>.
     The syntax <literal>COPY (SELECT * FROM <replaceable
     class="parameter">table</replaceable>) TO ...</literal> can be used to
-    dump all of the rows in an inheritance hierarchy, partitioned table,
-    or view.
+    dump all of the rows in an inheritance hierarchy, or view.
    </para>
 
    <para>
diff --git a/src/backend/commands/copyto.c b/src/backend/commands/copyto.c
index 84a3f3879a8..6fc940bddbc 100644
--- a/src/backend/commands/copyto.c
+++ b/src/backend/commands/copyto.c
@@ -19,6 +19,8 @@
 #include <sys/stat.h>
 
 #include "access/tableam.h"
+#include "access/table.h"
+#include "catalog/pg_inherits.h"
 #include "commands/copyapi.h"
 #include "commands/progress.h"
 #include "executor/execdesc.h"
@@ -82,6 +84,7 @@ typedef struct CopyToStateData
 	List	   *attnumlist;		/* integer list of attnums to copy */
 	char	   *filename;		/* filename, or NULL for STDOUT */
 	bool		is_program;		/* is 'filename' a program to popen? */
+	List	   *partitions;		/* oid list of partition oid for copy to */
 	copy_data_dest_cb data_dest_cb; /* function for writing data */
 
 	CopyFormatOptions opts;
@@ -116,6 +119,8 @@ static void CopyOneRowTo(CopyToState cstate, TupleTableSlot *slot);
 static void CopyAttributeOutText(CopyToState cstate, const char *string);
 static void CopyAttributeOutCSV(CopyToState cstate, const char *string,
 								bool use_quote);
+static void CopyThisRelTo(CopyToState cstate, Relation rel,
+						  Relation root_rel, uint64 *processed);
 
 /* built-in format-specific routines */
 static void CopyToTextLikeStart(CopyToState cstate, TupleDesc tupDesc);
@@ -643,6 +648,8 @@ BeginCopyTo(ParseState *pstate,
 		PROGRESS_COPY_COMMAND_TO,
 		0
 	};
+	List	   *children = NIL;
+	List	   *scan_oids = NIL;
 
 	if (rel != NULL && rel->rd_rel->relkind != RELKIND_RELATION)
 	{
@@ -670,11 +677,35 @@ BeginCopyTo(ParseState *pstate,
 					 errmsg("cannot copy from sequence \"%s\"",
 							RelationGetRelationName(rel))));
 		else if (rel->rd_rel->relkind == RELKIND_PARTITIONED_TABLE)
-			ereport(ERROR,
-					(errcode(ERRCODE_WRONG_OBJECT_TYPE),
-					 errmsg("cannot copy from partitioned table \"%s\"",
-							RelationGetRelationName(rel)),
-					 errhint("Try the COPY (SELECT ...) TO variant.")));
+		{
+			children = find_all_inheritors(RelationGetRelid(rel),
+										   AccessShareLock,
+										   NULL);
+
+			foreach_oid(childreloid, children)
+			{
+				char		 relkind = get_rel_relkind(childreloid);
+
+				if (relkind == RELKIND_FOREIGN_TABLE)
+				{
+					char	   *relation_name;
+
+					relation_name = get_rel_name(childreloid);
+					ereport(ERROR,
+							errcode(ERRCODE_WRONG_OBJECT_TYPE),
+							errmsg("cannot copy from foreign table \"%s\"", relation_name),
+							errdetail("Partition \"%s\" is a foreign table in the partitioned table \"%s.%s\"",
+									  relation_name, RelationGetRelationName(rel),
+									  get_namespace_name(rel->rd_rel->relnamespace)),
+							errhint("Try the COPY (SELECT ...) TO variant."));
+				}
+
+				if (RELKIND_HAS_PARTITIONS(relkind))
+					continue;
+
+				scan_oids = lappend_oid(scan_oids, childreloid);
+			}
+		}
 		else
 			ereport(ERROR,
 					(errcode(ERRCODE_WRONG_OBJECT_TYPE),
@@ -710,6 +741,7 @@ BeginCopyTo(ParseState *pstate,
 		cstate->rel = rel;
 
 		tupDesc = RelationGetDescr(cstate->rel);
+		cstate->partitions = list_copy(scan_oids);
 	}
 	else
 	{
@@ -1066,35 +1098,28 @@ DoCopyTo(CopyToState cstate)
 
 	cstate->routine->CopyToStart(cstate, tupDesc);
 
-	if (cstate->rel)
+	/*
+	 * if COPY TO source table is a partitioned table, then open each
+	 * partition and process each individual partition.
+	 */
+	if (cstate->rel && cstate->rel->rd_rel->relkind == RELKIND_PARTITIONED_TABLE)
 	{
-		TupleTableSlot *slot;
-		TableScanDesc scandesc;
-
-		scandesc = table_beginscan(cstate->rel, GetActiveSnapshot(), 0, NULL);
-		slot = table_slot_create(cstate->rel, NULL);
-
 		processed = 0;
-		while (table_scan_getnextslot(scandesc, ForwardScanDirection, slot))
+		foreach_oid(scan_oid, cstate->partitions)
 		{
-			CHECK_FOR_INTERRUPTS();
+			Relation		scan_rel;
 
-			/* Deconstruct the tuple ... */
-			slot_getallattrs(slot);
+			scan_rel = table_open(scan_oid, AccessShareLock);
 
-			/* Format and send the data */
-			CopyOneRowTo(cstate, slot);
+			CopyThisRelTo(cstate, scan_rel, cstate->rel, &processed);
 
-			/*
-			 * Increment the number of processed tuples, and report the
-			 * progress.
-			 */
-			pgstat_progress_update_param(PROGRESS_COPY_TUPLES_PROCESSED,
-										 ++processed);
+			table_close(scan_rel, AccessShareLock);
 		}
-
-		ExecDropSingleTupleTableSlot(slot);
-		table_endscan(scandesc);
+	}
+	else if (cstate->rel)
+	{
+		processed = 0;
+		CopyThisRelTo(cstate, cstate->rel, NULL, &processed);
 	}
 	else
 	{
@@ -1113,6 +1138,70 @@ DoCopyTo(CopyToState cstate)
 	return processed;
 }
 
+/*
+ * rel: the relation to be copied to.
+ * root_rel: if not NULL, then the COPY partitioned relation to destination.
+ * processed: number of tuple processed.
+*/
+static void
+CopyThisRelTo(CopyToState cstate, Relation rel, Relation root_rel,
+			  uint64 *processed)
+{
+	TupleTableSlot *slot;
+	TableScanDesc scandesc;
+	AttrMap    		*map	= NULL;
+	TupleTableSlot  *root_slot = NULL;
+	TupleTableSlot  *original_slot = NULL;
+	TupleDesc		scan_tupdesc;
+	TupleDesc 		rootdesc	= NULL;
+
+	scan_tupdesc = RelationGetDescr(rel);
+	scandesc = table_beginscan(rel, GetActiveSnapshot(), 0, NULL);
+	slot = table_slot_create(rel, NULL);
+
+	/*
+	 * partition's rowtype might differ from the root table's.  We must
+	 * convert it back to the root table's rowtype as we are export
+	 * partitioned table data here.
+	*/
+	if (root_rel != NULL)
+	{
+		rootdesc = RelationGetDescr(root_rel);
+		map = build_attrmap_by_name_if_req(rootdesc, scan_tupdesc, false);
+	}
+
+	while (table_scan_getnextslot(scandesc, ForwardScanDirection, slot))
+	{
+		CHECK_FOR_INTERRUPTS();
+
+		/* Deconstruct the tuple ... */
+		if (map != NULL)
+		{
+			original_slot = slot;
+			root_slot = MakeSingleTupleTableSlot(rootdesc, &TTSOpsBufferHeapTuple);
+			slot = execute_attr_map_slot(map, slot, root_slot);
+		}
+		else
+			slot_getallattrs(slot);
+
+		/* Format and send the data */
+		CopyOneRowTo(cstate, slot);
+
+		/*
+		 * Increment the number of processed tuples, and report the
+		 * progress.
+		 */
+		pgstat_progress_update_param(PROGRESS_COPY_TUPLES_PROCESSED,
+									 ++(*processed));
+
+		if (original_slot != NULL)
+			ExecDropSingleTupleTableSlot(original_slot);
+	}
+
+	ExecDropSingleTupleTableSlot(slot);
+	table_endscan(scandesc);
+}
+
 /*
  * Emit one row during DoCopyTo().
  */
diff --git a/src/test/regress/expected/copy2.out b/src/test/regress/expected/copy2.out
index 64ea33aeae8..b01389df44c 100644
--- a/src/test/regress/expected/copy2.out
+++ b/src/test/regress/expected/copy2.out
@@ -929,3 +929,23 @@ truncate copy_default;
 -- DEFAULT cannot be used in COPY TO
 copy (select 1 as test) TO stdout with (default '\D');
 ERROR:  COPY DEFAULT cannot be used with COPY TO
+-- COPY TO with partitioned table
+CREATE TABLE pp (id int,val int) PARTITION BY RANGE (id);
+CREATE TABLE pp_1 (val int, id int) PARTITION BY RANGE (id);
+CREATE TABLE pp_2 (val int, id int) PARTITION BY RANGE (id);
+ALTER TABLE pp ATTACH PARTITION pp_1 FOR VALUES FROM (1) TO (5);
+ALTER TABLE pp ATTACH PARTITION pp_2 FOR VALUES FROM (5) TO (10);
+CREATE TABLE pp_15 (val int, id int);
+CREATE TABLE pp_510 (val int,id int);
+ALTER TABLE pp_1 ATTACH PARTITION pp_15 FOR VALUES FROM (1) TO (5);
+ALTER TABLE pp_2 ATTACH PARTITION pp_510 FOR VALUES FROM (5) TO (10);
+INSERT INTO pp SELECT g, 10 + g FROM generate_series(1, 6) g;
+COPY pp TO stdout(header);
+id	val
+1	11
+2	12
+3	13
+4	14
+5	15
+6	16
+DROP TABLE PP;
diff --git a/src/test/regress/sql/copy2.sql b/src/test/regress/sql/copy2.sql
index 45273557ce0..3730f8af4e5 100644
--- a/src/test/regress/sql/copy2.sql
+++ b/src/test/regress/sql/copy2.sql
@@ -707,3 +707,20 @@ truncate copy_default;
 
 -- DEFAULT cannot be used in COPY TO
 copy (select 1 as test) TO stdout with (default '\D');
+
+-- COPY TO with partitioned table
+CREATE TABLE pp (id int,val int) PARTITION BY RANGE (id);
+CREATE TABLE pp_1 (val int, id int) PARTITION BY RANGE (id);
+CREATE TABLE pp_2 (val int, id int) PARTITION BY RANGE (id);
+ALTER TABLE pp ATTACH PARTITION pp_1 FOR VALUES FROM (1) TO (5);
+ALTER TABLE pp ATTACH PARTITION pp_2 FOR VALUES FROM (5) TO (10);
+
+CREATE TABLE pp_15 (val int, id int);
+CREATE TABLE pp_510 (val int,id int);
+ALTER TABLE pp_1 ATTACH PARTITION pp_15 FOR VALUES FROM (1) TO (5);
+ALTER TABLE pp_2 ATTACH PARTITION pp_510 FOR VALUES FROM (5) TO (10);
+
+INSERT INTO pp SELECT g, 10 + g FROM generate_series(1, 6) g;
+
+COPY pp TO stdout(header);
+DROP TABLE PP;
-- 
2.34.1

From 374f38d7187e92882e5fa4fe6e8b9dd7d7785ed9 Mon Sep 17 00:00:00 2001
From: jian he <jian.universality@gmail.com>
Date: Fri, 4 Apr 2025 11:09:04 +0800
Subject: [PATCH v9 1/1] support COPY partitioned_table TO

CREATE TABLE pp (id  INT, val int ) PARTITION BY RANGE (id);
create table pp_1 (val int, id int);
create table pp_2 (val int, id int);
ALTER TABLE pp ATTACH PARTITION pp_1 FOR VALUES FROM (1) TO (5);
ALTER TABLE pp ATTACH PARTITION pp_2 FOR VALUES FROM (5) TO (10);
insert into pp select g, 10 + g from generate_series(1,9) g;
copy pp to stdout(header);

the above case is much slower (around 25% in some case) than
``COPY (select * from pp) to stdout(header);``,
because of column remaping.  but this is still a new
feature, since master does not support ``COPY (partitioned_table)``.

reivewed by: vignesh C <vignesh21@gmail.com>
reivewed by: David Rowley <dgrowleyml@gmail.com>
reivewed by: Melih Mutlu <m.melihmutlu@gmail.com>
reivewed by: Kirill Reshke <reshkekirill@gmail.com>

discussion: https://postgr.es/m/CACJufxEZt+G19Ors3bQUq-42-61__C=y5k2wk=sHEFRusu7=iQ@mail.gmail.com
commitfest entry: https://commitfest.postgresql.org/patch/5467/
---
 doc/src/sgml/ref/copy.sgml          |   8 +-
 src/backend/commands/copyto.c       | 144 ++++++++++++++++++++++------
 src/test/regress/expected/copy2.out |  20 ++++
 src/test/regress/sql/copy2.sql      |  17 ++++
 4 files changed, 156 insertions(+), 33 deletions(-)

diff --git a/doc/src/sgml/ref/copy.sgml b/doc/src/sgml/ref/copy.sgml
index df093da97c5..b7d24f5d271 100644
--- a/doc/src/sgml/ref/copy.sgml
+++ b/doc/src/sgml/ref/copy.sgml
@@ -521,15 +521,15 @@ COPY <replaceable class="parameter">count</replaceable>
 
    <para>
     <command>COPY TO</command> can be used only with plain
-    tables, not views, and does not copy rows from child tables
-    or child partitions.  For example, <literal>COPY <replaceable
+    tables, not views, and does not copy rows from child tables,
+    however <command>COPY TO</command> can be used with partitioned tables.
+    For example, in a table inheritance hierarchy, <literal>COPY <replaceable
     class="parameter">table</replaceable> TO</literal> copies
     the same rows as <literal>SELECT * FROM ONLY <replaceable
     class="parameter">table</replaceable></literal>.
     The syntax <literal>COPY (SELECT * FROM <replaceable
     class="parameter">table</replaceable>) TO ...</literal> can be used to
-    dump all of the rows in an inheritance hierarchy, partitioned table,
-    or view.
+    dump all of the rows in a table inheritance hierarchy, or view.
    </para>
 
    <para>
diff --git a/src/backend/commands/copyto.c b/src/backend/commands/copyto.c
index 84a3f3879a8..d0aab894c36 100644
--- a/src/backend/commands/copyto.c
+++ b/src/backend/commands/copyto.c
@@ -19,6 +19,8 @@
 #include <sys/stat.h>
 
 #include "access/tableam.h"
+#include "access/table.h"
+#include "catalog/pg_inherits.h"
 #include "commands/copyapi.h"
 #include "commands/progress.h"
 #include "executor/execdesc.h"
@@ -82,6 +84,7 @@ typedef struct CopyToStateData
 	List	   *attnumlist;		/* integer list of attnums to copy */
 	char	   *filename;		/* filename, or NULL for STDOUT */
 	bool		is_program;		/* is 'filename' a program to popen? */
+	List	   *partitions;		/* oid list of partition oid for copy to */
 	copy_data_dest_cb data_dest_cb; /* function for writing data */
 
 	CopyFormatOptions opts;
@@ -116,6 +119,8 @@ static void CopyOneRowTo(CopyToState cstate, TupleTableSlot *slot);
 static void CopyAttributeOutText(CopyToState cstate, const char *string);
 static void CopyAttributeOutCSV(CopyToState cstate, const char *string,
 								bool use_quote);
+static void CopyThisRelTo(CopyToState cstate, Relation rel,
+						  Relation root_rel, uint64 *processed);
 
 /* built-in format-specific routines */
 static void CopyToTextLikeStart(CopyToState cstate, TupleDesc tupDesc);
@@ -643,6 +648,8 @@ BeginCopyTo(ParseState *pstate,
 		PROGRESS_COPY_COMMAND_TO,
 		0
 	};
+	List	   *children = NIL;
+	List	   *scan_oids = NIL;
 
 	if (rel != NULL && rel->rd_rel->relkind != RELKIND_RELATION)
 	{
@@ -670,11 +677,35 @@ BeginCopyTo(ParseState *pstate,
 					 errmsg("cannot copy from sequence \"%s\"",
 							RelationGetRelationName(rel))));
 		else if (rel->rd_rel->relkind == RELKIND_PARTITIONED_TABLE)
-			ereport(ERROR,
-					(errcode(ERRCODE_WRONG_OBJECT_TYPE),
-					 errmsg("cannot copy from partitioned table \"%s\"",
-							RelationGetRelationName(rel)),
-					 errhint("Try the COPY (SELECT ...) TO variant.")));
+		{
+			children = find_all_inheritors(RelationGetRelid(rel),
+										   AccessShareLock,
+										   NULL);
+
+			foreach_oid(childreloid, children)
+			{
+				char		 relkind = get_rel_relkind(childreloid);
+
+				if (relkind == RELKIND_FOREIGN_TABLE)
+				{
+					char	   *relation_name;
+
+					relation_name = get_rel_name(childreloid);
+					ereport(ERROR,
+							errcode(ERRCODE_WRONG_OBJECT_TYPE),
+							errmsg("cannot copy from foreign table \"%s\"", relation_name),
+							errdetail("Partition \"%s\" is a foreign table in the partitioned table \"%s.%s\"",
+									  relation_name, RelationGetRelationName(rel),
+									  get_namespace_name(rel->rd_rel->relnamespace)),
+							errhint("Try the COPY (SELECT ...) TO variant."));
+				}
+
+				if (RELKIND_HAS_PARTITIONS(relkind))
+					continue;
+
+				scan_oids = lappend_oid(scan_oids, childreloid);
+			}
+		}
 		else
 			ereport(ERROR,
 					(errcode(ERRCODE_WRONG_OBJECT_TYPE),
@@ -710,6 +741,7 @@ BeginCopyTo(ParseState *pstate,
 		cstate->rel = rel;
 
 		tupDesc = RelationGetDescr(cstate->rel);
+		cstate->partitions = list_copy(scan_oids);
 	}
 	else
 	{
@@ -1028,7 +1060,7 @@ DoCopyTo(CopyToState cstate)
 	TupleDesc	tupDesc;
 	int			num_phys_attrs;
 	ListCell   *cur;
-	uint64		processed;
+	uint64		processed = 0;
 
 	if (fe_copy)
 		SendCopyBegin(cstate);
@@ -1066,36 +1098,25 @@ DoCopyTo(CopyToState cstate)
 
 	cstate->routine->CopyToStart(cstate, tupDesc);
 
-	if (cstate->rel)
+	/*
+	 * if COPY TO source table is a partitioned table, then open each
+	 * partition and process each individual partition.
+	 */
+	if (cstate->rel && cstate->rel->rd_rel->relkind == RELKIND_PARTITIONED_TABLE)
 	{
-		TupleTableSlot *slot;
-		TableScanDesc scandesc;
-
-		scandesc = table_beginscan(cstate->rel, GetActiveSnapshot(), 0, NULL);
-		slot = table_slot_create(cstate->rel, NULL);
-
-		processed = 0;
-		while (table_scan_getnextslot(scandesc, ForwardScanDirection, slot))
+		foreach_oid(scan_oid, cstate->partitions)
 		{
-			CHECK_FOR_INTERRUPTS();
+			Relation		scan_rel;
 
-			/* Deconstruct the tuple ... */
-			slot_getallattrs(slot);
+			scan_rel = table_open(scan_oid, AccessShareLock);
 
-			/* Format and send the data */
-			CopyOneRowTo(cstate, slot);
+			CopyThisRelTo(cstate, scan_rel, cstate->rel, &processed);
 
-			/*
-			 * Increment the number of processed tuples, and report the
-			 * progress.
-			 */
-			pgstat_progress_update_param(PROGRESS_COPY_TUPLES_PROCESSED,
-										 ++processed);
+			table_close(scan_rel, AccessShareLock);
 		}
-
-		ExecDropSingleTupleTableSlot(slot);
-		table_endscan(scandesc);
 	}
+	else if (cstate->rel)
+		CopyThisRelTo(cstate, cstate->rel, NULL, &processed);
 	else
 	{
 		/* run the plan --- the dest receiver will send tuples */
@@ -1113,6 +1134,71 @@ DoCopyTo(CopyToState cstate)
 	return processed;
 }
 
+/*
+ * rel: the relation from which the actual data will be copied.
+ * root_rel: if not NULL, it indicates that we are copying partitioned relation
+ * data to the destination, and "rel" is the partition of "root_rel".
+ * processed: number of tuples processed.
+*/
+static void
+CopyThisRelTo(CopyToState cstate, Relation rel, Relation root_rel,
+			  uint64 *processed)
+{
+	TupleTableSlot *slot;
+	TableScanDesc scandesc;
+	AttrMap    		*map	= NULL;
+	TupleTableSlot  *root_slot = NULL;
+	TupleTableSlot  *original_slot = NULL;
+	TupleDesc		scan_tupdesc;
+	TupleDesc 		rootdesc	= NULL;
+
+	scan_tupdesc = RelationGetDescr(rel);
+	scandesc = table_beginscan(rel, GetActiveSnapshot(), 0, NULL);
+	slot = table_slot_create(rel, NULL);
+
+	/*
+	 * A partition's rowtype might differ from the root table's.
+	 * Since we are export partitioned table data here,
+	 * we must convert it back to the root table's rowtype.
+	*/
+	if (root_rel != NULL)
+	{
+		rootdesc = RelationGetDescr(root_rel);
+		map = build_attrmap_by_name_if_req(rootdesc, scan_tupdesc, false);
+	}
+
+	while (table_scan_getnextslot(scandesc, ForwardScanDirection, slot))
+	{
+		CHECK_FOR_INTERRUPTS();
+
+		/* Deconstruct the tuple ... */
+		if (map != NULL)
+		{
+			original_slot = slot;
+			root_slot = MakeSingleTupleTableSlot(rootdesc, &TTSOpsBufferHeapTuple);
+			slot = execute_attr_map_slot(map, slot, root_slot);
+		}
+		else
+			slot_getallattrs(slot);
+
+		/* Format and send the data */
+		CopyOneRowTo(cstate, slot);
+
+		/*
+		 * Increment the number of processed tuples, and report the
+		 * progress.
+		 */
+		pgstat_progress_update_param(PROGRESS_COPY_TUPLES_PROCESSED,
+									 ++(*processed));
+
+		if (original_slot != NULL)
+			ExecDropSingleTupleTableSlot(original_slot);
+	}
+
+	ExecDropSingleTupleTableSlot(slot);
+	table_endscan(scandesc);
+}
+
 /*
  * Emit one row during DoCopyTo().
  */
diff --git a/src/test/regress/expected/copy2.out b/src/test/regress/expected/copy2.out
index 64ea33aeae8..b01389df44c 100644
--- a/src/test/regress/expected/copy2.out
+++ b/src/test/regress/expected/copy2.out
@@ -929,3 +929,23 @@ truncate copy_default;
 -- DEFAULT cannot be used in COPY TO
 copy (select 1 as test) TO stdout with (default '\D');
 ERROR:  COPY DEFAULT cannot be used with COPY TO
+-- COPY TO with partitioned table
+CREATE TABLE pp (id int,val int) PARTITION BY RANGE (id);
+CREATE TABLE pp_1 (val int, id int) PARTITION BY RANGE (id);
+CREATE TABLE pp_2 (val int, id int) PARTITION BY RANGE (id);
+ALTER TABLE pp ATTACH PARTITION pp_1 FOR VALUES FROM (1) TO (5);
+ALTER TABLE pp ATTACH PARTITION pp_2 FOR VALUES FROM (5) TO (10);
+CREATE TABLE pp_15 (val int, id int);
+CREATE TABLE pp_510 (val int,id int);
+ALTER TABLE pp_1 ATTACH PARTITION pp_15 FOR VALUES FROM (1) TO (5);
+ALTER TABLE pp_2 ATTACH PARTITION pp_510 FOR VALUES FROM (5) TO (10);
+INSERT INTO pp SELECT g, 10 + g FROM generate_series(1, 6) g;
+COPY pp TO stdout(header);
+id	val
+1	11
+2	12
+3	13
+4	14
+5	15
+6	16
+DROP TABLE PP;
diff --git a/src/test/regress/sql/copy2.sql b/src/test/regress/sql/copy2.sql
index 45273557ce0..3730f8af4e5 100644
--- a/src/test/regress/sql/copy2.sql
+++ b/src/test/regress/sql/copy2.sql
@@ -707,3 +707,20 @@ truncate copy_default;
 
 -- DEFAULT cannot be used in COPY TO
 copy (select 1 as test) TO stdout with (default '\D');
+
+-- COPY TO with partitioned table
+CREATE TABLE pp (id int,val int) PARTITION BY RANGE (id);
+CREATE TABLE pp_1 (val int, id int) PARTITION BY RANGE (id);
+CREATE TABLE pp_2 (val int, id int) PARTITION BY RANGE (id);
+ALTER TABLE pp ATTACH PARTITION pp_1 FOR VALUES FROM (1) TO (5);
+ALTER TABLE pp ATTACH PARTITION pp_2 FOR VALUES FROM (5) TO (10);
+
+CREATE TABLE pp_15 (val int, id int);
+CREATE TABLE pp_510 (val int,id int);
+ALTER TABLE pp_1 ATTACH PARTITION pp_15 FOR VALUES FROM (1) TO (5);
+ALTER TABLE pp_2 ATTACH PARTITION pp_510 FOR VALUES FROM (5) TO (10);
+
+INSERT INTO pp SELECT g, 10 + g FROM generate_series(1, 6) g;
+
+COPY pp TO stdout(header);
+DROP TABLE PP;
-- 
2.34.1

From f56c94ccb018928e41cc35e162174831cb016c1d Mon Sep 17 00:00:00 2001
From: jian he <jian.universality@gmail.com>
Date: Thu, 10 Apr 2025 10:41:40 +0800
Subject: [PATCH v10 1/1] support COPY partitioned_table TO

CREATE TABLE pp (id  int, val int ) PARTITION BY RANGE (id);
CREATE TABLE pp_1 (val int, id int);
CREATE TABLE pp_2 (val int, id int);
ALTER TABLE pp ATTACH PARTITION pp_1 FOR VALUES FROM (1) TO (5);
ALTER TABLE pp ATTACH PARTITION pp_2 FOR VALUES FROM (5) TO (10);
insert into pp select g, 10 + g from generate_series(1,9) g;
copy pp to stdout(header);

the above case is much slower (around 25% in some case) than
``COPY (select * from pp) to stdout(header);``,
because of column remaping.  but this is still a new
feature, since master does not support ``COPY (partitioned_table)``.

reivewed by: vignesh C <vignesh21@gmail.com>
reivewed by: David Rowley <dgrowleyml@gmail.com>
reivewed by: Melih Mutlu <m.melihmutlu@gmail.com>
reivewed by: Kirill Reshke <reshkekirill@gmail.com>

discussion: https://postgr.es/m/CACJufxEZt+G19Ors3bQUq-42-61__C=y5k2wk=sHEFRusu7=iQ@mail.gmail.com
commitfest entry: https://commitfest.postgresql.org/patch/5467/
---
 doc/src/sgml/ref/copy.sgml         |   6 +-
 src/backend/commands/copyto.c      | 144 +++++++++++++++++++++++------
 src/test/regress/expected/copy.out |  18 ++++
 src/test/regress/sql/copy.sql      |  15 +++
 4 files changed, 151 insertions(+), 32 deletions(-)

diff --git a/doc/src/sgml/ref/copy.sgml b/doc/src/sgml/ref/copy.sgml
index d6859276bed..293251a76a0 100644
--- a/doc/src/sgml/ref/copy.sgml
+++ b/doc/src/sgml/ref/copy.sgml
@@ -521,13 +521,13 @@ COPY <replaceable class="parameter">count</replaceable>
 
    <para>
     <command>COPY TO</command> can be used with plain
-    tables and populated materialized views.
-    For example,
+    tables, populated materialized views and partitioned tables.
+    For example, if <replaceable class="parameter">table</replaceable> is not partitioned table,
     <literal>COPY <replaceable class="parameter">table</replaceable>
     TO</literal> copies the same rows as
     <literal>SELECT * FROM ONLY <replaceable class="parameter">table</replaceable></literal>.
     However it doesn't directly support other relation types,
-    such as partitioned tables, inheritance child tables, or views.
+    such as inheritance child tables, or views.
     To copy all rows from such relations, use <literal>COPY (SELECT * FROM
     <replaceable class="parameter">table</replaceable>) TO</literal>.
    </para>
diff --git a/src/backend/commands/copyto.c b/src/backend/commands/copyto.c
index f87e405351d..5f6cf92f86e 100644
--- a/src/backend/commands/copyto.c
+++ b/src/backend/commands/copyto.c
@@ -19,6 +19,8 @@
 #include <sys/stat.h>
 
 #include "access/tableam.h"
+#include "access/table.h"
+#include "catalog/pg_inherits.h"
 #include "commands/copyapi.h"
 #include "commands/progress.h"
 #include "executor/execdesc.h"
@@ -82,6 +84,7 @@ typedef struct CopyToStateData
 	List	   *attnumlist;		/* integer list of attnums to copy */
 	char	   *filename;		/* filename, or NULL for STDOUT */
 	bool		is_program;		/* is 'filename' a program to popen? */
+	List	   *partitions;		/* oid list of partition oid for copy to */
 	copy_data_dest_cb data_dest_cb; /* function for writing data */
 
 	CopyFormatOptions opts;
@@ -116,6 +119,8 @@ static void CopyOneRowTo(CopyToState cstate, TupleTableSlot *slot);
 static void CopyAttributeOutText(CopyToState cstate, const char *string);
 static void CopyAttributeOutCSV(CopyToState cstate, const char *string,
 								bool use_quote);
+static void CopyThisRelTo(CopyToState cstate, Relation rel,
+						  Relation root_rel, uint64 *processed);
 
 /* built-in format-specific routines */
 static void CopyToTextLikeStart(CopyToState cstate, TupleDesc tupDesc);
@@ -643,6 +648,8 @@ BeginCopyTo(ParseState *pstate,
 		PROGRESS_COPY_COMMAND_TO,
 		0
 	};
+	List	   *children = NIL;
+	List	   *scan_oids = NIL;
 
 	if (rel != NULL && rel->rd_rel->relkind != RELKIND_RELATION)
 	{
@@ -673,11 +680,35 @@ BeginCopyTo(ParseState *pstate,
 					 errmsg("cannot copy from sequence \"%s\"",
 							RelationGetRelationName(rel))));
 		else if (rel->rd_rel->relkind == RELKIND_PARTITIONED_TABLE)
-			ereport(ERROR,
-					(errcode(ERRCODE_WRONG_OBJECT_TYPE),
-					 errmsg("cannot copy from partitioned table \"%s\"",
-							RelationGetRelationName(rel)),
-					 errhint("Try the COPY (SELECT ...) TO variant.")));
+		{
+			children = find_all_inheritors(RelationGetRelid(rel),
+										   AccessShareLock,
+										   NULL);
+
+			foreach_oid(childreloid, children)
+			{
+				char		 relkind = get_rel_relkind(childreloid);
+
+				if (relkind == RELKIND_FOREIGN_TABLE)
+				{
+					char	   *relation_name;
+
+					relation_name = get_rel_name(childreloid);
+					ereport(ERROR,
+							errcode(ERRCODE_WRONG_OBJECT_TYPE),
+							errmsg("cannot copy from foreign table \"%s\"", relation_name),
+							errdetail("Partition \"%s\" is a foreign table in the partitioned table \"%s.%s\"",
+									  relation_name, RelationGetRelationName(rel),
+									  get_namespace_name(rel->rd_rel->relnamespace)),
+							errhint("Try the COPY (SELECT ...) TO variant."));
+				}
+
+				if (RELKIND_HAS_PARTITIONS(relkind))
+					continue;
+
+				scan_oids = lappend_oid(scan_oids, childreloid);
+			}
+		}
 		else
 			ereport(ERROR,
 					(errcode(ERRCODE_WRONG_OBJECT_TYPE),
@@ -713,6 +744,7 @@ BeginCopyTo(ParseState *pstate,
 		cstate->rel = rel;
 
 		tupDesc = RelationGetDescr(cstate->rel);
+		cstate->partitions = list_copy(scan_oids);
 	}
 	else
 	{
@@ -1031,7 +1063,7 @@ DoCopyTo(CopyToState cstate)
 	TupleDesc	tupDesc;
 	int			num_phys_attrs;
 	ListCell   *cur;
-	uint64		processed;
+	uint64		processed = 0;
 
 	if (fe_copy)
 		SendCopyBegin(cstate);
@@ -1069,36 +1101,25 @@ DoCopyTo(CopyToState cstate)
 
 	cstate->routine->CopyToStart(cstate, tupDesc);
 
-	if (cstate->rel)
+	/*
+	 * if COPY TO source table is a partitioned table, then open each
+	 * partition and process each individual partition.
+	 */
+	if (cstate->rel && cstate->rel->rd_rel->relkind == RELKIND_PARTITIONED_TABLE)
 	{
-		TupleTableSlot *slot;
-		TableScanDesc scandesc;
-
-		scandesc = table_beginscan(cstate->rel, GetActiveSnapshot(), 0, NULL);
-		slot = table_slot_create(cstate->rel, NULL);
-
-		processed = 0;
-		while (table_scan_getnextslot(scandesc, ForwardScanDirection, slot))
+		foreach_oid(scan_oid, cstate->partitions)
 		{
-			CHECK_FOR_INTERRUPTS();
+			Relation		scan_rel;
 
-			/* Deconstruct the tuple ... */
-			slot_getallattrs(slot);
+			scan_rel = table_open(scan_oid, AccessShareLock);
 
-			/* Format and send the data */
-			CopyOneRowTo(cstate, slot);
+			CopyThisRelTo(cstate, scan_rel, cstate->rel, &processed);
 
-			/*
-			 * Increment the number of processed tuples, and report the
-			 * progress.
-			 */
-			pgstat_progress_update_param(PROGRESS_COPY_TUPLES_PROCESSED,
-										 ++processed);
+			table_close(scan_rel, AccessShareLock);
 		}
-
-		ExecDropSingleTupleTableSlot(slot);
-		table_endscan(scandesc);
 	}
+	else if (cstate->rel)
+		CopyThisRelTo(cstate, cstate->rel, NULL, &processed);
 	else
 	{
 		/* run the plan --- the dest receiver will send tuples */
@@ -1116,6 +1137,71 @@ DoCopyTo(CopyToState cstate)
 	return processed;
 }
 
+/*
+ * rel: the relation from which the actual data will be copied.
+ * root_rel: if not NULL, it indicates that we are copying partitioned relation
+ * data to the destination, and "rel" is the partition of "root_rel".
+ * processed: number of tuples processed.
+*/
+static void
+CopyThisRelTo(CopyToState cstate, Relation rel, Relation root_rel,
+			  uint64 *processed)
+{
+	TupleTableSlot *slot;
+	TableScanDesc scandesc;
+	AttrMap    		*map	= NULL;
+	TupleTableSlot  *root_slot = NULL;
+	TupleTableSlot  *original_slot = NULL;
+	TupleDesc		scan_tupdesc;
+	TupleDesc 		rootdesc	= NULL;
+
+	scan_tupdesc = RelationGetDescr(rel);
+	scandesc = table_beginscan(rel, GetActiveSnapshot(), 0, NULL);
+	slot = table_slot_create(rel, NULL);
+
+	/*
+	 * A partition's rowtype might differ from the root table's.
+	 * Since we are export partitioned table data here,
+	 * we must convert it back to the root table's rowtype.
+	*/
+	if (root_rel != NULL)
+	{
+		rootdesc = RelationGetDescr(root_rel);
+		map = build_attrmap_by_name_if_req(rootdesc, scan_tupdesc, false);
+	}
+
+	while (table_scan_getnextslot(scandesc, ForwardScanDirection, slot))
+	{
+		CHECK_FOR_INTERRUPTS();
+
+		/* Deconstruct the tuple ... */
+		if (map != NULL)
+		{
+			original_slot = slot;
+			root_slot = MakeSingleTupleTableSlot(rootdesc, &TTSOpsBufferHeapTuple);
+			slot = execute_attr_map_slot(map, slot, root_slot);
+		}
+		else
+			slot_getallattrs(slot);
+
+		/* Format and send the data */
+		CopyOneRowTo(cstate, slot);
+
+		/*
+		 * Increment the number of processed tuples, and report the
+		 * progress.
+		 */
+		pgstat_progress_update_param(PROGRESS_COPY_TUPLES_PROCESSED,
+									 ++(*processed));
+
+		if (original_slot != NULL)
+			ExecDropSingleTupleTableSlot(original_slot);
+	}
+
+	ExecDropSingleTupleTableSlot(slot);
+	table_endscan(scandesc);
+}
+
 /*
  * Emit one row during DoCopyTo().
  */
diff --git a/src/test/regress/expected/copy.out b/src/test/regress/expected/copy.out
index 8d5a06563c4..05e5649c1bc 100644
--- a/src/test/regress/expected/copy.out
+++ b/src/test/regress/expected/copy.out
@@ -350,3 +350,21 @@ COPY copytest_mv(id) TO stdout WITH (header);
 id
 1
 DROP MATERIALIZED VIEW copytest_mv;
+-- Tests for COPY TO with partitioned tables.
+CREATE TABLE pp (id int,val int) PARTITION BY RANGE (id);
+CREATE TABLE pp_1 (val int, id int) PARTITION BY RANGE (id);
+CREATE TABLE pp_2 (val int, id int) PARTITION BY RANGE (id);
+ALTER TABLE pp ATTACH PARTITION pp_1 FOR VALUES FROM (1) TO (5);
+ALTER TABLE pp ATTACH PARTITION pp_2 FOR VALUES FROM (5) TO (10);
+CREATE TABLE pp_15 PARTITION OF pp_1 FOR VALUES FROM (1) TO (5);
+CREATE TABLE pp_510 PARTITION OF pp_2 FOR VALUES FROM (5) TO (10);
+INSERT INTO pp SELECT g, 10 + g FROM generate_series(1,6) g;
+COPY pp TO stdout(header);
+id	val
+1	11
+2	12
+3	13
+4	14
+5	15
+6	16
+DROP TABLE PP;
diff --git a/src/test/regress/sql/copy.sql b/src/test/regress/sql/copy.sql
index f0b88a23db8..fd2627deefa 100644
--- a/src/test/regress/sql/copy.sql
+++ b/src/test/regress/sql/copy.sql
@@ -375,3 +375,18 @@ COPY copytest_mv(id) TO stdout WITH (header);
 REFRESH MATERIALIZED VIEW copytest_mv;
 COPY copytest_mv(id) TO stdout WITH (header);
 DROP MATERIALIZED VIEW copytest_mv;
+
+-- Tests for COPY TO with partitioned tables.
+CREATE TABLE pp (id int,val int) PARTITION BY RANGE (id);
+CREATE TABLE pp_1 (val int, id int) PARTITION BY RANGE (id);
+CREATE TABLE pp_2 (val int, id int) PARTITION BY RANGE (id);
+ALTER TABLE pp ATTACH PARTITION pp_1 FOR VALUES FROM (1) TO (5);
+ALTER TABLE pp ATTACH PARTITION pp_2 FOR VALUES FROM (5) TO (10);
+
+CREATE TABLE pp_15 PARTITION OF pp_1 FOR VALUES FROM (1) TO (5);
+CREATE TABLE pp_510 PARTITION OF pp_2 FOR VALUES FROM (5) TO (10);
+
+INSERT INTO pp SELECT g, 10 + g FROM generate_series(1,6) g;
+
+COPY pp TO stdout(header);
+DROP TABLE PP;
\ No newline at end of file
-- 
2.34.1

From fca7b87718264cb5ea52f3b4462f4d6e52d58cdc Mon Sep 17 00:00:00 2001
From: jian he <jian.universality@gmail.com>
Date: Thu, 5 Jun 2025 08:44:13 +0800
Subject: [PATCH v11 1/1] support COPY partitioned_table TO

this is for implementatoin of ``COPY (partitioned_table) TO``.  it will be
faster than ``COPY (select * from partitioned_table) TO``.

reivewed by: vignesh C <vignesh21@gmail.com>
reivewed by: David Rowley <dgrowleyml@gmail.com>
reivewed by: Melih Mutlu <m.melihmutlu@gmail.com>
reivewed by: Kirill Reshke <reshkekirill@gmail.com>

discussion: https://postgr.es/m/CACJufxEZt+G19Ors3bQUq-42-61__C=y5k2wk=sHEFRusu7=iQ@mail.gmail.com
commitfest entry: https://commitfest.postgresql.org/patch/5467/
---
 doc/src/sgml/ref/copy.sgml         |   6 +-
 src/backend/commands/copyto.c      | 146 +++++++++++++++++++++++------
 src/test/regress/expected/copy.out |  18 ++++
 src/test/regress/sql/copy.sql      |  15 +++
 4 files changed, 153 insertions(+), 32 deletions(-)

diff --git a/doc/src/sgml/ref/copy.sgml b/doc/src/sgml/ref/copy.sgml
index 8433344e5b6..d750a2fef7c 100644
--- a/doc/src/sgml/ref/copy.sgml
+++ b/doc/src/sgml/ref/copy.sgml
@@ -521,13 +521,13 @@ COPY <replaceable class="parameter">count</replaceable>
 
    <para>
     <command>COPY TO</command> can be used with plain
-    tables and populated materialized views.
-    For example,
+    tables, populated materialized views and partitioned tables.
+    For example, if <replaceable class="parameter">table</replaceable> is not partitioned table,
     <literal>COPY <replaceable class="parameter">table</replaceable>
     TO</literal> copies the same rows as
     <literal>SELECT * FROM ONLY <replaceable class="parameter">table</replaceable></literal>.
     However it doesn't directly support other relation types,
-    such as partitioned tables, inheritance child tables, or views.
+    such as inheritance child tables, or views.
     To copy all rows from such relations, use <literal>COPY (SELECT * FROM
     <replaceable class="parameter">table</replaceable>) TO</literal>.
    </para>
diff --git a/src/backend/commands/copyto.c b/src/backend/commands/copyto.c
index ea6f18f2c80..a718ad02960 100644
--- a/src/backend/commands/copyto.c
+++ b/src/backend/commands/copyto.c
@@ -19,6 +19,8 @@
 #include <sys/stat.h>
 
 #include "access/tableam.h"
+#include "access/table.h"
+#include "catalog/pg_inherits.h"
 #include "commands/copyapi.h"
 #include "commands/progress.h"
 #include "executor/execdesc.h"
@@ -82,6 +84,7 @@ typedef struct CopyToStateData
 	List	   *attnumlist;		/* integer list of attnums to copy */
 	char	   *filename;		/* filename, or NULL for STDOUT */
 	bool		is_program;		/* is 'filename' a program to popen? */
+	List	   *partitions;		/* oid list of partition oid for copy to */
 	copy_data_dest_cb data_dest_cb; /* function for writing data */
 
 	CopyFormatOptions opts;
@@ -116,6 +119,8 @@ static void CopyOneRowTo(CopyToState cstate, TupleTableSlot *slot);
 static void CopyAttributeOutText(CopyToState cstate, const char *string);
 static void CopyAttributeOutCSV(CopyToState cstate, const char *string,
 								bool use_quote);
+static void CopyThisRelTo(CopyToState cstate, Relation rel,
+						  Relation root_rel, uint64 *processed);
 
 /* built-in format-specific routines */
 static void CopyToTextLikeStart(CopyToState cstate, TupleDesc tupDesc);
@@ -643,6 +648,8 @@ BeginCopyTo(ParseState *pstate,
 		PROGRESS_COPY_COMMAND_TO,
 		0
 	};
+	List	   *children = NIL;
+	List	   *scan_oids = NIL;
 
 	if (rel != NULL && rel->rd_rel->relkind != RELKIND_RELATION)
 	{
@@ -673,11 +680,35 @@ BeginCopyTo(ParseState *pstate,
 					 errmsg("cannot copy from sequence \"%s\"",
 							RelationGetRelationName(rel))));
 		else if (rel->rd_rel->relkind == RELKIND_PARTITIONED_TABLE)
-			ereport(ERROR,
-					(errcode(ERRCODE_WRONG_OBJECT_TYPE),
-					 errmsg("cannot copy from partitioned table \"%s\"",
-							RelationGetRelationName(rel)),
-					 errhint("Try the COPY (SELECT ...) TO variant.")));
+		{
+			children = find_all_inheritors(RelationGetRelid(rel),
+										   AccessShareLock,
+										   NULL);
+
+			foreach_oid(childreloid, children)
+			{
+				char		 relkind = get_rel_relkind(childreloid);
+
+				if (relkind == RELKIND_FOREIGN_TABLE)
+				{
+					char	   *relation_name;
+
+					relation_name = get_rel_name(childreloid);
+					ereport(ERROR,
+							errcode(ERRCODE_WRONG_OBJECT_TYPE),
+							errmsg("cannot copy from foreign table \"%s\"", relation_name),
+							errdetail("Partition \"%s\" is a foreign table in the partitioned table \"%s.%s\"",
+									  relation_name, RelationGetRelationName(rel),
+									  get_namespace_name(rel->rd_rel->relnamespace)),
+							errhint("Try the COPY (SELECT ...) TO variant."));
+				}
+
+				if (RELKIND_HAS_PARTITIONS(relkind))
+					continue;
+
+				scan_oids = lappend_oid(scan_oids, childreloid);
+			}
+		}
 		else
 			ereport(ERROR,
 					(errcode(ERRCODE_WRONG_OBJECT_TYPE),
@@ -713,6 +744,7 @@ BeginCopyTo(ParseState *pstate,
 		cstate->rel = rel;
 
 		tupDesc = RelationGetDescr(cstate->rel);
+		cstate->partitions = list_copy(scan_oids);
 	}
 	else
 	{
@@ -722,6 +754,7 @@ BeginCopyTo(ParseState *pstate,
 		DestReceiver *dest;
 
 		cstate->rel = NULL;
+		cstate->partitions = NIL;
 
 		/*
 		 * Run parse analysis and rewrite.  Note this also acquires sufficient
@@ -1030,7 +1063,7 @@ DoCopyTo(CopyToState cstate)
 	TupleDesc	tupDesc;
 	int			num_phys_attrs;
 	ListCell   *cur;
-	uint64		processed;
+	uint64		processed = 0;
 
 	if (fe_copy)
 		SendCopyBegin(cstate);
@@ -1068,36 +1101,25 @@ DoCopyTo(CopyToState cstate)
 
 	cstate->routine->CopyToStart(cstate, tupDesc);
 
-	if (cstate->rel)
+	/*
+	 * if COPY TO source table is a partitioned table, then open each
+	 * partition and process each individual partition.
+	 */
+	if (cstate->rel && cstate->rel->rd_rel->relkind == RELKIND_PARTITIONED_TABLE)
 	{
-		TupleTableSlot *slot;
-		TableScanDesc scandesc;
-
-		scandesc = table_beginscan(cstate->rel, GetActiveSnapshot(), 0, NULL);
-		slot = table_slot_create(cstate->rel, NULL);
-
-		processed = 0;
-		while (table_scan_getnextslot(scandesc, ForwardScanDirection, slot))
+		foreach_oid(scan_oid, cstate->partitions)
 		{
-			CHECK_FOR_INTERRUPTS();
+			Relation		scan_rel;
 
-			/* Deconstruct the tuple ... */
-			slot_getallattrs(slot);
+			scan_rel = table_open(scan_oid, AccessShareLock);
 
-			/* Format and send the data */
-			CopyOneRowTo(cstate, slot);
+			CopyThisRelTo(cstate, scan_rel, cstate->rel, &processed);
 
-			/*
-			 * Increment the number of processed tuples, and report the
-			 * progress.
-			 */
-			pgstat_progress_update_param(PROGRESS_COPY_TUPLES_PROCESSED,
-										 ++processed);
+			table_close(scan_rel, AccessShareLock);
 		}
-
-		ExecDropSingleTupleTableSlot(slot);
-		table_endscan(scandesc);
 	}
+	else if (cstate->rel)
+		CopyThisRelTo(cstate, cstate->rel, NULL, &processed);
 	else
 	{
 		/* run the plan --- the dest receiver will send tuples */
@@ -1115,6 +1137,72 @@ DoCopyTo(CopyToState cstate)
 	return processed;
 }
 
+/*
+ * rel: the relation from which the actual data will be copied.
+ * root_rel: if not NULL, it indicates that we are copying partitioned relation
+ * data to the destination, and "rel" is the partition of "root_rel".
+ * processed: number of tuples processed.
+*/
+static void
+CopyThisRelTo(CopyToState cstate, Relation rel, Relation root_rel,
+			  uint64 *processed)
+{
+	TupleTableSlot *slot;
+	TableScanDesc scandesc;
+	AttrMap    		*map	= NULL;
+	TupleTableSlot  *root_slot = NULL;
+	TupleDesc		tupdesc;
+	TupleDesc 		rootdesc;
+
+	tupdesc = RelationGetDescr(rel);
+	scandesc = table_beginscan(rel, GetActiveSnapshot(), 0, NULL);
+	slot = table_slot_create(rel, NULL);
+
+	/*
+	 * A partition's rowtype might differ from the root table's.  Since we are
+	 * export partitioned table data here, we must convert it back to the root
+	 * table's rowtype.
+	*/
+	if (root_rel != NULL)
+	{
+		rootdesc = RelationGetDescr(root_rel);
+		root_slot = table_slot_create(root_rel, NULL);
+		map = build_attrmap_by_name_if_req(rootdesc, tupdesc, false);
+	}
+
+	while (table_scan_getnextslot(scandesc, ForwardScanDirection, slot))
+	{
+		TupleTableSlot *copyslot;
+
+		CHECK_FOR_INTERRUPTS();
+
+		/* Deconstruct the tuple ... */
+		if (map != NULL)
+			copyslot = execute_attr_map_slot(map, slot, root_slot);
+		else
+		{
+			slot_getallattrs(slot);
+			copyslot = slot;
+		}
+
+		/* Format and send the data */
+		CopyOneRowTo(cstate, copyslot);
+
+		/*
+		 * Increment the number of processed tuples, and report the
+		 * progress.
+		 */
+		pgstat_progress_update_param(PROGRESS_COPY_TUPLES_PROCESSED,
+									 ++(*processed));
+	}
+
+	ExecDropSingleTupleTableSlot(slot);
+
+	if (root_slot != NULL)
+		ExecDropSingleTupleTableSlot(root_slot);
+	table_endscan(scandesc);
+}
+
 /*
  * Emit one row during DoCopyTo().
  */
diff --git a/src/test/regress/expected/copy.out b/src/test/regress/expected/copy.out
index 8d5a06563c4..05e5649c1bc 100644
--- a/src/test/regress/expected/copy.out
+++ b/src/test/regress/expected/copy.out
@@ -350,3 +350,21 @@ COPY copytest_mv(id) TO stdout WITH (header);
 id
 1
 DROP MATERIALIZED VIEW copytest_mv;
+-- Tests for COPY TO with partitioned tables.
+CREATE TABLE pp (id int,val int) PARTITION BY RANGE (id);
+CREATE TABLE pp_1 (val int, id int) PARTITION BY RANGE (id);
+CREATE TABLE pp_2 (val int, id int) PARTITION BY RANGE (id);
+ALTER TABLE pp ATTACH PARTITION pp_1 FOR VALUES FROM (1) TO (5);
+ALTER TABLE pp ATTACH PARTITION pp_2 FOR VALUES FROM (5) TO (10);
+CREATE TABLE pp_15 PARTITION OF pp_1 FOR VALUES FROM (1) TO (5);
+CREATE TABLE pp_510 PARTITION OF pp_2 FOR VALUES FROM (5) TO (10);
+INSERT INTO pp SELECT g, 10 + g FROM generate_series(1,6) g;
+COPY pp TO stdout(header);
+id	val
+1	11
+2	12
+3	13
+4	14
+5	15
+6	16
+DROP TABLE PP;
diff --git a/src/test/regress/sql/copy.sql b/src/test/regress/sql/copy.sql
index f0b88a23db8..fd2627deefa 100644
--- a/src/test/regress/sql/copy.sql
+++ b/src/test/regress/sql/copy.sql
@@ -375,3 +375,18 @@ COPY copytest_mv(id) TO stdout WITH (header);
 REFRESH MATERIALIZED VIEW copytest_mv;
 COPY copytest_mv(id) TO stdout WITH (header);
 DROP MATERIALIZED VIEW copytest_mv;
+
+-- Tests for COPY TO with partitioned tables.
+CREATE TABLE pp (id int,val int) PARTITION BY RANGE (id);
+CREATE TABLE pp_1 (val int, id int) PARTITION BY RANGE (id);
+CREATE TABLE pp_2 (val int, id int) PARTITION BY RANGE (id);
+ALTER TABLE pp ATTACH PARTITION pp_1 FOR VALUES FROM (1) TO (5);
+ALTER TABLE pp ATTACH PARTITION pp_2 FOR VALUES FROM (5) TO (10);
+
+CREATE TABLE pp_15 PARTITION OF pp_1 FOR VALUES FROM (1) TO (5);
+CREATE TABLE pp_510 PARTITION OF pp_2 FOR VALUES FROM (5) TO (10);
+
+INSERT INTO pp SELECT g, 10 + g FROM generate_series(1,6) g;
+
+COPY pp TO stdout(header);
+DROP TABLE PP;
\ No newline at end of file
-- 
2.34.1

From 9257c14d08c0c0a53262cf3d7be70dfc2cfa62df Mon Sep 17 00:00:00 2001
From: jian he <jian.universality@gmail.com>
Date: Fri, 27 Jun 2025 15:12:24 +0800
Subject: [PATCH v12 1/1] support COPY partitioned_table TO

this is for implementatoin of ``COPY (partitioned_table) TO``.  it will be
faster than ``COPY (select * from partitioned_table) TO``.

If destination table is a partitioned table, COPY table TO copies the same rows
as SELECT * FROM table.

reivewed by: vignesh C <vignesh21@gmail.com>
reivewed by: David Rowley <dgrowleyml@gmail.com>
reivewed by: Melih Mutlu <m.melihmutlu@gmail.com>
reivewed by: Kirill Reshke <reshkekirill@gmail.com>
reivewed by: Atsushi Torikoshi <torikoshia@oss.nttdata.com>

discussion: https://postgr.es/m/CACJufxEZt+G19Ors3bQUq-42-61__C=y5k2wk=sHEFRusu7=iQ@mail.gmail.com
commitfest entry: https://commitfest.postgresql.org/patch/5467
---
 doc/src/sgml/ref/copy.sgml         |   9 +-
 src/backend/commands/copyto.c      | 152 +++++++++++++++++++++++------
 src/test/regress/expected/copy.out |  18 ++++
 src/test/regress/sql/copy.sql      |  15 +++
 4 files changed, 160 insertions(+), 34 deletions(-)

diff --git a/doc/src/sgml/ref/copy.sgml b/doc/src/sgml/ref/copy.sgml
index 8433344e5b6..0775a799a5e 100644
--- a/doc/src/sgml/ref/copy.sgml
+++ b/doc/src/sgml/ref/copy.sgml
@@ -521,13 +521,16 @@ COPY <replaceable class="parameter">count</replaceable>
 
    <para>
     <command>COPY TO</command> can be used with plain
-    tables and populated materialized views.
-    For example,
+    tables, populated materialized views and partitioned tables.
+    For example, if <replaceable class="parameter">table</replaceable> is not partitioned table,
     <literal>COPY <replaceable class="parameter">table</replaceable>
     TO</literal> copies the same rows as
     <literal>SELECT * FROM ONLY <replaceable class="parameter">table</replaceable></literal>.
+    If <replaceable class="parameter">table</replaceable> is a partitioned table,
+    <literal>COPY <replaceable class="parameter">table</replaceable> TO</literal>
+    copies the same rows as <literal>SELECT * FROM <replaceable class="parameter">table</replaceable></literal>.
     However it doesn't directly support other relation types,
-    such as partitioned tables, inheritance child tables, or views.
+    such as inheritance child tables, or views.
     To copy all rows from such relations, use <literal>COPY (SELECT * FROM
     <replaceable class="parameter">table</replaceable>) TO</literal>.
    </para>
diff --git a/src/backend/commands/copyto.c b/src/backend/commands/copyto.c
index ea6f18f2c80..fbfe6d926d0 100644
--- a/src/backend/commands/copyto.c
+++ b/src/backend/commands/copyto.c
@@ -19,6 +19,8 @@
 #include <sys/stat.h>
 
 #include "access/tableam.h"
+#include "access/table.h"
+#include "catalog/pg_inherits.h"
 #include "commands/copyapi.h"
 #include "commands/progress.h"
 #include "executor/execdesc.h"
@@ -82,6 +84,7 @@ typedef struct CopyToStateData
 	List	   *attnumlist;		/* integer list of attnums to copy */
 	char	   *filename;		/* filename, or NULL for STDOUT */
 	bool		is_program;		/* is 'filename' a program to popen? */
+	List	   *partitions;		/* oid list of partition oid for copy to */
 	copy_data_dest_cb data_dest_cb; /* function for writing data */
 
 	CopyFormatOptions opts;
@@ -116,6 +119,8 @@ static void CopyOneRowTo(CopyToState cstate, TupleTableSlot *slot);
 static void CopyAttributeOutText(CopyToState cstate, const char *string);
 static void CopyAttributeOutCSV(CopyToState cstate, const char *string,
 								bool use_quote);
+static void CopyThisRelTo(CopyToState cstate, Relation rel,
+						  Relation root_rel, uint64 *processed);
 
 /* built-in format-specific routines */
 static void CopyToTextLikeStart(CopyToState cstate, TupleDesc tupDesc);
@@ -643,6 +648,8 @@ BeginCopyTo(ParseState *pstate,
 		PROGRESS_COPY_COMMAND_TO,
 		0
 	};
+	List	   *children = NIL;
+	List	   *scan_oids = NIL;
 
 	if (rel != NULL && rel->rd_rel->relkind != RELKIND_RELATION)
 	{
@@ -673,11 +680,35 @@ BeginCopyTo(ParseState *pstate,
 					 errmsg("cannot copy from sequence \"%s\"",
 							RelationGetRelationName(rel))));
 		else if (rel->rd_rel->relkind == RELKIND_PARTITIONED_TABLE)
-			ereport(ERROR,
-					(errcode(ERRCODE_WRONG_OBJECT_TYPE),
-					 errmsg("cannot copy from partitioned table \"%s\"",
-							RelationGetRelationName(rel)),
-					 errhint("Try the COPY (SELECT ...) TO variant.")));
+		{
+			children = find_all_inheritors(RelationGetRelid(rel),
+										   AccessShareLock,
+										   NULL);
+
+			foreach_oid(childreloid, children)
+			{
+				char		 relkind = get_rel_relkind(childreloid);
+
+				if (relkind == RELKIND_FOREIGN_TABLE)
+				{
+					char	   *relation_name;
+
+					relation_name = get_rel_name(childreloid);
+					ereport(ERROR,
+							errcode(ERRCODE_WRONG_OBJECT_TYPE),
+							errmsg("cannot copy from foreign table \"%s\"", relation_name),
+							errdetail("Partition \"%s\" is a foreign table in the partitioned table \"%s.%s\"",
+									  relation_name, RelationGetRelationName(rel),
+									  get_namespace_name(rel->rd_rel->relnamespace)),
+							errhint("Try the COPY (SELECT ...) TO variant."));
+				}
+
+				if (RELKIND_HAS_PARTITIONS(relkind))
+					continue;
+
+				scan_oids = lappend_oid(scan_oids, childreloid);
+			}
+		}
 		else
 			ereport(ERROR,
 					(errcode(ERRCODE_WRONG_OBJECT_TYPE),
@@ -713,6 +744,7 @@ BeginCopyTo(ParseState *pstate,
 		cstate->rel = rel;
 
 		tupDesc = RelationGetDescr(cstate->rel);
+		cstate->partitions = list_copy(scan_oids);
 	}
 	else
 	{
@@ -722,6 +754,7 @@ BeginCopyTo(ParseState *pstate,
 		DestReceiver *dest;
 
 		cstate->rel = NULL;
+		cstate->partitions = NIL;
 
 		/*
 		 * Run parse analysis and rewrite.  Note this also acquires sufficient
@@ -1030,7 +1063,7 @@ DoCopyTo(CopyToState cstate)
 	TupleDesc	tupDesc;
 	int			num_phys_attrs;
 	ListCell   *cur;
-	uint64		processed;
+	uint64		processed = 0;
 
 	if (fe_copy)
 		SendCopyBegin(cstate);
@@ -1068,36 +1101,23 @@ DoCopyTo(CopyToState cstate)
 
 	cstate->routine->CopyToStart(cstate, tupDesc);
 
-	if (cstate->rel)
+	/*
+	 * If COPY TO source table is a partitioned table, then open each
+	 * partition and process each individual partition.
+	 */
+	if (cstate->rel && cstate->rel->rd_rel->relkind == RELKIND_PARTITIONED_TABLE)
 	{
-		TupleTableSlot *slot;
-		TableScanDesc scandesc;
-
-		scandesc = table_beginscan(cstate->rel, GetActiveSnapshot(), 0, NULL);
-		slot = table_slot_create(cstate->rel, NULL);
-
-		processed = 0;
-		while (table_scan_getnextslot(scandesc, ForwardScanDirection, slot))
+		foreach_oid(scan_oid, cstate->partitions)
 		{
-			CHECK_FOR_INTERRUPTS();
+			Relation		scan_rel;
 
-			/* Deconstruct the tuple ... */
-			slot_getallattrs(slot);
-
-			/* Format and send the data */
-			CopyOneRowTo(cstate, slot);
-
-			/*
-			 * Increment the number of processed tuples, and report the
-			 * progress.
-			 */
-			pgstat_progress_update_param(PROGRESS_COPY_TUPLES_PROCESSED,
-										 ++processed);
+			scan_rel = table_open(scan_oid, NoLock);
+			CopyThisRelTo(cstate, scan_rel, cstate->rel, &processed);
+			table_close(scan_rel, NoLock);
 		}
-
-		ExecDropSingleTupleTableSlot(slot);
-		table_endscan(scandesc);
 	}
+	else if (cstate->rel)
+		CopyThisRelTo(cstate, cstate->rel, NULL, &processed);
 	else
 	{
 		/* run the plan --- the dest receiver will send tuples */
@@ -1115,6 +1135,76 @@ DoCopyTo(CopyToState cstate)
 	return processed;
 }
 
+/*
+ * CopyThisRelTo:
+ * This will scanning a single table (which may be a partition) and exporting
+ * its rows to a COPY destination.
+ *
+ * rel: the relation from which the actual data will be copied.
+ * root_rel: if not NULL, it indicates that we are copying partitioned relation
+ * data to the destination, and "rel" is the partition of "root_rel".
+ * processed: number of tuples processed.
+*/
+static void
+CopyThisRelTo(CopyToState cstate, Relation rel, Relation root_rel,
+			  uint64 *processed)
+{
+	TupleTableSlot *slot;
+	TableScanDesc scandesc;
+	AttrMap    		*map	= NULL;
+	TupleTableSlot  *root_slot = NULL;
+	TupleDesc		tupdesc;
+	TupleDesc 		rootdesc;
+
+	tupdesc = RelationGetDescr(rel);
+	scandesc = table_beginscan(rel, GetActiveSnapshot(), 0, NULL);
+	slot = table_slot_create(rel, NULL);
+
+	/*
+	 * A partition's rowtype might differ from the root table's.  Since we are
+	 * exporting partitioned table data here, we must convert it back to the
+	 * root table's rowtype.
+	*/
+	if (root_rel != NULL)
+	{
+		rootdesc = RelationGetDescr(root_rel);
+		root_slot = table_slot_create(root_rel, NULL);
+		map = build_attrmap_by_name_if_req(rootdesc, tupdesc, false);
+	}
+
+	while (table_scan_getnextslot(scandesc, ForwardScanDirection, slot))
+	{
+		TupleTableSlot *copyslot;
+
+		CHECK_FOR_INTERRUPTS();
+
+		/* Deconstruct the tuple ... */
+		if (map != NULL)
+			copyslot = execute_attr_map_slot(map, slot, root_slot);
+		else
+		{
+			slot_getallattrs(slot);
+			copyslot = slot;
+		}
+
+		/* Format and send the data */
+		CopyOneRowTo(cstate, copyslot);
+
+		/*
+		 * Increment the number of processed tuples, and report the
+		 * progress.
+		 */
+		pgstat_progress_update_param(PROGRESS_COPY_TUPLES_PROCESSED,
+									 ++(*processed));
+	}
+
+	ExecDropSingleTupleTableSlot(slot);
+
+	if (root_slot != NULL)
+		ExecDropSingleTupleTableSlot(root_slot);
+	table_endscan(scandesc);
+}
+
 /*
  * Emit one row during DoCopyTo().
  */
diff --git a/src/test/regress/expected/copy.out b/src/test/regress/expected/copy.out
index 8d5a06563c4..05e5649c1bc 100644
--- a/src/test/regress/expected/copy.out
+++ b/src/test/regress/expected/copy.out
@@ -350,3 +350,21 @@ COPY copytest_mv(id) TO stdout WITH (header);
 id
 1
 DROP MATERIALIZED VIEW copytest_mv;
+-- Tests for COPY TO with partitioned tables.
+CREATE TABLE pp (id int,val int) PARTITION BY RANGE (id);
+CREATE TABLE pp_1 (val int, id int) PARTITION BY RANGE (id);
+CREATE TABLE pp_2 (val int, id int) PARTITION BY RANGE (id);
+ALTER TABLE pp ATTACH PARTITION pp_1 FOR VALUES FROM (1) TO (5);
+ALTER TABLE pp ATTACH PARTITION pp_2 FOR VALUES FROM (5) TO (10);
+CREATE TABLE pp_15 PARTITION OF pp_1 FOR VALUES FROM (1) TO (5);
+CREATE TABLE pp_510 PARTITION OF pp_2 FOR VALUES FROM (5) TO (10);
+INSERT INTO pp SELECT g, 10 + g FROM generate_series(1,6) g;
+COPY pp TO stdout(header);
+id	val
+1	11
+2	12
+3	13
+4	14
+5	15
+6	16
+DROP TABLE PP;
diff --git a/src/test/regress/sql/copy.sql b/src/test/regress/sql/copy.sql
index f0b88a23db8..9be7cb6c8dc 100644
--- a/src/test/regress/sql/copy.sql
+++ b/src/test/regress/sql/copy.sql
@@ -375,3 +375,18 @@ COPY copytest_mv(id) TO stdout WITH (header);
 REFRESH MATERIALIZED VIEW copytest_mv;
 COPY copytest_mv(id) TO stdout WITH (header);
 DROP MATERIALIZED VIEW copytest_mv;
+
+-- Tests for COPY TO with partitioned tables.
+CREATE TABLE pp (id int,val int) PARTITION BY RANGE (id);
+CREATE TABLE pp_1 (val int, id int) PARTITION BY RANGE (id);
+CREATE TABLE pp_2 (val int, id int) PARTITION BY RANGE (id);
+ALTER TABLE pp ATTACH PARTITION pp_1 FOR VALUES FROM (1) TO (5);
+ALTER TABLE pp ATTACH PARTITION pp_2 FOR VALUES FROM (5) TO (10);
+
+CREATE TABLE pp_15 PARTITION OF pp_1 FOR VALUES FROM (1) TO (5);
+CREATE TABLE pp_510 PARTITION OF pp_2 FOR VALUES FROM (5) TO (10);
+
+INSERT INTO pp SELECT g, 10 + g FROM generate_series(1,6) g;
+
+COPY pp TO stdout(header);
+DROP TABLE PP;
-- 
2.34.1

From 26eb0aa22c091d9f3de6db0433b5202ebe564bdd Mon Sep 17 00:00:00 2001
From: jian he <jian.universality@gmail.com>
Date: Wed, 2 Jul 2025 12:07:19 +0800
Subject: [PATCH v13 1/1] support COPY partitioned_table TO

This is for implementation of ``COPY (partitioned_table) TO``.  it will be
faster than ``COPY (select * from partitioned_table) TO``.
If the destination table is a partitioned table, COPY table TO copies the same
rows as SELECT * FROM table.

reivewed by: vignesh C <vignesh21@gmail.com>
reivewed by: David Rowley <dgrowleyml@gmail.com>
reivewed by: Melih Mutlu <m.melihmutlu@gmail.com>
reivewed by: Kirill Reshke <reshkekirill@gmail.com>
reivewed by: Atsushi Torikoshi <torikoshia@oss.nttdata.com>

discussion: https://postgr.es/m/CACJufxEZt+G19Ors3bQUq-42-61__C=y5k2wk=sHEFRusu7=iQ@mail.gmail.com
commitfest entry: https://commitfest.postgresql.org/patch/5467
---
 .../postgres_fdw/expected/postgres_fdw.out    |   8 +
 contrib/postgres_fdw/sql/postgres_fdw.sql     |   4 +
 doc/src/sgml/ref/copy.sgml                    |   9 +-
 src/backend/commands/copyto.c                 | 151 ++++++++++++++----
 src/test/regress/expected/copy.out            |  18 +++
 src/test/regress/sql/copy.sql                 |  15 ++
 6 files changed, 171 insertions(+), 34 deletions(-)

diff --git a/contrib/postgres_fdw/expected/postgres_fdw.out b/contrib/postgres_fdw/expected/postgres_fdw.out
index 2185b42bb4f..05f64157832 100644
--- a/contrib/postgres_fdw/expected/postgres_fdw.out
+++ b/contrib/postgres_fdw/expected/postgres_fdw.out
@@ -11475,6 +11475,14 @@ SELECT * FROM result_tbl ORDER BY a;
 (3 rows)
 
 DELETE FROM result_tbl;
+-- Test COPY TO with a foreign table or when the foreign table is a partition
+COPY async_p3 TO stdout; --error
+ERROR:  cannot copy from foreign table "async_p3"
+HINT:  Try the COPY (SELECT ...) TO variant.
+COPY async_pt TO stdout; --error
+ERROR:  cannot copy from foreign table "async_p1"
+DETAIL:  Partition "async_p1" is a foreign table in the partitioned table "async_pt"
+HINT:  Try the COPY (SELECT ...) TO variant.
 DROP FOREIGN TABLE async_p3;
 DROP TABLE base_tbl3;
 -- Check case where the partitioned table has local/remote partitions
diff --git a/contrib/postgres_fdw/sql/postgres_fdw.sql b/contrib/postgres_fdw/sql/postgres_fdw.sql
index e534b40de3c..d11105a20dc 100644
--- a/contrib/postgres_fdw/sql/postgres_fdw.sql
+++ b/contrib/postgres_fdw/sql/postgres_fdw.sql
@@ -3872,6 +3872,10 @@ INSERT INTO result_tbl SELECT * FROM async_pt WHERE b === 505;
 SELECT * FROM result_tbl ORDER BY a;
 DELETE FROM result_tbl;
 
+-- Test COPY TO with a foreign table or when the foreign table is a partition
+COPY async_p3 TO stdout; --error
+COPY async_pt TO stdout; --error
+
 DROP FOREIGN TABLE async_p3;
 DROP TABLE base_tbl3;
 
diff --git a/doc/src/sgml/ref/copy.sgml b/doc/src/sgml/ref/copy.sgml
index 8433344e5b6..0775a799a5e 100644
--- a/doc/src/sgml/ref/copy.sgml
+++ b/doc/src/sgml/ref/copy.sgml
@@ -521,13 +521,16 @@ COPY <replaceable class="parameter">count</replaceable>
 
    <para>
     <command>COPY TO</command> can be used with plain
-    tables and populated materialized views.
-    For example,
+    tables, populated materialized views and partitioned tables.
+    For example, if <replaceable class="parameter">table</replaceable> is not partitioned table,
     <literal>COPY <replaceable class="parameter">table</replaceable>
     TO</literal> copies the same rows as
     <literal>SELECT * FROM ONLY <replaceable class="parameter">table</replaceable></literal>.
+    If <replaceable class="parameter">table</replaceable> is a partitioned table,
+    <literal>COPY <replaceable class="parameter">table</replaceable> TO</literal>
+    copies the same rows as <literal>SELECT * FROM <replaceable class="parameter">table</replaceable></literal>.
     However it doesn't directly support other relation types,
-    such as partitioned tables, inheritance child tables, or views.
+    such as inheritance child tables, or views.
     To copy all rows from such relations, use <literal>COPY (SELECT * FROM
     <replaceable class="parameter">table</replaceable>) TO</literal>.
    </para>
diff --git a/src/backend/commands/copyto.c b/src/backend/commands/copyto.c
index ea6f18f2c80..ca25cba2a15 100644
--- a/src/backend/commands/copyto.c
+++ b/src/backend/commands/copyto.c
@@ -19,6 +19,8 @@
 #include <sys/stat.h>
 
 #include "access/tableam.h"
+#include "access/table.h"
+#include "catalog/pg_inherits.h"
 #include "commands/copyapi.h"
 #include "commands/progress.h"
 #include "executor/execdesc.h"
@@ -82,6 +84,7 @@ typedef struct CopyToStateData
 	List	   *attnumlist;		/* integer list of attnums to copy */
 	char	   *filename;		/* filename, or NULL for STDOUT */
 	bool		is_program;		/* is 'filename' a program to popen? */
+	List	   *partitions;		/* oid list of partition oid for copy to */
 	copy_data_dest_cb data_dest_cb; /* function for writing data */
 
 	CopyFormatOptions opts;
@@ -116,6 +119,8 @@ static void CopyOneRowTo(CopyToState cstate, TupleTableSlot *slot);
 static void CopyAttributeOutText(CopyToState cstate, const char *string);
 static void CopyAttributeOutCSV(CopyToState cstate, const char *string,
 								bool use_quote);
+static void CopyRelTo(CopyToState cstate, Relation rel, Relation root_rel,
+					  uint64 *processed);
 
 /* built-in format-specific routines */
 static void CopyToTextLikeStart(CopyToState cstate, TupleDesc tupDesc);
@@ -643,6 +648,8 @@ BeginCopyTo(ParseState *pstate,
 		PROGRESS_COPY_COMMAND_TO,
 		0
 	};
+	List	   *children = NIL;
+	List	   *scan_oids = NIL;
 
 	if (rel != NULL && rel->rd_rel->relkind != RELKIND_RELATION)
 	{
@@ -673,11 +680,34 @@ BeginCopyTo(ParseState *pstate,
 					 errmsg("cannot copy from sequence \"%s\"",
 							RelationGetRelationName(rel))));
 		else if (rel->rd_rel->relkind == RELKIND_PARTITIONED_TABLE)
-			ereport(ERROR,
-					(errcode(ERRCODE_WRONG_OBJECT_TYPE),
-					 errmsg("cannot copy from partitioned table \"%s\"",
-							RelationGetRelationName(rel)),
-					 errhint("Try the COPY (SELECT ...) TO variant.")));
+		{
+			children = find_all_inheritors(RelationGetRelid(rel),
+										   AccessShareLock,
+										   NULL);
+
+			foreach_oid(childreloid, children)
+			{
+				char		 relkind = get_rel_relkind(childreloid);
+
+				if (relkind == RELKIND_FOREIGN_TABLE)
+				{
+					char	   *relation_name;
+
+					relation_name = get_rel_name(childreloid);
+					ereport(ERROR,
+							errcode(ERRCODE_WRONG_OBJECT_TYPE),
+							errmsg("cannot copy from foreign table \"%s\"", relation_name),
+							errdetail("Partition \"%s\" is a foreign table in the partitioned table \"%s\"",
+									  relation_name, RelationGetRelationName(rel)),
+							errhint("Try the COPY (SELECT ...) TO variant."));
+				}
+
+				if (RELKIND_HAS_PARTITIONS(relkind))
+					continue;
+
+				scan_oids = lappend_oid(scan_oids, childreloid);
+			}
+		}
 		else
 			ereport(ERROR,
 					(errcode(ERRCODE_WRONG_OBJECT_TYPE),
@@ -713,6 +743,7 @@ BeginCopyTo(ParseState *pstate,
 		cstate->rel = rel;
 
 		tupDesc = RelationGetDescr(cstate->rel);
+		cstate->partitions = list_copy(scan_oids);
 	}
 	else
 	{
@@ -722,6 +753,7 @@ BeginCopyTo(ParseState *pstate,
 		DestReceiver *dest;
 
 		cstate->rel = NULL;
+		cstate->partitions = NIL;
 
 		/*
 		 * Run parse analysis and rewrite.  Note this also acquires sufficient
@@ -1030,7 +1062,7 @@ DoCopyTo(CopyToState cstate)
 	TupleDesc	tupDesc;
 	int			num_phys_attrs;
 	ListCell   *cur;
-	uint64		processed;
+	uint64		processed = 0;
 
 	if (fe_copy)
 		SendCopyBegin(cstate);
@@ -1068,36 +1100,24 @@ DoCopyTo(CopyToState cstate)
 
 	cstate->routine->CopyToStart(cstate, tupDesc);
 
-	if (cstate->rel)
+	/*
+	 * If COPY TO source table is a partitioned table, then open each
+	 * partition and process each individual partition.
+	 */
+	if (cstate->rel && cstate->rel->rd_rel->relkind == RELKIND_PARTITIONED_TABLE)
 	{
-		TupleTableSlot *slot;
-		TableScanDesc scandesc;
-
-		scandesc = table_beginscan(cstate->rel, GetActiveSnapshot(), 0, NULL);
-		slot = table_slot_create(cstate->rel, NULL);
-
-		processed = 0;
-		while (table_scan_getnextslot(scandesc, ForwardScanDirection, slot))
+		foreach_oid(scan_oid, cstate->partitions)
 		{
-			CHECK_FOR_INTERRUPTS();
+			Relation		scan_rel;
 
-			/* Deconstruct the tuple ... */
-			slot_getallattrs(slot);
-
-			/* Format and send the data */
-			CopyOneRowTo(cstate, slot);
-
-			/*
-			 * Increment the number of processed tuples, and report the
-			 * progress.
-			 */
-			pgstat_progress_update_param(PROGRESS_COPY_TUPLES_PROCESSED,
-										 ++processed);
+			/* We already got the needed lock in BeginCopyTo */
+			scan_rel = table_open(scan_oid, NoLock);
+			CopyRelTo(cstate, scan_rel, cstate->rel, &processed);
+			table_close(scan_rel, NoLock);
 		}
-
-		ExecDropSingleTupleTableSlot(slot);
-		table_endscan(scandesc);
 	}
+	else if (cstate->rel)
+		CopyRelTo(cstate, cstate->rel, NULL, &processed);
 	else
 	{
 		/* run the plan --- the dest receiver will send tuples */
@@ -1115,6 +1135,75 @@ DoCopyTo(CopyToState cstate)
 	return processed;
 }
 
+/*
+ * Scan a single table (which may be a partition) and export its rows to the
+ * COPY destination.
+ *
+ * rel: the relation from which the actual data will be copied.
+ * root_rel: if not NULL, it indicates that we are copying partitioned relation
+ * data to the destination, and "rel" is the partition of "root_rel".
+ * processed: number of tuples processed.
+*/
+static void
+CopyRelTo(CopyToState cstate, Relation rel, Relation root_rel,
+		  uint64 *processed)
+{
+	TupleTableSlot *slot;
+	TableScanDesc scandesc;
+	AttrMap    		*map	= NULL;
+	TupleTableSlot  *root_slot = NULL;
+	TupleDesc		tupdesc;
+	TupleDesc 		rootdesc;
+
+	tupdesc = RelationGetDescr(rel);
+	scandesc = table_beginscan(rel, GetActiveSnapshot(), 0, NULL);
+	slot = table_slot_create(rel, NULL);
+
+	/*
+	 * A partition's rowtype might differ from the root table's.  Since we are
+	 * exporting partitioned table data here, we must convert it back to the
+	 * root table's rowtype.
+	*/
+	if (root_rel != NULL)
+	{
+		rootdesc = RelationGetDescr(root_rel);
+		root_slot = table_slot_create(root_rel, NULL);
+		map = build_attrmap_by_name_if_req(rootdesc, tupdesc, false);
+	}
+
+	while (table_scan_getnextslot(scandesc, ForwardScanDirection, slot))
+	{
+		TupleTableSlot *copyslot;
+
+		CHECK_FOR_INTERRUPTS();
+
+		/* Deconstruct the tuple ... */
+		if (map != NULL)
+			copyslot = execute_attr_map_slot(map, slot, root_slot);
+		else
+		{
+			slot_getallattrs(slot);
+			copyslot = slot;
+		}
+
+		/* Format and send the data */
+		CopyOneRowTo(cstate, copyslot);
+
+		/*
+		 * Increment the number of processed tuples, and report the
+		 * progress.
+		 */
+		pgstat_progress_update_param(PROGRESS_COPY_TUPLES_PROCESSED,
+									 ++(*processed));
+	}
+
+	ExecDropSingleTupleTableSlot(slot);
+
+	if (root_slot != NULL)
+		ExecDropSingleTupleTableSlot(root_slot);
+	table_endscan(scandesc);
+}
+
 /*
  * Emit one row during DoCopyTo().
  */
diff --git a/src/test/regress/expected/copy.out b/src/test/regress/expected/copy.out
index 8d5a06563c4..05e5649c1bc 100644
--- a/src/test/regress/expected/copy.out
+++ b/src/test/regress/expected/copy.out
@@ -350,3 +350,21 @@ COPY copytest_mv(id) TO stdout WITH (header);
 id
 1
 DROP MATERIALIZED VIEW copytest_mv;
+-- Tests for COPY TO with partitioned tables.
+CREATE TABLE pp (id int,val int) PARTITION BY RANGE (id);
+CREATE TABLE pp_1 (val int, id int) PARTITION BY RANGE (id);
+CREATE TABLE pp_2 (val int, id int) PARTITION BY RANGE (id);
+ALTER TABLE pp ATTACH PARTITION pp_1 FOR VALUES FROM (1) TO (5);
+ALTER TABLE pp ATTACH PARTITION pp_2 FOR VALUES FROM (5) TO (10);
+CREATE TABLE pp_15 PARTITION OF pp_1 FOR VALUES FROM (1) TO (5);
+CREATE TABLE pp_510 PARTITION OF pp_2 FOR VALUES FROM (5) TO (10);
+INSERT INTO pp SELECT g, 10 + g FROM generate_series(1,6) g;
+COPY pp TO stdout(header);
+id	val
+1	11
+2	12
+3	13
+4	14
+5	15
+6	16
+DROP TABLE PP;
diff --git a/src/test/regress/sql/copy.sql b/src/test/regress/sql/copy.sql
index f0b88a23db8..9be7cb6c8dc 100644
--- a/src/test/regress/sql/copy.sql
+++ b/src/test/regress/sql/copy.sql
@@ -375,3 +375,18 @@ COPY copytest_mv(id) TO stdout WITH (header);
 REFRESH MATERIALIZED VIEW copytest_mv;
 COPY copytest_mv(id) TO stdout WITH (header);
 DROP MATERIALIZED VIEW copytest_mv;
+
+-- Tests for COPY TO with partitioned tables.
+CREATE TABLE pp (id int,val int) PARTITION BY RANGE (id);
+CREATE TABLE pp_1 (val int, id int) PARTITION BY RANGE (id);
+CREATE TABLE pp_2 (val int, id int) PARTITION BY RANGE (id);
+ALTER TABLE pp ATTACH PARTITION pp_1 FOR VALUES FROM (1) TO (5);
+ALTER TABLE pp ATTACH PARTITION pp_2 FOR VALUES FROM (5) TO (10);
+
+CREATE TABLE pp_15 PARTITION OF pp_1 FOR VALUES FROM (1) TO (5);
+CREATE TABLE pp_510 PARTITION OF pp_2 FOR VALUES FROM (5) TO (10);
+
+INSERT INTO pp SELECT g, 10 + g FROM generate_series(1,6) g;
+
+COPY pp TO stdout(header);
+DROP TABLE PP;
-- 
2.34.1

From a6064e7943d791329ef6e73b48e4695f5883a8eb Mon Sep 17 00:00:00 2001
From: jian he <jian.universality@gmail.com>
Date: Tue, 15 Jul 2025 12:11:35 +0800
Subject: [PATCH v14 1/1] support COPY partitioned_table TO
MIME-Version: 1.0
Content-Type: text/plain; charset=UTF-8
Content-Transfer-Encoding: 8bit

This is for implementation of ``COPY (partitioned_table) TO``.  it will be
faster than ``COPY (select * from partitioned_table) TO``.
If the destination table is a partitioned table, COPY table TO copies the same
rows as SELECT * FROM table.

reivewed by: vignesh C <vignesh21@gmail.com>
reivewed by: David Rowley <dgrowleyml@gmail.com>
reivewed by: Melih Mutlu <m.melihmutlu@gmail.com>
reivewed by: Kirill Reshke <reshkekirill@gmail.com>
reivewed by: Atsushi Torikoshi <torikoshia@oss.nttdata.com>
reivewed by: Álvaro Herrera <alvherre@kurilemu.de>

discussion: https://postgr.es/m/CACJufxEZt+G19Ors3bQUq-42-61__C=y5k2wk=sHEFRusu7=iQ@mail.gmail.com
commitfest entry: https://commitfest.postgresql.org/patch/5467
---
 .../postgres_fdw/expected/postgres_fdw.out    |   8 +
 contrib/postgres_fdw/sql/postgres_fdw.sql     |   4 +
 doc/src/sgml/ref/copy.sgml                    |   9 +-
 src/backend/commands/copyto.c                 | 151 ++++++++++++++----
 src/test/regress/expected/copy.out            |  18 +++
 src/test/regress/sql/copy.sql                 |  15 ++
 6 files changed, 171 insertions(+), 34 deletions(-)

diff --git a/contrib/postgres_fdw/expected/postgres_fdw.out b/contrib/postgres_fdw/expected/postgres_fdw.out
index 2185b42bb4f..05f64157832 100644
--- a/contrib/postgres_fdw/expected/postgres_fdw.out
+++ b/contrib/postgres_fdw/expected/postgres_fdw.out
@@ -11475,6 +11475,14 @@ SELECT * FROM result_tbl ORDER BY a;
 (3 rows)
 
 DELETE FROM result_tbl;
+-- Test COPY TO with a foreign table or when the foreign table is a partition
+COPY async_p3 TO stdout; --error
+ERROR:  cannot copy from foreign table "async_p3"
+HINT:  Try the COPY (SELECT ...) TO variant.
+COPY async_pt TO stdout; --error
+ERROR:  cannot copy from foreign table "async_p1"
+DETAIL:  Partition "async_p1" is a foreign table in the partitioned table "async_pt"
+HINT:  Try the COPY (SELECT ...) TO variant.
 DROP FOREIGN TABLE async_p3;
 DROP TABLE base_tbl3;
 -- Check case where the partitioned table has local/remote partitions
diff --git a/contrib/postgres_fdw/sql/postgres_fdw.sql b/contrib/postgres_fdw/sql/postgres_fdw.sql
index e534b40de3c..d11105a20dc 100644
--- a/contrib/postgres_fdw/sql/postgres_fdw.sql
+++ b/contrib/postgres_fdw/sql/postgres_fdw.sql
@@ -3872,6 +3872,10 @@ INSERT INTO result_tbl SELECT * FROM async_pt WHERE b === 505;
 SELECT * FROM result_tbl ORDER BY a;
 DELETE FROM result_tbl;
 
+-- Test COPY TO with a foreign table or when the foreign table is a partition
+COPY async_p3 TO stdout; --error
+COPY async_pt TO stdout; --error
+
 DROP FOREIGN TABLE async_p3;
 DROP TABLE base_tbl3;
 
diff --git a/doc/src/sgml/ref/copy.sgml b/doc/src/sgml/ref/copy.sgml
index c2d1fbc1fbe..f91bc9740ec 100644
--- a/doc/src/sgml/ref/copy.sgml
+++ b/doc/src/sgml/ref/copy.sgml
@@ -539,13 +539,16 @@ COPY <replaceable class="parameter">count</replaceable>
 
    <para>
     <command>COPY TO</command> can be used with plain
-    tables and populated materialized views.
-    For example,
+    tables, populated materialized views and partitioned tables.
+    For example, if <replaceable class="parameter">table</replaceable> is not partitioned table,
     <literal>COPY <replaceable class="parameter">table</replaceable>
     TO</literal> copies the same rows as
     <literal>SELECT * FROM ONLY <replaceable class="parameter">table</replaceable></literal>.
+    If <replaceable class="parameter">table</replaceable> is a partitioned table,
+    <literal>COPY <replaceable class="parameter">table</replaceable> TO</literal>
+    copies the same rows as <literal>SELECT * FROM <replaceable class="parameter">table</replaceable></literal>.
     However it doesn't directly support other relation types,
-    such as partitioned tables, inheritance child tables, or views.
+    such as inheritance child tables, or views.
     To copy all rows from such relations, use <literal>COPY (SELECT * FROM
     <replaceable class="parameter">table</replaceable>) TO</literal>.
    </para>
diff --git a/src/backend/commands/copyto.c b/src/backend/commands/copyto.c
index 67b94b91cae..fa61032491e 100644
--- a/src/backend/commands/copyto.c
+++ b/src/backend/commands/copyto.c
@@ -19,6 +19,8 @@
 #include <sys/stat.h>
 
 #include "access/tableam.h"
+#include "access/table.h"
+#include "catalog/pg_inherits.h"
 #include "commands/copyapi.h"
 #include "commands/progress.h"
 #include "executor/execdesc.h"
@@ -82,6 +84,7 @@ typedef struct CopyToStateData
 	List	   *attnumlist;		/* integer list of attnums to copy */
 	char	   *filename;		/* filename, or NULL for STDOUT */
 	bool		is_program;		/* is 'filename' a program to popen? */
+	List	   *partitions;		/* oid list of partition oid for copy to */
 	copy_data_dest_cb data_dest_cb; /* function for writing data */
 
 	CopyFormatOptions opts;
@@ -116,6 +119,8 @@ static void CopyOneRowTo(CopyToState cstate, TupleTableSlot *slot);
 static void CopyAttributeOutText(CopyToState cstate, const char *string);
 static void CopyAttributeOutCSV(CopyToState cstate, const char *string,
 								bool use_quote);
+static void CopyRelTo(CopyToState cstate, Relation rel, Relation root_rel,
+					  uint64 *processed);
 
 /* built-in format-specific routines */
 static void CopyToTextLikeStart(CopyToState cstate, TupleDesc tupDesc);
@@ -643,6 +648,8 @@ BeginCopyTo(ParseState *pstate,
 		PROGRESS_COPY_COMMAND_TO,
 		0
 	};
+	List	   *children = NIL;
+	List	   *scan_oids = NIL;
 
 	if (rel != NULL && rel->rd_rel->relkind != RELKIND_RELATION)
 	{
@@ -673,11 +680,34 @@ BeginCopyTo(ParseState *pstate,
 					 errmsg("cannot copy from sequence \"%s\"",
 							RelationGetRelationName(rel))));
 		else if (rel->rd_rel->relkind == RELKIND_PARTITIONED_TABLE)
-			ereport(ERROR,
-					(errcode(ERRCODE_WRONG_OBJECT_TYPE),
-					 errmsg("cannot copy from partitioned table \"%s\"",
-							RelationGetRelationName(rel)),
-					 errhint("Try the COPY (SELECT ...) TO variant.")));
+		{
+			children = find_all_inheritors(RelationGetRelid(rel),
+										   AccessShareLock,
+										   NULL);
+
+			foreach_oid(childreloid, children)
+			{
+				char		 relkind = get_rel_relkind(childreloid);
+
+				if (relkind == RELKIND_FOREIGN_TABLE)
+				{
+					char	   *relation_name;
+
+					relation_name = get_rel_name(childreloid);
+					ereport(ERROR,
+							errcode(ERRCODE_WRONG_OBJECT_TYPE),
+							errmsg("cannot copy from foreign table \"%s\"", relation_name),
+							errdetail("Partition \"%s\" is a foreign table in the partitioned table \"%s\"",
+									  relation_name, RelationGetRelationName(rel)),
+							errhint("Try the COPY (SELECT ...) TO variant."));
+				}
+
+				if (RELKIND_HAS_PARTITIONS(relkind))
+					continue;
+
+				scan_oids = lappend_oid(scan_oids, childreloid);
+			}
+		}
 		else
 			ereport(ERROR,
 					(errcode(ERRCODE_WRONG_OBJECT_TYPE),
@@ -713,6 +743,7 @@ BeginCopyTo(ParseState *pstate,
 		cstate->rel = rel;
 
 		tupDesc = RelationGetDescr(cstate->rel);
+		cstate->partitions = list_copy(scan_oids);
 	}
 	else
 	{
@@ -722,6 +753,7 @@ BeginCopyTo(ParseState *pstate,
 		DestReceiver *dest;
 
 		cstate->rel = NULL;
+		cstate->partitions = NIL;
 
 		/*
 		 * Run parse analysis and rewrite.  Note this also acquires sufficient
@@ -1030,7 +1062,7 @@ DoCopyTo(CopyToState cstate)
 	TupleDesc	tupDesc;
 	int			num_phys_attrs;
 	ListCell   *cur;
-	uint64		processed;
+	uint64		processed = 0;
 
 	if (fe_copy)
 		SendCopyBegin(cstate);
@@ -1068,36 +1100,24 @@ DoCopyTo(CopyToState cstate)
 
 	cstate->routine->CopyToStart(cstate, tupDesc);
 
-	if (cstate->rel)
+	/*
+	 * If COPY TO source table is a partitioned table, then open each
+	 * partition and process each individual partition.
+	 */
+	if (cstate->rel && cstate->rel->rd_rel->relkind == RELKIND_PARTITIONED_TABLE)
 	{
-		TupleTableSlot *slot;
-		TableScanDesc scandesc;
-
-		scandesc = table_beginscan(cstate->rel, GetActiveSnapshot(), 0, NULL);
-		slot = table_slot_create(cstate->rel, NULL);
-
-		processed = 0;
-		while (table_scan_getnextslot(scandesc, ForwardScanDirection, slot))
+		foreach_oid(scan_oid, cstate->partitions)
 		{
-			CHECK_FOR_INTERRUPTS();
+			Relation		scan_rel;
 
-			/* Deconstruct the tuple ... */
-			slot_getallattrs(slot);
-
-			/* Format and send the data */
-			CopyOneRowTo(cstate, slot);
-
-			/*
-			 * Increment the number of processed tuples, and report the
-			 * progress.
-			 */
-			pgstat_progress_update_param(PROGRESS_COPY_TUPLES_PROCESSED,
-										 ++processed);
+			/* We already got the needed lock in BeginCopyTo */
+			scan_rel = table_open(scan_oid, NoLock);
+			CopyRelTo(cstate, scan_rel, cstate->rel, &processed);
+			table_close(scan_rel, NoLock);
 		}
-
-		ExecDropSingleTupleTableSlot(slot);
-		table_endscan(scandesc);
 	}
+	else if (cstate->rel)
+		CopyRelTo(cstate, cstate->rel, NULL, &processed);
 	else
 	{
 		/* run the plan --- the dest receiver will send tuples */
@@ -1115,6 +1135,75 @@ DoCopyTo(CopyToState cstate)
 	return processed;
 }
 
+/*
+ * Scan a single table (which may be a partition) and export its rows to the
+ * COPY destination.
+ *
+ * rel: the table from which the actual data will be copied.
+ * root_rel: if not NULL, it indicates that COPY TO command copy partitioned
+ * table data to the destination, and "rel" is the partition of "root_rel".
+ * processed: number of tuples processed.
+*/
+static void
+CopyRelTo(CopyToState cstate, Relation rel, Relation root_rel,
+		  uint64 *processed)
+{
+	TupleTableSlot *slot;
+	TableScanDesc scandesc;
+	AttrMap    		*map	= NULL;
+	TupleTableSlot  *root_slot = NULL;
+	TupleDesc		tupdesc;
+	TupleDesc 		rootdesc;
+
+	tupdesc = RelationGetDescr(rel);
+	scandesc = table_beginscan(rel, GetActiveSnapshot(), 0, NULL);
+	slot = table_slot_create(rel, NULL);
+
+	/*
+	 * A partition's rowtype might differ from the root table's. If we are
+	 * exporting partition data here, we must convert it back to the root
+	 * table's rowtype.
+	*/
+	if (root_rel != NULL)
+	{
+		rootdesc = RelationGetDescr(root_rel);
+		root_slot = table_slot_create(root_rel, NULL);
+		map = build_attrmap_by_name_if_req(rootdesc, tupdesc, false);
+	}
+
+	while (table_scan_getnextslot(scandesc, ForwardScanDirection, slot))
+	{
+		TupleTableSlot *copyslot;
+
+		CHECK_FOR_INTERRUPTS();
+
+		/* Deconstruct the tuple ... */
+		if (map != NULL)
+			copyslot = execute_attr_map_slot(map, slot, root_slot);
+		else
+		{
+			slot_getallattrs(slot);
+			copyslot = slot;
+		}
+
+		/* Format and send the data */
+		CopyOneRowTo(cstate, copyslot);
+
+		/*
+		 * Increment the number of processed tuples, and report the
+		 * progress.
+		 */
+		pgstat_progress_update_param(PROGRESS_COPY_TUPLES_PROCESSED,
+									 ++(*processed));
+	}
+
+	ExecDropSingleTupleTableSlot(slot);
+
+	if (root_slot != NULL)
+		ExecDropSingleTupleTableSlot(root_slot);
+	table_endscan(scandesc);
+}
+
 /*
  * Emit one row during DoCopyTo().
  */
diff --git a/src/test/regress/expected/copy.out b/src/test/regress/expected/copy.out
index ac66eb55aee..3bf5ecf469e 100644
--- a/src/test/regress/expected/copy.out
+++ b/src/test/regress/expected/copy.out
@@ -373,3 +373,21 @@ COPY copytest_mv(id) TO stdout WITH (header);
 id
 1
 DROP MATERIALIZED VIEW copytest_mv;
+-- Tests for COPY TO with partitioned tables.
+CREATE TABLE pp (id int,val int) PARTITION BY RANGE (id);
+CREATE TABLE pp_1 (val int, id int) PARTITION BY RANGE (id);
+CREATE TABLE pp_2 (val int, id int) PARTITION BY RANGE (id);
+ALTER TABLE pp ATTACH PARTITION pp_1 FOR VALUES FROM (1) TO (5);
+ALTER TABLE pp ATTACH PARTITION pp_2 FOR VALUES FROM (5) TO (10);
+CREATE TABLE pp_15 PARTITION OF pp_1 FOR VALUES FROM (1) TO (5);
+CREATE TABLE pp_510 PARTITION OF pp_2 FOR VALUES FROM (5) TO (10);
+INSERT INTO pp SELECT g, 10 + g FROM generate_series(1,6) g;
+COPY pp TO stdout(header);
+id	val
+1	11
+2	12
+3	13
+4	14
+5	15
+6	16
+DROP TABLE PP;
diff --git a/src/test/regress/sql/copy.sql b/src/test/regress/sql/copy.sql
index a1316c73bac..3d84764c65f 100644
--- a/src/test/regress/sql/copy.sql
+++ b/src/test/regress/sql/copy.sql
@@ -405,3 +405,18 @@ COPY copytest_mv(id) TO stdout WITH (header);
 REFRESH MATERIALIZED VIEW copytest_mv;
 COPY copytest_mv(id) TO stdout WITH (header);
 DROP MATERIALIZED VIEW copytest_mv;
+
+-- Tests for COPY TO with partitioned tables.
+CREATE TABLE pp (id int,val int) PARTITION BY RANGE (id);
+CREATE TABLE pp_1 (val int, id int) PARTITION BY RANGE (id);
+CREATE TABLE pp_2 (val int, id int) PARTITION BY RANGE (id);
+ALTER TABLE pp ATTACH PARTITION pp_1 FOR VALUES FROM (1) TO (5);
+ALTER TABLE pp ATTACH PARTITION pp_2 FOR VALUES FROM (5) TO (10);
+
+CREATE TABLE pp_15 PARTITION OF pp_1 FOR VALUES FROM (1) TO (5);
+CREATE TABLE pp_510 PARTITION OF pp_2 FOR VALUES FROM (5) TO (10);
+
+INSERT INTO pp SELECT g, 10 + g FROM generate_series(1,6) g;
+
+COPY pp TO stdout(header);
+DROP TABLE PP;
-- 
2.34.1

From bdfc161494ce6a5fedfaa901fde7dba0dcb79c2c Mon Sep 17 00:00:00 2001
From: jian he <jian.universality@gmail.com>
Date: Mon, 6 Oct 2025 17:39:57 +0800
Subject: [PATCH v15 1/1] support COPY partitioned_table TO
MIME-Version: 1.0
Content-Type: text/plain; charset=UTF-8
Content-Transfer-Encoding: 8bit

This is for implementation of ``COPY (partitioned_table) TO``.  it will be
faster than ``COPY (select * from partitioned_table) TO``.
If the destination table is a partitioned table, COPY table TO copies the same
rows as SELECT * FROM table.

reivewed by: vignesh C <vignesh21@gmail.com>
reivewed by: David Rowley <dgrowleyml@gmail.com>
reivewed by: Melih Mutlu <m.melihmutlu@gmail.com>
reivewed by: Kirill Reshke <reshkekirill@gmail.com>
reivewed by: Atsushi Torikoshi <torikoshia@oss.nttdata.com>
reivewed by: Álvaro Herrera <alvherre@kurilemu.de>

discussion: https://postgr.es/m/CACJufxEZt+G19Ors3bQUq-42-61__C=y5k2wk=sHEFRusu7=iQ@mail.gmail.com
commitfest entry: https://commitfest.postgresql.org/patch/5467
---
 .../postgres_fdw/expected/postgres_fdw.out    |   8 +
 contrib/postgres_fdw/sql/postgres_fdw.sql     |   4 +
 doc/src/sgml/ref/copy.sgml                    |   9 +-
 src/backend/commands/copyto.c                 | 152 ++++++++++++++----
 src/test/regress/expected/copy.out            |  18 +++
 src/test/regress/sql/copy.sql                 |  15 ++
 6 files changed, 172 insertions(+), 34 deletions(-)

diff --git a/contrib/postgres_fdw/expected/postgres_fdw.out b/contrib/postgres_fdw/expected/postgres_fdw.out
index 6dc04e916dc..99827de2dd8 100644
--- a/contrib/postgres_fdw/expected/postgres_fdw.out
+++ b/contrib/postgres_fdw/expected/postgres_fdw.out
@@ -11596,6 +11596,14 @@ SELECT * FROM result_tbl ORDER BY a;
 (3 rows)
 
 DELETE FROM result_tbl;
+-- Test COPY TO with a foreign table or when the foreign table is a partition
+COPY async_p3 TO stdout; --error
+ERROR:  cannot copy from foreign table "async_p3"
+HINT:  Try the COPY (SELECT ...) TO variant.
+COPY async_pt TO stdout; --error
+ERROR:  cannot copy from foreign table "async_p1"
+DETAIL:  Partition "async_p1" is a foreign table in the partitioned table "async_pt"
+HINT:  Try the COPY (SELECT ...) TO variant.
 DROP FOREIGN TABLE async_p3;
 DROP TABLE base_tbl3;
 -- Check case where the partitioned table has local/remote partitions
diff --git a/contrib/postgres_fdw/sql/postgres_fdw.sql b/contrib/postgres_fdw/sql/postgres_fdw.sql
index 3b7da128519..8a672f05039 100644
--- a/contrib/postgres_fdw/sql/postgres_fdw.sql
+++ b/contrib/postgres_fdw/sql/postgres_fdw.sql
@@ -3941,6 +3941,10 @@ INSERT INTO result_tbl SELECT * FROM async_pt WHERE b === 505;
 SELECT * FROM result_tbl ORDER BY a;
 DELETE FROM result_tbl;
 
+-- Test COPY TO with a foreign table or when the foreign table is a partition
+COPY async_p3 TO stdout; --error
+COPY async_pt TO stdout; --error
+
 DROP FOREIGN TABLE async_p3;
 DROP TABLE base_tbl3;
 
diff --git a/doc/src/sgml/ref/copy.sgml b/doc/src/sgml/ref/copy.sgml
index c2d1fbc1fbe..ecd300097fc 100644
--- a/doc/src/sgml/ref/copy.sgml
+++ b/doc/src/sgml/ref/copy.sgml
@@ -539,13 +539,16 @@ COPY <replaceable class="parameter">count</replaceable>
 
    <para>
     <command>COPY TO</command> can be used with plain
-    tables and populated materialized views.
-    For example,
+    tables, populated materialized views and partitioned tables.
+    For example, if <replaceable class="parameter">table</replaceable> is a plain table,
     <literal>COPY <replaceable class="parameter">table</replaceable>
     TO</literal> copies the same rows as
     <literal>SELECT * FROM ONLY <replaceable class="parameter">table</replaceable></literal>.
+    If <replaceable class="parameter">table</replaceable> is a partitioned table or a materialized view
+    <literal>COPY <replaceable class="parameter">table</replaceable> TO</literal>
+    copies the same rows as <literal>SELECT * FROM <replaceable class="parameter">table</replaceable></literal>.
     However it doesn't directly support other relation types,
-    such as partitioned tables, inheritance child tables, or views.
+    such as inheritance child tables, or views.
     To copy all rows from such relations, use <literal>COPY (SELECT * FROM
     <replaceable class="parameter">table</replaceable>) TO</literal>.
    </para>
diff --git a/src/backend/commands/copyto.c b/src/backend/commands/copyto.c
index 67b94b91cae..73f54ed5f62 100644
--- a/src/backend/commands/copyto.c
+++ b/src/backend/commands/copyto.c
@@ -19,6 +19,8 @@
 #include <sys/stat.h>
 
 #include "access/tableam.h"
+#include "access/table.h"
+#include "catalog/pg_inherits.h"
 #include "commands/copyapi.h"
 #include "commands/progress.h"
 #include "executor/execdesc.h"
@@ -82,6 +84,7 @@ typedef struct CopyToStateData
 	List	   *attnumlist;		/* integer list of attnums to copy */
 	char	   *filename;		/* filename, or NULL for STDOUT */
 	bool		is_program;		/* is 'filename' a program to popen? */
+	List	   *partitions;		/* oid list of partition oid for copy to */
 	copy_data_dest_cb data_dest_cb; /* function for writing data */
 
 	CopyFormatOptions opts;
@@ -116,6 +119,8 @@ static void CopyOneRowTo(CopyToState cstate, TupleTableSlot *slot);
 static void CopyAttributeOutText(CopyToState cstate, const char *string);
 static void CopyAttributeOutCSV(CopyToState cstate, const char *string,
 								bool use_quote);
+static void CopyRelTo(CopyToState cstate, Relation rel, Relation root_rel,
+					  uint64 *processed);
 
 /* built-in format-specific routines */
 static void CopyToTextLikeStart(CopyToState cstate, TupleDesc tupDesc);
@@ -643,6 +648,7 @@ BeginCopyTo(ParseState *pstate,
 		PROGRESS_COPY_COMMAND_TO,
 		0
 	};
+	List	   *scan_oids = NIL;
 
 	if (rel != NULL && rel->rd_rel->relkind != RELKIND_RELATION)
 	{
@@ -673,11 +679,36 @@ BeginCopyTo(ParseState *pstate,
 					 errmsg("cannot copy from sequence \"%s\"",
 							RelationGetRelationName(rel))));
 		else if (rel->rd_rel->relkind == RELKIND_PARTITIONED_TABLE)
-			ereport(ERROR,
-					(errcode(ERRCODE_WRONG_OBJECT_TYPE),
-					 errmsg("cannot copy from partitioned table \"%s\"",
-							RelationGetRelationName(rel)),
-					 errhint("Try the COPY (SELECT ...) TO variant.")));
+		{
+			List	   *children = NIL;
+
+			children = find_all_inheritors(RelationGetRelid(rel),
+										   AccessShareLock,
+										   NULL);
+
+			foreach_oid(childreloid, children)
+			{
+				char		 relkind = get_rel_relkind(childreloid);
+
+				if (relkind == RELKIND_FOREIGN_TABLE)
+				{
+					char	   *relation_name;
+
+					relation_name = get_rel_name(childreloid);
+					ereport(ERROR,
+							errcode(ERRCODE_WRONG_OBJECT_TYPE),
+							errmsg("cannot copy from foreign table \"%s\"", relation_name),
+							errdetail("Partition \"%s\" is a foreign table in the partitioned table \"%s\"",
+									  relation_name, RelationGetRelationName(rel)),
+							errhint("Try the COPY (SELECT ...) TO variant."));
+				}
+
+				if (RELKIND_HAS_PARTITIONS(relkind))
+					continue;
+
+				scan_oids = lappend_oid(scan_oids, childreloid);
+			}
+		}
 		else
 			ereport(ERROR,
 					(errcode(ERRCODE_WRONG_OBJECT_TYPE),
@@ -713,6 +744,7 @@ BeginCopyTo(ParseState *pstate,
 		cstate->rel = rel;
 
 		tupDesc = RelationGetDescr(cstate->rel);
+		cstate->partitions = list_copy(scan_oids);
 	}
 	else
 	{
@@ -722,6 +754,7 @@ BeginCopyTo(ParseState *pstate,
 		DestReceiver *dest;
 
 		cstate->rel = NULL;
+		cstate->partitions = NIL;
 
 		/*
 		 * Run parse analysis and rewrite.  Note this also acquires sufficient
@@ -1030,7 +1063,7 @@ DoCopyTo(CopyToState cstate)
 	TupleDesc	tupDesc;
 	int			num_phys_attrs;
 	ListCell   *cur;
-	uint64		processed;
+	uint64		processed = 0;
 
 	if (fe_copy)
 		SendCopyBegin(cstate);
@@ -1068,36 +1101,24 @@ DoCopyTo(CopyToState cstate)
 
 	cstate->routine->CopyToStart(cstate, tupDesc);
 
-	if (cstate->rel)
+	/*
+	 * If COPY TO source table is a partitioned table, then open each
+	 * partition and process each individual partition.
+	 */
+	if (cstate->rel && cstate->rel->rd_rel->relkind == RELKIND_PARTITIONED_TABLE)
 	{
-		TupleTableSlot *slot;
-		TableScanDesc scandesc;
-
-		scandesc = table_beginscan(cstate->rel, GetActiveSnapshot(), 0, NULL);
-		slot = table_slot_create(cstate->rel, NULL);
-
-		processed = 0;
-		while (table_scan_getnextslot(scandesc, ForwardScanDirection, slot))
+		foreach_oid(scan_oid, cstate->partitions)
 		{
-			CHECK_FOR_INTERRUPTS();
+			Relation		scan_rel;
 
-			/* Deconstruct the tuple ... */
-			slot_getallattrs(slot);
-
-			/* Format and send the data */
-			CopyOneRowTo(cstate, slot);
-
-			/*
-			 * Increment the number of processed tuples, and report the
-			 * progress.
-			 */
-			pgstat_progress_update_param(PROGRESS_COPY_TUPLES_PROCESSED,
-										 ++processed);
+			/* We already got the needed lock in BeginCopyTo */
+			scan_rel = table_open(scan_oid, NoLock);
+			CopyRelTo(cstate, scan_rel, cstate->rel, &processed);
+			table_close(scan_rel, NoLock);
 		}
-
-		ExecDropSingleTupleTableSlot(slot);
-		table_endscan(scandesc);
 	}
+	else if (cstate->rel)
+		CopyRelTo(cstate, cstate->rel, NULL, &processed);
 	else
 	{
 		/* run the plan --- the dest receiver will send tuples */
@@ -1115,6 +1136,75 @@ DoCopyTo(CopyToState cstate)
 	return processed;
 }
 
+/*
+ * Scan a single table (which may be a partition) and export its rows to the
+ * COPY destination.
+ *
+ * rel: the table from which the actual data will be copied.
+ * root_rel: if not NULL, it indicates that COPY TO command copy partitioned
+ * table data to the destination, and "rel" is the partition of "root_rel".
+ * processed: number of tuples processed.
+*/
+static void
+CopyRelTo(CopyToState cstate, Relation rel, Relation root_rel,
+		  uint64 *processed)
+{
+	TupleTableSlot *slot;
+	TableScanDesc scandesc;
+	AttrMap    		*map	= NULL;
+	TupleTableSlot  *root_slot = NULL;
+	TupleDesc		tupdesc;
+	TupleDesc 		rootdesc;
+
+	tupdesc = RelationGetDescr(rel);
+	scandesc = table_beginscan(rel, GetActiveSnapshot(), 0, NULL);
+	slot = table_slot_create(rel, NULL);
+
+	/*
+	 * A partition's rowtype might differ from the root table's. If we are
+	 * exporting partition data here, we must convert it back to the root
+	 * table's rowtype.
+	*/
+	if (root_rel != NULL)
+	{
+		rootdesc = RelationGetDescr(root_rel);
+		root_slot = table_slot_create(root_rel, NULL);
+		map = build_attrmap_by_name_if_req(rootdesc, tupdesc, false);
+	}
+
+	while (table_scan_getnextslot(scandesc, ForwardScanDirection, slot))
+	{
+		TupleTableSlot *copyslot;
+
+		CHECK_FOR_INTERRUPTS();
+
+		/* Deconstruct the tuple ... */
+		if (map != NULL)
+			copyslot = execute_attr_map_slot(map, slot, root_slot);
+		else
+		{
+			slot_getallattrs(slot);
+			copyslot = slot;
+		}
+
+		/* Format and send the data */
+		CopyOneRowTo(cstate, copyslot);
+
+		/*
+		 * Increment the number of processed tuples, and report the
+		 * progress.
+		 */
+		pgstat_progress_update_param(PROGRESS_COPY_TUPLES_PROCESSED,
+									 ++(*processed));
+	}
+
+	ExecDropSingleTupleTableSlot(slot);
+
+	if (root_slot != NULL)
+		ExecDropSingleTupleTableSlot(root_slot);
+	table_endscan(scandesc);
+}
+
 /*
  * Emit one row during DoCopyTo().
  */
diff --git a/src/test/regress/expected/copy.out b/src/test/regress/expected/copy.out
index ac66eb55aee..3bf5ecf469e 100644
--- a/src/test/regress/expected/copy.out
+++ b/src/test/regress/expected/copy.out
@@ -373,3 +373,21 @@ COPY copytest_mv(id) TO stdout WITH (header);
 id
 1
 DROP MATERIALIZED VIEW copytest_mv;
+-- Tests for COPY TO with partitioned tables.
+CREATE TABLE pp (id int,val int) PARTITION BY RANGE (id);
+CREATE TABLE pp_1 (val int, id int) PARTITION BY RANGE (id);
+CREATE TABLE pp_2 (val int, id int) PARTITION BY RANGE (id);
+ALTER TABLE pp ATTACH PARTITION pp_1 FOR VALUES FROM (1) TO (5);
+ALTER TABLE pp ATTACH PARTITION pp_2 FOR VALUES FROM (5) TO (10);
+CREATE TABLE pp_15 PARTITION OF pp_1 FOR VALUES FROM (1) TO (5);
+CREATE TABLE pp_510 PARTITION OF pp_2 FOR VALUES FROM (5) TO (10);
+INSERT INTO pp SELECT g, 10 + g FROM generate_series(1,6) g;
+COPY pp TO stdout(header);
+id	val
+1	11
+2	12
+3	13
+4	14
+5	15
+6	16
+DROP TABLE PP;
diff --git a/src/test/regress/sql/copy.sql b/src/test/regress/sql/copy.sql
index a1316c73bac..3d84764c65f 100644
--- a/src/test/regress/sql/copy.sql
+++ b/src/test/regress/sql/copy.sql
@@ -405,3 +405,18 @@ COPY copytest_mv(id) TO stdout WITH (header);
 REFRESH MATERIALIZED VIEW copytest_mv;
 COPY copytest_mv(id) TO stdout WITH (header);
 DROP MATERIALIZED VIEW copytest_mv;
+
+-- Tests for COPY TO with partitioned tables.
+CREATE TABLE pp (id int,val int) PARTITION BY RANGE (id);
+CREATE TABLE pp_1 (val int, id int) PARTITION BY RANGE (id);
+CREATE TABLE pp_2 (val int, id int) PARTITION BY RANGE (id);
+ALTER TABLE pp ATTACH PARTITION pp_1 FOR VALUES FROM (1) TO (5);
+ALTER TABLE pp ATTACH PARTITION pp_2 FOR VALUES FROM (5) TO (10);
+
+CREATE TABLE pp_15 PARTITION OF pp_1 FOR VALUES FROM (1) TO (5);
+CREATE TABLE pp_510 PARTITION OF pp_2 FOR VALUES FROM (5) TO (10);
+
+INSERT INTO pp SELECT g, 10 + g FROM generate_series(1,6) g;
+
+COPY pp TO stdout(header);
+DROP TABLE PP;
-- 
2.34.1

From a9189e99050a0a9387df797d3a0bfb11afed5887 Mon Sep 17 00:00:00 2001
From: jian he <jian.universality@gmail.com>
Date: Thu, 9 Oct 2025 15:05:52 +0800
Subject: [PATCH v16 1/1] support COPY partitioned_table TO
MIME-Version: 1.0
Content-Type: text/plain; charset=UTF-8
Content-Transfer-Encoding: 8bit

This is for implementation of ``COPY (partitioned_table) TO``.  it will be
faster than ``COPY (select * from partitioned_table) TO``.
If the destination table is a partitioned table, COPY table TO copies the same
rows as SELECT * FROM table.

reivewed by: vignesh C <vignesh21@gmail.com>
reivewed by: David Rowley <dgrowleyml@gmail.com>
reivewed by: Melih Mutlu <m.melihmutlu@gmail.com>
reivewed by: Kirill Reshke <reshkekirill@gmail.com>
reivewed by: Atsushi Torikoshi <torikoshia@oss.nttdata.com>
reivewed by: Álvaro Herrera <alvherre@kurilemu.de>
reivewed by: Masahiko Sawada <sawada.mshk@gmail.com>

discussion: https://postgr.es/m/CACJufxEZt+G19Ors3bQUq-42-61__C=y5k2wk=sHEFRusu7=iQ@mail.gmail.com
commitfest entry: https://commitfest.postgresql.org/patch/5467
---
 .../postgres_fdw/expected/postgres_fdw.out    |   8 +
 contrib/postgres_fdw/sql/postgres_fdw.sql     |   4 +
 doc/src/sgml/ref/copy.sgml                    |   9 +-
 src/backend/commands/copyto.c                 | 155 ++++++++++++++----
 src/test/regress/expected/copy.out            |  18 ++
 src/test/regress/sql/copy.sql                 |  15 ++
 6 files changed, 176 insertions(+), 33 deletions(-)

diff --git a/contrib/postgres_fdw/expected/postgres_fdw.out b/contrib/postgres_fdw/expected/postgres_fdw.out
index 91bbd0d8c73..2f9e315fd57 100644
--- a/contrib/postgres_fdw/expected/postgres_fdw.out
+++ b/contrib/postgres_fdw/expected/postgres_fdw.out
@@ -11599,6 +11599,14 @@ SELECT * FROM result_tbl ORDER BY a;
 (3 rows)
 
 DELETE FROM result_tbl;
+-- Test COPY TO with a foreign table or when the foreign table is a partition
+COPY async_p3 TO stdout; --error
+ERROR:  cannot copy from foreign table "async_p3"
+HINT:  Try the COPY (SELECT ...) TO variant.
+COPY async_pt TO stdout; --error
+ERROR:  cannot copy from foreign table "async_p1"
+DETAIL:  Partition "async_p1" is a foreign table in the partitioned table "async_pt"
+HINT:  Try the COPY (SELECT ...) TO variant.
 DROP FOREIGN TABLE async_p3;
 DROP TABLE base_tbl3;
 -- Check case where the partitioned table has local/remote partitions
diff --git a/contrib/postgres_fdw/sql/postgres_fdw.sql b/contrib/postgres_fdw/sql/postgres_fdw.sql
index 3b7da128519..8a672f05039 100644
--- a/contrib/postgres_fdw/sql/postgres_fdw.sql
+++ b/contrib/postgres_fdw/sql/postgres_fdw.sql
@@ -3941,6 +3941,10 @@ INSERT INTO result_tbl SELECT * FROM async_pt WHERE b === 505;
 SELECT * FROM result_tbl ORDER BY a;
 DELETE FROM result_tbl;
 
+-- Test COPY TO with a foreign table or when the foreign table is a partition
+COPY async_p3 TO stdout; --error
+COPY async_pt TO stdout; --error
+
 DROP FOREIGN TABLE async_p3;
 DROP TABLE base_tbl3;
 
diff --git a/doc/src/sgml/ref/copy.sgml b/doc/src/sgml/ref/copy.sgml
index c2d1fbc1fbe..ecd300097fc 100644
--- a/doc/src/sgml/ref/copy.sgml
+++ b/doc/src/sgml/ref/copy.sgml
@@ -539,13 +539,16 @@ COPY <replaceable class="parameter">count</replaceable>
 
    <para>
     <command>COPY TO</command> can be used with plain
-    tables and populated materialized views.
-    For example,
+    tables, populated materialized views and partitioned tables.
+    For example, if <replaceable class="parameter">table</replaceable> is a plain table,
     <literal>COPY <replaceable class="parameter">table</replaceable>
     TO</literal> copies the same rows as
     <literal>SELECT * FROM ONLY <replaceable class="parameter">table</replaceable></literal>.
+    If <replaceable class="parameter">table</replaceable> is a partitioned table or a materialized view
+    <literal>COPY <replaceable class="parameter">table</replaceable> TO</literal>
+    copies the same rows as <literal>SELECT * FROM <replaceable class="parameter">table</replaceable></literal>.
     However it doesn't directly support other relation types,
-    such as partitioned tables, inheritance child tables, or views.
+    such as inheritance child tables, or views.
     To copy all rows from such relations, use <literal>COPY (SELECT * FROM
     <replaceable class="parameter">table</replaceable>) TO</literal>.
    </para>
diff --git a/src/backend/commands/copyto.c b/src/backend/commands/copyto.c
index e5781155cdf..aba76eb0173 100644
--- a/src/backend/commands/copyto.c
+++ b/src/backend/commands/copyto.c
@@ -18,7 +18,9 @@
 #include <unistd.h>
 #include <sys/stat.h>
 
+#include "access/table.h"
 #include "access/tableam.h"
+#include "catalog/pg_inherits.h"
 #include "commands/copyapi.h"
 #include "commands/progress.h"
 #include "executor/execdesc.h"
@@ -82,6 +84,7 @@ typedef struct CopyToStateData
 	List	   *attnumlist;		/* integer list of attnums to copy */
 	char	   *filename;		/* filename, or NULL for STDOUT */
 	bool		is_program;		/* is 'filename' a program to popen? */
+	List	   *partitions;		/* oid list of partition oid for copy to */
 	copy_data_dest_cb data_dest_cb; /* function for writing data */
 
 	CopyFormatOptions opts;
@@ -116,6 +119,8 @@ static void CopyOneRowTo(CopyToState cstate, TupleTableSlot *slot);
 static void CopyAttributeOutText(CopyToState cstate, const char *string);
 static void CopyAttributeOutCSV(CopyToState cstate, const char *string,
 								bool use_quote);
+static void CopyRelTo(CopyToState cstate, Relation rel, Relation root_rel,
+					  uint64 *processed);
 
 /* built-in format-specific routines */
 static void CopyToTextLikeStart(CopyToState cstate, TupleDesc tupDesc);
@@ -643,6 +648,7 @@ BeginCopyTo(ParseState *pstate,
 		PROGRESS_COPY_COMMAND_TO,
 		0
 	};
+	List	   *children = NIL;
 
 	if (rel != NULL && rel->rd_rel->relkind != RELKIND_RELATION)
 	{
@@ -673,11 +679,36 @@ BeginCopyTo(ParseState *pstate,
 					 errmsg("cannot copy from sequence \"%s\"",
 							RelationGetRelationName(rel))));
 		else if (rel->rd_rel->relkind == RELKIND_PARTITIONED_TABLE)
-			ereport(ERROR,
-					(errcode(ERRCODE_WRONG_OBJECT_TYPE),
-					 errmsg("cannot copy from partitioned table \"%s\"",
-							RelationGetRelationName(rel)),
-					 errhint("Try the COPY (SELECT ...) TO variant.")));
+		{
+			/*
+			 * Collect a list of partitions containing data, so that later
+			 * DoCopyTo can copy the data from them.
+			*/
+			children = find_all_inheritors(RelationGetRelid(rel),
+										   AccessShareLock,
+										   NULL);
+
+			foreach_oid(childreloid, children)
+			{
+				char		 relkind = get_rel_relkind(childreloid);
+
+				if (relkind == RELKIND_FOREIGN_TABLE)
+				{
+					char	   *relation_name;
+
+					relation_name = get_rel_name(childreloid);
+					ereport(ERROR,
+							errcode(ERRCODE_WRONG_OBJECT_TYPE),
+							errmsg("cannot copy from foreign table \"%s\"", relation_name),
+							errdetail("Partition \"%s\" is a foreign table in the partitioned table \"%s\"",
+									  relation_name, RelationGetRelationName(rel)),
+							errhint("Try the COPY (SELECT ...) TO variant."));
+				}
+
+				if (RELKIND_HAS_PARTITIONS(relkind))
+					children = foreach_delete_current(children, childreloid);
+			}
+		}
 		else
 			ereport(ERROR,
 					(errcode(ERRCODE_WRONG_OBJECT_TYPE),
@@ -713,6 +744,7 @@ BeginCopyTo(ParseState *pstate,
 		cstate->rel = rel;
 
 		tupDesc = RelationGetDescr(cstate->rel);
+		cstate->partitions = children;
 	}
 	else
 	{
@@ -722,6 +754,7 @@ BeginCopyTo(ParseState *pstate,
 		DestReceiver *dest;
 
 		cstate->rel = NULL;
+		cstate->partitions = NIL;
 
 		/*
 		 * Run parse analysis and rewrite.  Note this also acquires sufficient
@@ -1030,7 +1063,7 @@ DoCopyTo(CopyToState cstate)
 	TupleDesc	tupDesc;
 	int			num_phys_attrs;
 	ListCell   *cur;
-	uint64		processed;
+	uint64		processed = 0;
 
 	if (fe_copy)
 		SendCopyBegin(cstate);
@@ -1070,33 +1103,24 @@ DoCopyTo(CopyToState cstate)
 
 	if (cstate->rel)
 	{
-		TupleTableSlot *slot;
-		TableScanDesc scandesc;
-
-		scandesc = table_beginscan(cstate->rel, GetActiveSnapshot(), 0, NULL);
-		slot = table_slot_create(cstate->rel, NULL);
-
-		processed = 0;
-		while (table_scan_getnextslot(scandesc, ForwardScanDirection, slot))
+		/*
+		 * If COPY TO source table is a partitioned table, then open each
+		 * partition and process each individual partition.
+		*/
+		if (cstate->rel->rd_rel->relkind == RELKIND_PARTITIONED_TABLE)
 		{
-			CHECK_FOR_INTERRUPTS();
+			foreach_oid(scan_oid, cstate->partitions)
+			{
+				Relation		scan_rel;
 
-			/* Deconstruct the tuple ... */
-			slot_getallattrs(slot);
-
-			/* Format and send the data */
-			CopyOneRowTo(cstate, slot);
-
-			/*
-			 * Increment the number of processed tuples, and report the
-			 * progress.
-			 */
-			pgstat_progress_update_param(PROGRESS_COPY_TUPLES_PROCESSED,
-										 ++processed);
+				/* We already got the needed lock in BeginCopyTo */
+				scan_rel = table_open(scan_oid, NoLock);
+				CopyRelTo(cstate, scan_rel, cstate->rel, &processed);
+				table_close(scan_rel, NoLock);
+			}
 		}
-
-		ExecDropSingleTupleTableSlot(slot);
-		table_endscan(scandesc);
+		else
+			CopyRelTo(cstate, cstate->rel, NULL, &processed);
 	}
 	else
 	{
@@ -1115,6 +1139,77 @@ DoCopyTo(CopyToState cstate)
 	return processed;
 }
 
+/*
+ * Scan a single table (which may be a partition) and export its rows to the
+ * COPY destination.
+ *
+ * rel: the table from which the actual data will be copied.
+ * root_rel: if not NULL, it indicates that COPY TO command copy partitioned
+ * table data to the destination, and "rel" is the partition of "root_rel".
+ * processed: number of tuples processed.
+*/
+static void
+CopyRelTo(CopyToState cstate, Relation rel, Relation root_rel,
+		  uint64 *processed)
+{
+	TupleTableSlot *slot;
+	TableScanDesc scandesc;
+	AttrMap    		*map	= NULL;
+	TupleTableSlot  *root_slot = NULL;
+
+	scandesc = table_beginscan(rel, GetActiveSnapshot(), 0, NULL);
+	slot = table_slot_create(rel, NULL);
+
+	/*
+	 * A partition's rowtype might differ from the root table's. If we are
+	 * exporting partition data here, we must convert it back to the root
+	 * table's rowtype.
+	*/
+	if (root_rel != NULL)
+	{
+		root_slot = table_slot_create(root_rel, NULL);
+		map = build_attrmap_by_name_if_req(RelationGetDescr(root_rel),
+										   RelationGetDescr(rel),
+										   false);
+	}
+
+	while (table_scan_getnextslot(scandesc, ForwardScanDirection, slot))
+	{
+		TupleTableSlot *copyslot;
+
+		CHECK_FOR_INTERRUPTS();
+
+		if (map != NULL)
+			copyslot = execute_attr_map_slot(map, slot, root_slot);
+		else
+		{
+			/* Deconstruct the tuple ... */
+			slot_getallattrs(slot);
+			copyslot = slot;
+		}
+
+		/* Format and send the data */
+		CopyOneRowTo(cstate, copyslot);
+
+		/*
+		 * Increment the number of processed tuples, and report the
+		 * progress.
+		 */
+		pgstat_progress_update_param(PROGRESS_COPY_TUPLES_PROCESSED,
+									 ++(*processed));
+	}
+
+	ExecDropSingleTupleTableSlot(slot);
+
+	if (root_slot != NULL)
+		ExecDropSingleTupleTableSlot(root_slot);
+
+	if (map != NULL)
+		free_attrmap(map);
+
+	table_endscan(scandesc);
+}
+
 /*
  * Emit one row during DoCopyTo().
  */
diff --git a/src/test/regress/expected/copy.out b/src/test/regress/expected/copy.out
index ac66eb55aee..3bf5ecf469e 100644
--- a/src/test/regress/expected/copy.out
+++ b/src/test/regress/expected/copy.out
@@ -373,3 +373,21 @@ COPY copytest_mv(id) TO stdout WITH (header);
 id
 1
 DROP MATERIALIZED VIEW copytest_mv;
+-- Tests for COPY TO with partitioned tables.
+CREATE TABLE pp (id int,val int) PARTITION BY RANGE (id);
+CREATE TABLE pp_1 (val int, id int) PARTITION BY RANGE (id);
+CREATE TABLE pp_2 (val int, id int) PARTITION BY RANGE (id);
+ALTER TABLE pp ATTACH PARTITION pp_1 FOR VALUES FROM (1) TO (5);
+ALTER TABLE pp ATTACH PARTITION pp_2 FOR VALUES FROM (5) TO (10);
+CREATE TABLE pp_15 PARTITION OF pp_1 FOR VALUES FROM (1) TO (5);
+CREATE TABLE pp_510 PARTITION OF pp_2 FOR VALUES FROM (5) TO (10);
+INSERT INTO pp SELECT g, 10 + g FROM generate_series(1,6) g;
+COPY pp TO stdout(header);
+id	val
+1	11
+2	12
+3	13
+4	14
+5	15
+6	16
+DROP TABLE PP;
diff --git a/src/test/regress/sql/copy.sql b/src/test/regress/sql/copy.sql
index a1316c73bac..3d84764c65f 100644
--- a/src/test/regress/sql/copy.sql
+++ b/src/test/regress/sql/copy.sql
@@ -405,3 +405,18 @@ COPY copytest_mv(id) TO stdout WITH (header);
 REFRESH MATERIALIZED VIEW copytest_mv;
 COPY copytest_mv(id) TO stdout WITH (header);
 DROP MATERIALIZED VIEW copytest_mv;
+
+-- Tests for COPY TO with partitioned tables.
+CREATE TABLE pp (id int,val int) PARTITION BY RANGE (id);
+CREATE TABLE pp_1 (val int, id int) PARTITION BY RANGE (id);
+CREATE TABLE pp_2 (val int, id int) PARTITION BY RANGE (id);
+ALTER TABLE pp ATTACH PARTITION pp_1 FOR VALUES FROM (1) TO (5);
+ALTER TABLE pp ATTACH PARTITION pp_2 FOR VALUES FROM (5) TO (10);
+
+CREATE TABLE pp_15 PARTITION OF pp_1 FOR VALUES FROM (1) TO (5);
+CREATE TABLE pp_510 PARTITION OF pp_2 FOR VALUES FROM (5) TO (10);
+
+INSERT INTO pp SELECT g, 10 + g FROM generate_series(1,6) g;
+
+COPY pp TO stdout(header);
+DROP TABLE PP;
-- 
2.34.1

diff --git a/src/backend/commands/copyto.c b/src/backend/commands/copyto.c
index aba76eb0173..7d5067d90b5 100644
--- a/src/backend/commands/copyto.c
+++ b/src/backend/commands/copyto.c
@@ -119,8 +119,8 @@ static void CopyOneRowTo(CopyToState cstate, TupleTableSlot *slot);
 static void CopyAttributeOutText(CopyToState cstate, const char *string);
 static void CopyAttributeOutCSV(CopyToState cstate, const char *string,
 								bool use_quote);
-static void CopyRelTo(CopyToState cstate, Relation rel, Relation root_rel,
-					  uint64 *processed);
+static void CopyRelationTo(CopyToState cstate, Relation rel, Relation root_rel,
+						   uint64 *processed);
 
 /* built-in format-specific routines */
 static void CopyToTextLikeStart(CopyToState cstate, TupleDesc tupDesc);
@@ -681,32 +681,30 @@ BeginCopyTo(ParseState *pstate,
 		else if (rel->rd_rel->relkind == RELKIND_PARTITIONED_TABLE)
 		{
 			/*
-			 * Collect a list of partitions containing data, so that later
+			 * Collect OIDs of relation containing data, so that later
 			 * DoCopyTo can copy the data from them.
-			*/
-			children = find_all_inheritors(RelationGetRelid(rel),
-										   AccessShareLock,
-										   NULL);
+			 */
+			children = find_all_inheritors(RelationGetRelid(rel), AccessShareLock, NULL);
 
-			foreach_oid(childreloid, children)
+			foreach_oid(child, children)
 			{
-				char		 relkind = get_rel_relkind(childreloid);
+				char		relkind = get_rel_relkind(child);
 
 				if (relkind == RELKIND_FOREIGN_TABLE)
 				{
-					char	   *relation_name;
+					char	   *relation_name = get_rel_name(child);
 
-					relation_name = get_rel_name(childreloid);
 					ereport(ERROR,
-							errcode(ERRCODE_WRONG_OBJECT_TYPE),
-							errmsg("cannot copy from foreign table \"%s\"", relation_name),
-							errdetail("Partition \"%s\" is a foreign table in the partitioned table \"%s\"",
-									  relation_name, RelationGetRelationName(rel)),
-							errhint("Try the COPY (SELECT ...) TO variant."));
+							(errcode(ERRCODE_WRONG_OBJECT_TYPE),
+							 errmsg("cannot copy from foreign table \"%s\"", relation_name),
+							 errdetail("Partition \"%s\" is a foreign table in partitioned table \"%s\"",
+									   relation_name, RelationGetRelationName(rel)),
+							 errhint("Try the COPY (SELECT ...) TO variant.")));
 				}
 
+				/* Exclude tables with no data */
 				if (RELKIND_HAS_PARTITIONS(relkind))
-					children = foreach_delete_current(children, childreloid);
+					children = foreach_delete_current(children, child);
 			}
 		}
 		else
@@ -1106,21 +1104,21 @@ DoCopyTo(CopyToState cstate)
 		/*
 		 * If COPY TO source table is a partitioned table, then open each
 		 * partition and process each individual partition.
-		*/
+		 */
 		if (cstate->rel->rd_rel->relkind == RELKIND_PARTITIONED_TABLE)
 		{
-			foreach_oid(scan_oid, cstate->partitions)
+			foreach_oid(child, cstate->partitions)
 			{
-				Relation		scan_rel;
+				Relation	scan_rel;
 
-				/* We already got the needed lock in BeginCopyTo */
-				scan_rel = table_open(scan_oid, NoLock);
-				CopyRelTo(cstate, scan_rel, cstate->rel, &processed);
+				/* We already got the lock in BeginCopyTo */
+				scan_rel = table_open(child, NoLock);
+				CopyRelationTo(cstate, scan_rel, cstate->rel, &processed);
 				table_close(scan_rel, NoLock);
 			}
 		}
 		else
-			CopyRelTo(cstate, cstate->rel, NULL, &processed);
+			CopyRelationTo(cstate, cstate->rel, NULL, &processed);
 	}
 	else
 	{
@@ -1140,22 +1138,19 @@ DoCopyTo(CopyToState cstate)
 }
 
 /*
- * Scan a single table (which may be a partition) and export its rows to the
- * COPY destination.
+ * Scans a single table and exports its rows to the COPY destination.
  *
- * rel: the table from which the actual data will be copied.
- * root_rel: if not NULL, it indicates that COPY TO command copy partitioned
- * table data to the destination, and "rel" is the partition of "root_rel".
- * processed: number of tuples processed.
+ * root_rel can be set to the root table of rel if rel is a partition
+ * table so that we can send tuples in root_rel's rowtype, which might
+ * differ from individual partitions.
 */
 static void
-CopyRelTo(CopyToState cstate, Relation rel, Relation root_rel,
-		  uint64 *processed)
+CopyRelationTo(CopyToState cstate, Relation rel, Relation root_rel, uint64 *processed)
 {
 	TupleTableSlot *slot;
 	TableScanDesc scandesc;
-	AttrMap    		*map	= NULL;
-	TupleTableSlot  *root_slot = NULL;
+	AttrMap    *map = NULL;
+	TupleTableSlot *root_slot = NULL;
 
 	scandesc = table_beginscan(rel, GetActiveSnapshot(), 0, NULL);
 	slot = table_slot_create(rel, NULL);
@@ -1164,7 +1159,7 @@ CopyRelTo(CopyToState cstate, Relation rel, Relation root_rel,
 	 * A partition's rowtype might differ from the root table's. If we are
 	 * exporting partition data here, we must convert it back to the root
 	 * table's rowtype.
-	*/
+	 */
 	if (root_rel != NULL)
 	{
 		root_slot = table_slot_create(root_rel, NULL);
@@ -1192,8 +1187,7 @@ CopyRelTo(CopyToState cstate, Relation rel, Relation root_rel,
 		CopyOneRowTo(cstate, copyslot);
 
 		/*
-		 * Increment the number of processed tuples, and report the
-		 * progress.
+		 * Increment the number of processed tuples, and report the progress.
 		 */
 		pgstat_progress_update_param(PROGRESS_COPY_TUPLES_PROCESSED,
 									 ++(*processed));

From 63cd35bf535cbc06b09d07171cecf57ed21e89cc Mon Sep 17 00:00:00 2001
From: jian he <jian.universality@gmail.com>
Date: Fri, 10 Oct 2025 14:36:15 +0800
Subject: [PATCH v17 1/1] support COPY partitioned_table TO
MIME-Version: 1.0
Content-Type: text/plain; charset=UTF-8
Content-Transfer-Encoding: 8bit

This is for implementation of ``COPY (partitioned_table) TO``.  it will be
faster than ``COPY (select * from partitioned_table) TO``.
If the destination table is a partitioned table, COPY table TO copies the same
rows as SELECT * FROM table.

reivewed by: vignesh C <vignesh21@gmail.com>
reivewed by: David Rowley <dgrowleyml@gmail.com>
reivewed by: Melih Mutlu <m.melihmutlu@gmail.com>
reivewed by: Kirill Reshke <reshkekirill@gmail.com>
reivewed by: Atsushi Torikoshi <torikoshia@oss.nttdata.com>
reivewed by: Álvaro Herrera <alvherre@kurilemu.de>
reivewed by: Masahiko Sawada <sawada.mshk@gmail.com>
reivewed by: Chao Li <li.evan.chao@gmail.com>

discussion: https://postgr.es/m/CACJufxEZt+G19Ors3bQUq-42-61__C=y5k2wk=sHEFRusu7=iQ@mail.gmail.com
commitfest entry: https://commitfest.postgresql.org/patch/5467
---
 .../postgres_fdw/expected/postgres_fdw.out    |   8 +
 contrib/postgres_fdw/sql/postgres_fdw.sql     |   4 +
 doc/src/sgml/ref/copy.sgml                    |   9 +-
 src/backend/commands/copy.c                   |  32 ++++
 src/backend/commands/copyto.c                 | 153 ++++++++++++++----
 src/test/regress/expected/copy.out            |  18 +++
 src/test/regress/expected/rowsecurity.out     |  54 +++++++
 src/test/regress/sql/copy.sql                 |  15 ++
 src/test/regress/sql/rowsecurity.sql          |   6 +
 9 files changed, 266 insertions(+), 33 deletions(-)

diff --git a/contrib/postgres_fdw/expected/postgres_fdw.out b/contrib/postgres_fdw/expected/postgres_fdw.out
index 91bbd0d8c73..aa1329eee37 100644
--- a/contrib/postgres_fdw/expected/postgres_fdw.out
+++ b/contrib/postgres_fdw/expected/postgres_fdw.out
@@ -11599,6 +11599,14 @@ SELECT * FROM result_tbl ORDER BY a;
 (3 rows)
 
 DELETE FROM result_tbl;
+-- Test COPY TO with a foreign table or when the foreign table is a partition
+COPY async_p3 TO stdout; --error
+ERROR:  cannot copy from foreign table "async_p3"
+HINT:  Try the COPY (SELECT ...) TO variant.
+COPY async_pt TO stdout; --error
+ERROR:  cannot copy from foreign table "async_p1"
+DETAIL:  Partition "async_p1" is a foreign table in partitioned table "async_pt"
+HINT:  Try the COPY (SELECT ...) TO variant.
 DROP FOREIGN TABLE async_p3;
 DROP TABLE base_tbl3;
 -- Check case where the partitioned table has local/remote partitions
diff --git a/contrib/postgres_fdw/sql/postgres_fdw.sql b/contrib/postgres_fdw/sql/postgres_fdw.sql
index 3b7da128519..8a672f05039 100644
--- a/contrib/postgres_fdw/sql/postgres_fdw.sql
+++ b/contrib/postgres_fdw/sql/postgres_fdw.sql
@@ -3941,6 +3941,10 @@ INSERT INTO result_tbl SELECT * FROM async_pt WHERE b === 505;
 SELECT * FROM result_tbl ORDER BY a;
 DELETE FROM result_tbl;
 
+-- Test COPY TO with a foreign table or when the foreign table is a partition
+COPY async_p3 TO stdout; --error
+COPY async_pt TO stdout; --error
+
 DROP FOREIGN TABLE async_p3;
 DROP TABLE base_tbl3;
 
diff --git a/doc/src/sgml/ref/copy.sgml b/doc/src/sgml/ref/copy.sgml
index c2d1fbc1fbe..ecd300097fc 100644
--- a/doc/src/sgml/ref/copy.sgml
+++ b/doc/src/sgml/ref/copy.sgml
@@ -539,13 +539,16 @@ COPY <replaceable class="parameter">count</replaceable>
 
    <para>
     <command>COPY TO</command> can be used with plain
-    tables and populated materialized views.
-    For example,
+    tables, populated materialized views and partitioned tables.
+    For example, if <replaceable class="parameter">table</replaceable> is a plain table,
     <literal>COPY <replaceable class="parameter">table</replaceable>
     TO</literal> copies the same rows as
     <literal>SELECT * FROM ONLY <replaceable class="parameter">table</replaceable></literal>.
+    If <replaceable class="parameter">table</replaceable> is a partitioned table or a materialized view
+    <literal>COPY <replaceable class="parameter">table</replaceable> TO</literal>
+    copies the same rows as <literal>SELECT * FROM <replaceable class="parameter">table</replaceable></literal>.
     However it doesn't directly support other relation types,
-    such as partitioned tables, inheritance child tables, or views.
+    such as inheritance child tables, or views.
     To copy all rows from such relations, use <literal>COPY (SELECT * FROM
     <replaceable class="parameter">table</replaceable>) TO</literal>.
    </para>
diff --git a/src/backend/commands/copy.c b/src/backend/commands/copy.c
index fae9c41db65..d09b54308bd 100644
--- a/src/backend/commands/copy.c
+++ b/src/backend/commands/copy.c
@@ -186,6 +186,7 @@ DoCopy(ParseState *pstate, const CopyStmt *stmt,
 			ResTarget  *target;
 			RangeVar   *from;
 			List	   *targetList = NIL;
+			bool		rel_is_partitioned;
 
 			if (is_from)
 				ereport(ERROR,
@@ -193,6 +194,8 @@ DoCopy(ParseState *pstate, const CopyStmt *stmt,
 						 errmsg("COPY FROM not supported with row-level security"),
 						 errhint("Use INSERT statements instead.")));
 
+			rel_is_partitioned = (get_rel_relkind(relid) == RELKIND_PARTITIONED_TABLE);
+
 			/*
 			 * Build target list
 			 *
@@ -251,17 +254,46 @@ DoCopy(ParseState *pstate, const CopyStmt *stmt,
 			 * relation which we have opened and locked.  Use "ONLY" so that
 			 * COPY retrieves rows from only the target table not any
 			 * inheritance children, the same as when RLS doesn't apply.
+			 *
+			 * However, when COPY data from a partitioned table, we should not
+			 * use "ONLY", since we also need to retrieve rows from its child
+			 * partitions too.
 			 */
 			from = makeRangeVar(get_namespace_name(RelationGetNamespace(rel)),
 								pstrdup(RelationGetRelationName(rel)),
 								-1);
 			from->inh = false;	/* apply ONLY */
+			if (rel_is_partitioned)
+				from->inh = true;
 
 			/* Build query */
 			select = makeNode(SelectStmt);
 			select->targetList = targetList;
 			select->fromClause = list_make1(from);
 
+			/*
+			 * To COPY data from multiple partitions, we rely on the order of
+			 * the partitions' tableoids, which matches the order produced by
+			 * find_all_inheritors.
+			 */
+			if (rel_is_partitioned)
+			{
+				SortBy  	*sortby;
+				ColumnRef  	*colref;
+				List	   *orderlist = NIL;
+
+				colref = makeNode(ColumnRef);
+				colref->fields = list_make1(makeString("tableoid"));
+				colref->location = -1;
+
+				sortby = makeNode(SortBy);
+				sortby->node = (Node *) colref;
+				sortby->location = -1;
+
+				orderlist = lappend(orderlist, sortby);
+				select->sortClause = orderlist;
+			}
+
 			query = makeNode(RawStmt);
 			query->stmt = (Node *) select;
 			query->stmt_location = stmt_location;
diff --git a/src/backend/commands/copyto.c b/src/backend/commands/copyto.c
index e5781155cdf..74497240105 100644
--- a/src/backend/commands/copyto.c
+++ b/src/backend/commands/copyto.c
@@ -18,7 +18,9 @@
 #include <unistd.h>
 #include <sys/stat.h>
 
+#include "access/table.h"
 #include "access/tableam.h"
+#include "catalog/pg_inherits.h"
 #include "commands/copyapi.h"
 #include "commands/progress.h"
 #include "executor/execdesc.h"
@@ -82,6 +84,7 @@ typedef struct CopyToStateData
 	List	   *attnumlist;		/* integer list of attnums to copy */
 	char	   *filename;		/* filename, or NULL for STDOUT */
 	bool		is_program;		/* is 'filename' a program to popen? */
+	List	   *partitions;		/* OID list of partitions to copy data from */
 	copy_data_dest_cb data_dest_cb; /* function for writing data */
 
 	CopyFormatOptions opts;
@@ -116,6 +119,8 @@ static void CopyOneRowTo(CopyToState cstate, TupleTableSlot *slot);
 static void CopyAttributeOutText(CopyToState cstate, const char *string);
 static void CopyAttributeOutCSV(CopyToState cstate, const char *string,
 								bool use_quote);
+static void CopyRelationTo(CopyToState cstate, Relation rel, Relation root_rel,
+						   uint64 *processed);
 
 /* built-in format-specific routines */
 static void CopyToTextLikeStart(CopyToState cstate, TupleDesc tupDesc);
@@ -602,6 +607,10 @@ EndCopy(CopyToState cstate)
 	pgstat_progress_end_command();
 
 	MemoryContextDelete(cstate->copycontext);
+
+	if (cstate->partitions)
+		list_free(cstate->partitions);
+
 	pfree(cstate);
 }
 
@@ -643,6 +652,7 @@ BeginCopyTo(ParseState *pstate,
 		PROGRESS_COPY_COMMAND_TO,
 		0
 	};
+	List	   *children = NIL;
 
 	if (rel != NULL && rel->rd_rel->relkind != RELKIND_RELATION)
 	{
@@ -673,11 +683,34 @@ BeginCopyTo(ParseState *pstate,
 					 errmsg("cannot copy from sequence \"%s\"",
 							RelationGetRelationName(rel))));
 		else if (rel->rd_rel->relkind == RELKIND_PARTITIONED_TABLE)
-			ereport(ERROR,
-					(errcode(ERRCODE_WRONG_OBJECT_TYPE),
-					 errmsg("cannot copy from partitioned table \"%s\"",
-							RelationGetRelationName(rel)),
-					 errhint("Try the COPY (SELECT ...) TO variant.")));
+		{
+			/*
+			 * Collect OIDs of relation containing data, so that later
+			 * DoCopyTo can copy the data from them.
+			 */
+			children = find_all_inheritors(RelationGetRelid(rel), AccessShareLock, NULL);
+
+			foreach_oid(child, children)
+			{
+				char		relkind = get_rel_relkind(child);
+
+				if (relkind == RELKIND_FOREIGN_TABLE)
+				{
+					char	   *relation_name = get_rel_name(child);
+
+					ereport(ERROR,
+							errcode(ERRCODE_WRONG_OBJECT_TYPE),
+							errmsg("cannot copy from foreign table \"%s\"", relation_name),
+							errdetail("Partition \"%s\" is a foreign table in partitioned table \"%s\"",
+									  relation_name, RelationGetRelationName(rel)),
+							errhint("Try the COPY (SELECT ...) TO variant."));
+				}
+
+				/* Exclude tables with no data */
+				if (RELKIND_HAS_PARTITIONS(relkind))
+					children = foreach_delete_current(children, child);
+			}
+		}
 		else
 			ereport(ERROR,
 					(errcode(ERRCODE_WRONG_OBJECT_TYPE),
@@ -713,6 +746,7 @@ BeginCopyTo(ParseState *pstate,
 		cstate->rel = rel;
 
 		tupDesc = RelationGetDescr(cstate->rel);
+		cstate->partitions = children;
 	}
 	else
 	{
@@ -722,6 +756,7 @@ BeginCopyTo(ParseState *pstate,
 		DestReceiver *dest;
 
 		cstate->rel = NULL;
+		cstate->partitions = NIL;
 
 		/*
 		 * Run parse analysis and rewrite.  Note this also acquires sufficient
@@ -1030,7 +1065,7 @@ DoCopyTo(CopyToState cstate)
 	TupleDesc	tupDesc;
 	int			num_phys_attrs;
 	ListCell   *cur;
-	uint64		processed;
+	uint64		processed = 0;
 
 	if (fe_copy)
 		SendCopyBegin(cstate);
@@ -1070,33 +1105,24 @@ DoCopyTo(CopyToState cstate)
 
 	if (cstate->rel)
 	{
-		TupleTableSlot *slot;
-		TableScanDesc scandesc;
-
-		scandesc = table_beginscan(cstate->rel, GetActiveSnapshot(), 0, NULL);
-		slot = table_slot_create(cstate->rel, NULL);
-
-		processed = 0;
-		while (table_scan_getnextslot(scandesc, ForwardScanDirection, slot))
+		/*
+		 * If COPY TO source table is a partitioned table, then open each
+		 * partition and process each individual partition.
+		 */
+		if (cstate->rel->rd_rel->relkind == RELKIND_PARTITIONED_TABLE)
 		{
-			CHECK_FOR_INTERRUPTS();
+			foreach_oid(child, cstate->partitions)
+			{
+				Relation	scan_rel;
 
-			/* Deconstruct the tuple ... */
-			slot_getallattrs(slot);
-
-			/* Format and send the data */
-			CopyOneRowTo(cstate, slot);
-
-			/*
-			 * Increment the number of processed tuples, and report the
-			 * progress.
-			 */
-			pgstat_progress_update_param(PROGRESS_COPY_TUPLES_PROCESSED,
-										 ++processed);
+				/* We already got the lock in BeginCopyTo */
+				scan_rel = table_open(child, NoLock);
+				CopyRelationTo(cstate, scan_rel, cstate->rel, &processed);
+				table_close(scan_rel, NoLock);
+			}
 		}
-
-		ExecDropSingleTupleTableSlot(slot);
-		table_endscan(scandesc);
+		else
+			CopyRelationTo(cstate, cstate->rel, NULL, &processed);
 	}
 	else
 	{
@@ -1115,6 +1141,73 @@ DoCopyTo(CopyToState cstate)
 	return processed;
 }
 
+/*
+ * Scans a single table and exports its rows to the COPY destination.
+ *
+ * root_rel can be set to the root table of rel if rel is a partition
+ * table so that we can send tuples in root_rel's rowtype, which might
+ * differ from individual partitions.
+*/
+static void
+CopyRelationTo(CopyToState cstate, Relation rel, Relation root_rel, uint64 *processed)
+{
+	TupleTableSlot *slot;
+	TableScanDesc scandesc;
+	AttrMap    *map = NULL;
+	TupleTableSlot *root_slot = NULL;
+
+	scandesc = table_beginscan(rel, GetActiveSnapshot(), 0, NULL);
+	slot = table_slot_create(rel, NULL);
+
+	/*
+	 * A partition's rowtype might differ from the root table's. If we are
+	 * exporting partition data here, we must convert it back to the root
+	 * table's rowtype.
+	 */
+	if (root_rel != NULL)
+	{
+		root_slot = table_slot_create(root_rel, NULL);
+		map = build_attrmap_by_name_if_req(RelationGetDescr(root_rel),
+										   RelationGetDescr(rel),
+										   false);
+	}
+
+	while (table_scan_getnextslot(scandesc, ForwardScanDirection, slot))
+	{
+		TupleTableSlot *copyslot;
+
+		CHECK_FOR_INTERRUPTS();
+
+		if (map != NULL)
+			copyslot = execute_attr_map_slot(map, slot, root_slot);
+		else
+		{
+			/* Deconstruct the tuple ... */
+			slot_getallattrs(slot);
+			copyslot = slot;
+		}
+
+		/* Format and send the data */
+		CopyOneRowTo(cstate, copyslot);
+
+		/*
+		 * Increment the number of processed tuples, and report the progress.
+		 */
+		pgstat_progress_update_param(PROGRESS_COPY_TUPLES_PROCESSED,
+									 ++(*processed));
+	}
+
+	ExecDropSingleTupleTableSlot(slot);
+
+	if (root_slot != NULL)
+		ExecDropSingleTupleTableSlot(root_slot);
+
+	if (map != NULL)
+		free_attrmap(map);
+
+	table_endscan(scandesc);
+}
+
 /*
  * Emit one row during DoCopyTo().
  */
diff --git a/src/test/regress/expected/copy.out b/src/test/regress/expected/copy.out
index ac66eb55aee..af01e84cea1 100644
--- a/src/test/regress/expected/copy.out
+++ b/src/test/regress/expected/copy.out
@@ -373,3 +373,21 @@ COPY copytest_mv(id) TO stdout WITH (header);
 id
 1
 DROP MATERIALIZED VIEW copytest_mv;
+-- Tests for COPY TO with partitioned tables.
+CREATE TABLE pp (id int,val int) PARTITION BY RANGE (id);
+CREATE TABLE pp_1 (val int, id int) PARTITION BY RANGE (id);
+CREATE TABLE pp_2 (id int, val int) PARTITION BY RANGE (id);
+ALTER TABLE pp ATTACH PARTITION pp_1 FOR VALUES FROM (1) TO (5);
+ALTER TABLE pp ATTACH PARTITION pp_2 FOR VALUES FROM (5) TO (10);
+CREATE TABLE pp_15 PARTITION OF pp_1 FOR VALUES FROM (1) TO (5);
+CREATE TABLE pp_510 PARTITION OF pp_2 FOR VALUES FROM (5) TO (10);
+INSERT INTO pp SELECT g, 10 + g FROM generate_series(1,6) g;
+COPY pp TO stdout(header);
+id	val
+1	11
+2	12
+3	13
+4	14
+5	15
+6	16
+DROP TABLE PP;
diff --git a/src/test/regress/expected/rowsecurity.out b/src/test/regress/expected/rowsecurity.out
index 5a172c5d91c..0b88b1eed44 100644
--- a/src/test/regress/expected/rowsecurity.out
+++ b/src/test/regress/expected/rowsecurity.out
@@ -986,6 +986,20 @@ NOTICE:  f_leak => my first satire
    9 |  11 |      1 | regress_rls_dave  | awesome science fiction
 (4 rows)
 
+SELECT * FROM part_document ORDER BY tableoid;
+ did | cid | dlevel |      dauthor      |         dtitle          
+-----+-----+--------+-------------------+-------------------------
+   1 |  11 |      1 | regress_rls_bob   | my first novel
+   6 |  11 |      1 | regress_rls_carol | great science fiction
+   9 |  11 |      1 | regress_rls_dave  | awesome science fiction
+   4 |  55 |      1 | regress_rls_bob   | my first satire
+(4 rows)
+
+COPY part_document TO stdout WITH (DELIMITER ',');
+1,11,1,regress_rls_bob,my first novel
+6,11,1,regress_rls_carol,great science fiction
+9,11,1,regress_rls_dave,awesome science fiction
+4,55,1,regress_rls_bob,my first satire
 EXPLAIN (COSTS OFF) SELECT * FROM part_document WHERE f_leak(dtitle);
                                QUERY PLAN                                
 -------------------------------------------------------------------------
@@ -1028,6 +1042,32 @@ NOTICE:  f_leak => awesome technology book
   10 |  99 |      2 | regress_rls_dave  | awesome technology book
 (10 rows)
 
+SELECT * FROM part_document ORDER BY tableoid;
+ did | cid | dlevel |      dauthor      |         dtitle          
+-----+-----+--------+-------------------+-------------------------
+   1 |  11 |      1 | regress_rls_bob   | my first novel
+   2 |  11 |      2 | regress_rls_bob   | my second novel
+   6 |  11 |      1 | regress_rls_carol | great science fiction
+   9 |  11 |      1 | regress_rls_dave  | awesome science fiction
+   4 |  55 |      1 | regress_rls_bob   | my first satire
+   8 |  55 |      2 | regress_rls_carol | great satire
+   3 |  99 |      2 | regress_rls_bob   | my science textbook
+   5 |  99 |      2 | regress_rls_bob   | my history book
+   7 |  99 |      2 | regress_rls_carol | great technology book
+  10 |  99 |      2 | regress_rls_dave  | awesome technology book
+(10 rows)
+
+COPY part_document TO stdout WITH (DELIMITER ',');
+1,11,1,regress_rls_bob,my first novel
+2,11,2,regress_rls_bob,my second novel
+6,11,1,regress_rls_carol,great science fiction
+9,11,1,regress_rls_dave,awesome science fiction
+4,55,1,regress_rls_bob,my first satire
+8,55,2,regress_rls_carol,great satire
+3,99,2,regress_rls_bob,my science textbook
+5,99,2,regress_rls_bob,my history book
+7,99,2,regress_rls_carol,great technology book
+10,99,2,regress_rls_dave,awesome technology book
 EXPLAIN (COSTS OFF) SELECT * FROM part_document WHERE f_leak(dtitle);
                                QUERY PLAN                                
 -------------------------------------------------------------------------
@@ -1058,6 +1098,20 @@ NOTICE:  f_leak => awesome science fiction
    9 |  11 |      1 | regress_rls_dave  | awesome science fiction
 (4 rows)
 
+SELECT * FROM part_document ORDER BY tableoid;
+ did | cid | dlevel |      dauthor      |         dtitle          
+-----+-----+--------+-------------------+-------------------------
+   1 |  11 |      1 | regress_rls_bob   | my first novel
+   2 |  11 |      2 | regress_rls_bob   | my second novel
+   6 |  11 |      1 | regress_rls_carol | great science fiction
+   9 |  11 |      1 | regress_rls_dave  | awesome science fiction
+(4 rows)
+
+COPY part_document TO stdout WITH (DELIMITER ',');
+1,11,1,regress_rls_bob,my first novel
+2,11,2,regress_rls_bob,my second novel
+6,11,1,regress_rls_carol,great science fiction
+9,11,1,regress_rls_dave,awesome science fiction
 EXPLAIN (COSTS OFF) SELECT * FROM part_document WHERE f_leak(dtitle);
                                     QUERY PLAN                                    
 ----------------------------------------------------------------------------------
diff --git a/src/test/regress/sql/copy.sql b/src/test/regress/sql/copy.sql
index a1316c73bac..56d506ad4c6 100644
--- a/src/test/regress/sql/copy.sql
+++ b/src/test/regress/sql/copy.sql
@@ -405,3 +405,18 @@ COPY copytest_mv(id) TO stdout WITH (header);
 REFRESH MATERIALIZED VIEW copytest_mv;
 COPY copytest_mv(id) TO stdout WITH (header);
 DROP MATERIALIZED VIEW copytest_mv;
+
+-- Tests for COPY TO with partitioned tables.
+CREATE TABLE pp (id int,val int) PARTITION BY RANGE (id);
+CREATE TABLE pp_1 (val int, id int) PARTITION BY RANGE (id);
+CREATE TABLE pp_2 (id int, val int) PARTITION BY RANGE (id);
+ALTER TABLE pp ATTACH PARTITION pp_1 FOR VALUES FROM (1) TO (5);
+ALTER TABLE pp ATTACH PARTITION pp_2 FOR VALUES FROM (5) TO (10);
+
+CREATE TABLE pp_15 PARTITION OF pp_1 FOR VALUES FROM (1) TO (5);
+CREATE TABLE pp_510 PARTITION OF pp_2 FOR VALUES FROM (5) TO (10);
+
+INSERT INTO pp SELECT g, 10 + g FROM generate_series(1,6) g;
+
+COPY pp TO stdout(header);
+DROP TABLE PP;
diff --git a/src/test/regress/sql/rowsecurity.sql b/src/test/regress/sql/rowsecurity.sql
index 21ac0ca51ee..d1306071070 100644
--- a/src/test/regress/sql/rowsecurity.sql
+++ b/src/test/regress/sql/rowsecurity.sql
@@ -362,16 +362,22 @@ SELECT * FROM pg_policies WHERE schemaname = 'regress_rls_schema' AND tablename
 SET SESSION AUTHORIZATION regress_rls_bob;
 SET row_security TO ON;
 SELECT * FROM part_document WHERE f_leak(dtitle) ORDER BY did;
+SELECT * FROM part_document ORDER BY tableoid;
+COPY part_document TO stdout WITH (DELIMITER ',');
 EXPLAIN (COSTS OFF) SELECT * FROM part_document WHERE f_leak(dtitle);
 
 -- viewpoint from regress_rls_carol
 SET SESSION AUTHORIZATION regress_rls_carol;
 SELECT * FROM part_document WHERE f_leak(dtitle) ORDER BY did;
+SELECT * FROM part_document ORDER BY tableoid;
+COPY part_document TO stdout WITH (DELIMITER ',');
 EXPLAIN (COSTS OFF) SELECT * FROM part_document WHERE f_leak(dtitle);
 
 -- viewpoint from regress_rls_dave
 SET SESSION AUTHORIZATION regress_rls_dave;
 SELECT * FROM part_document WHERE f_leak(dtitle) ORDER BY did;
+SELECT * FROM part_document ORDER BY tableoid;
+COPY part_document TO stdout WITH (DELIMITER ',');
 EXPLAIN (COSTS OFF) SELECT * FROM part_document WHERE f_leak(dtitle);
 
 -- pp1 ERROR
-- 
2.34.1

From fc87e123872b60ccaa37c02b80bae6765d27f4a8 Mon Sep 17 00:00:00 2001
From: jian he <jian.universality@gmail.com>
Date: Wed, 15 Oct 2025 09:44:00 +0800
Subject: [PATCH v18 1/1] support COPY partitioned_table TO
MIME-Version: 1.0
Content-Type: text/plain; charset=UTF-8
Content-Transfer-Encoding: 8bit

This is for implementation of ``COPY (partitioned_table) TO``.  it will be
faster than ``COPY (select * from partitioned_table) TO``.
If the destination table is a partitioned table, COPY table TO copies the same
rows as SELECT * FROM table.

reivewed by: vignesh C <vignesh21@gmail.com>
reivewed by: David Rowley <dgrowleyml@gmail.com>
reivewed by: Melih Mutlu <m.melihmutlu@gmail.com>
reivewed by: Kirill Reshke <reshkekirill@gmail.com>
reivewed by: Atsushi Torikoshi <torikoshia@oss.nttdata.com>
reivewed by: Álvaro Herrera <alvherre@kurilemu.de>
reivewed by: Masahiko Sawada <sawada.mshk@gmail.com>
reivewed by: Chao Li <li.evan.chao@gmail.com>

discussion: https://postgr.es/m/CACJufxEZt+G19Ors3bQUq-42-61__C=y5k2wk=sHEFRusu7=iQ@mail.gmail.com
commitfest entry: https://commitfest.postgresql.org/patch/5467
---
 .../postgres_fdw/expected/postgres_fdw.out    |   5 +
 contrib/postgres_fdw/sql/postgres_fdw.sql     |   3 +
 doc/src/sgml/ref/copy.sgml                    |   9 +-
 src/backend/commands/copy.c                   |   6 +
 src/backend/commands/copyto.c                 | 153 ++++++++++++++----
 src/test/regress/expected/copy.out            |  18 +++
 src/test/regress/expected/rowsecurity.out     |  21 +++
 src/test/regress/sql/copy.sql                 |  15 ++
 src/test/regress/sql/rowsecurity.sql          |   3 +
 9 files changed, 200 insertions(+), 33 deletions(-)

diff --git a/contrib/postgres_fdw/expected/postgres_fdw.out b/contrib/postgres_fdw/expected/postgres_fdw.out
index 91bbd0d8c73..cd28126049d 100644
--- a/contrib/postgres_fdw/expected/postgres_fdw.out
+++ b/contrib/postgres_fdw/expected/postgres_fdw.out
@@ -11599,6 +11599,11 @@ SELECT * FROM result_tbl ORDER BY a;
 (3 rows)
 
 DELETE FROM result_tbl;
+-- Test COPY TO when foreign table is partition
+COPY async_pt TO stdout; --error
+ERROR:  cannot copy from foreign table "async_p1"
+DETAIL:  Partition "async_p1" is a foreign table in partitioned table "async_pt"
+HINT:  Try the COPY (SELECT ...) TO variant.
 DROP FOREIGN TABLE async_p3;
 DROP TABLE base_tbl3;
 -- Check case where the partitioned table has local/remote partitions
diff --git a/contrib/postgres_fdw/sql/postgres_fdw.sql b/contrib/postgres_fdw/sql/postgres_fdw.sql
index 3b7da128519..9a8f9e28135 100644
--- a/contrib/postgres_fdw/sql/postgres_fdw.sql
+++ b/contrib/postgres_fdw/sql/postgres_fdw.sql
@@ -3941,6 +3941,9 @@ INSERT INTO result_tbl SELECT * FROM async_pt WHERE b === 505;
 SELECT * FROM result_tbl ORDER BY a;
 DELETE FROM result_tbl;
 
+-- Test COPY TO when foreign table is partition
+COPY async_pt TO stdout; --error
+
 DROP FOREIGN TABLE async_p3;
 DROP TABLE base_tbl3;
 
diff --git a/doc/src/sgml/ref/copy.sgml b/doc/src/sgml/ref/copy.sgml
index c2d1fbc1fbe..ecd300097fc 100644
--- a/doc/src/sgml/ref/copy.sgml
+++ b/doc/src/sgml/ref/copy.sgml
@@ -539,13 +539,16 @@ COPY <replaceable class="parameter">count</replaceable>
 
    <para>
     <command>COPY TO</command> can be used with plain
-    tables and populated materialized views.
-    For example,
+    tables, populated materialized views and partitioned tables.
+    For example, if <replaceable class="parameter">table</replaceable> is a plain table,
     <literal>COPY <replaceable class="parameter">table</replaceable>
     TO</literal> copies the same rows as
     <literal>SELECT * FROM ONLY <replaceable class="parameter">table</replaceable></literal>.
+    If <replaceable class="parameter">table</replaceable> is a partitioned table or a materialized view
+    <literal>COPY <replaceable class="parameter">table</replaceable> TO</literal>
+    copies the same rows as <literal>SELECT * FROM <replaceable class="parameter">table</replaceable></literal>.
     However it doesn't directly support other relation types,
-    such as partitioned tables, inheritance child tables, or views.
+    such as inheritance child tables, or views.
     To copy all rows from such relations, use <literal>COPY (SELECT * FROM
     <replaceable class="parameter">table</replaceable>) TO</literal>.
    </para>
diff --git a/src/backend/commands/copy.c b/src/backend/commands/copy.c
index fae9c41db65..9e12adb81a1 100644
--- a/src/backend/commands/copy.c
+++ b/src/backend/commands/copy.c
@@ -251,11 +251,17 @@ DoCopy(ParseState *pstate, const CopyStmt *stmt,
 			 * relation which we have opened and locked.  Use "ONLY" so that
 			 * COPY retrieves rows from only the target table not any
 			 * inheritance children, the same as when RLS doesn't apply.
+			 *
+			 * However, when COPY data from a partitioned table, we should not
+			 * use "ONLY", since we also need to retrieve rows from its child
+			 * partitions too.
 			 */
 			from = makeRangeVar(get_namespace_name(RelationGetNamespace(rel)),
 								pstrdup(RelationGetRelationName(rel)),
 								-1);
 			from->inh = false;	/* apply ONLY */
+			if (get_rel_relkind(relid) == RELKIND_PARTITIONED_TABLE)
+				from->inh = true;
 
 			/* Build query */
 			select = makeNode(SelectStmt);
diff --git a/src/backend/commands/copyto.c b/src/backend/commands/copyto.c
index e5781155cdf..74497240105 100644
--- a/src/backend/commands/copyto.c
+++ b/src/backend/commands/copyto.c
@@ -18,7 +18,9 @@
 #include <unistd.h>
 #include <sys/stat.h>
 
+#include "access/table.h"
 #include "access/tableam.h"
+#include "catalog/pg_inherits.h"
 #include "commands/copyapi.h"
 #include "commands/progress.h"
 #include "executor/execdesc.h"
@@ -82,6 +84,7 @@ typedef struct CopyToStateData
 	List	   *attnumlist;		/* integer list of attnums to copy */
 	char	   *filename;		/* filename, or NULL for STDOUT */
 	bool		is_program;		/* is 'filename' a program to popen? */
+	List	   *partitions;		/* OID list of partitions to copy data from */
 	copy_data_dest_cb data_dest_cb; /* function for writing data */
 
 	CopyFormatOptions opts;
@@ -116,6 +119,8 @@ static void CopyOneRowTo(CopyToState cstate, TupleTableSlot *slot);
 static void CopyAttributeOutText(CopyToState cstate, const char *string);
 static void CopyAttributeOutCSV(CopyToState cstate, const char *string,
 								bool use_quote);
+static void CopyRelationTo(CopyToState cstate, Relation rel, Relation root_rel,
+						   uint64 *processed);
 
 /* built-in format-specific routines */
 static void CopyToTextLikeStart(CopyToState cstate, TupleDesc tupDesc);
@@ -602,6 +607,10 @@ EndCopy(CopyToState cstate)
 	pgstat_progress_end_command();
 
 	MemoryContextDelete(cstate->copycontext);
+
+	if (cstate->partitions)
+		list_free(cstate->partitions);
+
 	pfree(cstate);
 }
 
@@ -643,6 +652,7 @@ BeginCopyTo(ParseState *pstate,
 		PROGRESS_COPY_COMMAND_TO,
 		0
 	};
+	List	   *children = NIL;
 
 	if (rel != NULL && rel->rd_rel->relkind != RELKIND_RELATION)
 	{
@@ -673,11 +683,34 @@ BeginCopyTo(ParseState *pstate,
 					 errmsg("cannot copy from sequence \"%s\"",
 							RelationGetRelationName(rel))));
 		else if (rel->rd_rel->relkind == RELKIND_PARTITIONED_TABLE)
-			ereport(ERROR,
-					(errcode(ERRCODE_WRONG_OBJECT_TYPE),
-					 errmsg("cannot copy from partitioned table \"%s\"",
-							RelationGetRelationName(rel)),
-					 errhint("Try the COPY (SELECT ...) TO variant.")));
+		{
+			/*
+			 * Collect OIDs of relation containing data, so that later
+			 * DoCopyTo can copy the data from them.
+			 */
+			children = find_all_inheritors(RelationGetRelid(rel), AccessShareLock, NULL);
+
+			foreach_oid(child, children)
+			{
+				char		relkind = get_rel_relkind(child);
+
+				if (relkind == RELKIND_FOREIGN_TABLE)
+				{
+					char	   *relation_name = get_rel_name(child);
+
+					ereport(ERROR,
+							errcode(ERRCODE_WRONG_OBJECT_TYPE),
+							errmsg("cannot copy from foreign table \"%s\"", relation_name),
+							errdetail("Partition \"%s\" is a foreign table in partitioned table \"%s\"",
+									  relation_name, RelationGetRelationName(rel)),
+							errhint("Try the COPY (SELECT ...) TO variant."));
+				}
+
+				/* Exclude tables with no data */
+				if (RELKIND_HAS_PARTITIONS(relkind))
+					children = foreach_delete_current(children, child);
+			}
+		}
 		else
 			ereport(ERROR,
 					(errcode(ERRCODE_WRONG_OBJECT_TYPE),
@@ -713,6 +746,7 @@ BeginCopyTo(ParseState *pstate,
 		cstate->rel = rel;
 
 		tupDesc = RelationGetDescr(cstate->rel);
+		cstate->partitions = children;
 	}
 	else
 	{
@@ -722,6 +756,7 @@ BeginCopyTo(ParseState *pstate,
 		DestReceiver *dest;
 
 		cstate->rel = NULL;
+		cstate->partitions = NIL;
 
 		/*
 		 * Run parse analysis and rewrite.  Note this also acquires sufficient
@@ -1030,7 +1065,7 @@ DoCopyTo(CopyToState cstate)
 	TupleDesc	tupDesc;
 	int			num_phys_attrs;
 	ListCell   *cur;
-	uint64		processed;
+	uint64		processed = 0;
 
 	if (fe_copy)
 		SendCopyBegin(cstate);
@@ -1070,33 +1105,24 @@ DoCopyTo(CopyToState cstate)
 
 	if (cstate->rel)
 	{
-		TupleTableSlot *slot;
-		TableScanDesc scandesc;
-
-		scandesc = table_beginscan(cstate->rel, GetActiveSnapshot(), 0, NULL);
-		slot = table_slot_create(cstate->rel, NULL);
-
-		processed = 0;
-		while (table_scan_getnextslot(scandesc, ForwardScanDirection, slot))
+		/*
+		 * If COPY TO source table is a partitioned table, then open each
+		 * partition and process each individual partition.
+		 */
+		if (cstate->rel->rd_rel->relkind == RELKIND_PARTITIONED_TABLE)
 		{
-			CHECK_FOR_INTERRUPTS();
+			foreach_oid(child, cstate->partitions)
+			{
+				Relation	scan_rel;
 
-			/* Deconstruct the tuple ... */
-			slot_getallattrs(slot);
-
-			/* Format and send the data */
-			CopyOneRowTo(cstate, slot);
-
-			/*
-			 * Increment the number of processed tuples, and report the
-			 * progress.
-			 */
-			pgstat_progress_update_param(PROGRESS_COPY_TUPLES_PROCESSED,
-										 ++processed);
+				/* We already got the lock in BeginCopyTo */
+				scan_rel = table_open(child, NoLock);
+				CopyRelationTo(cstate, scan_rel, cstate->rel, &processed);
+				table_close(scan_rel, NoLock);
+			}
 		}
-
-		ExecDropSingleTupleTableSlot(slot);
-		table_endscan(scandesc);
+		else
+			CopyRelationTo(cstate, cstate->rel, NULL, &processed);
 	}
 	else
 	{
@@ -1115,6 +1141,73 @@ DoCopyTo(CopyToState cstate)
 	return processed;
 }
 
+/*
+ * Scans a single table and exports its rows to the COPY destination.
+ *
+ * root_rel can be set to the root table of rel if rel is a partition
+ * table so that we can send tuples in root_rel's rowtype, which might
+ * differ from individual partitions.
+*/
+static void
+CopyRelationTo(CopyToState cstate, Relation rel, Relation root_rel, uint64 *processed)
+{
+	TupleTableSlot *slot;
+	TableScanDesc scandesc;
+	AttrMap    *map = NULL;
+	TupleTableSlot *root_slot = NULL;
+
+	scandesc = table_beginscan(rel, GetActiveSnapshot(), 0, NULL);
+	slot = table_slot_create(rel, NULL);
+
+	/*
+	 * A partition's rowtype might differ from the root table's. If we are
+	 * exporting partition data here, we must convert it back to the root
+	 * table's rowtype.
+	 */
+	if (root_rel != NULL)
+	{
+		root_slot = table_slot_create(root_rel, NULL);
+		map = build_attrmap_by_name_if_req(RelationGetDescr(root_rel),
+										   RelationGetDescr(rel),
+										   false);
+	}
+
+	while (table_scan_getnextslot(scandesc, ForwardScanDirection, slot))
+	{
+		TupleTableSlot *copyslot;
+
+		CHECK_FOR_INTERRUPTS();
+
+		if (map != NULL)
+			copyslot = execute_attr_map_slot(map, slot, root_slot);
+		else
+		{
+			/* Deconstruct the tuple ... */
+			slot_getallattrs(slot);
+			copyslot = slot;
+		}
+
+		/* Format and send the data */
+		CopyOneRowTo(cstate, copyslot);
+
+		/*
+		 * Increment the number of processed tuples, and report the progress.
+		 */
+		pgstat_progress_update_param(PROGRESS_COPY_TUPLES_PROCESSED,
+									 ++(*processed));
+	}
+
+	ExecDropSingleTupleTableSlot(slot);
+
+	if (root_slot != NULL)
+		ExecDropSingleTupleTableSlot(root_slot);
+
+	if (map != NULL)
+		free_attrmap(map);
+
+	table_endscan(scandesc);
+}
+
 /*
  * Emit one row during DoCopyTo().
  */
diff --git a/src/test/regress/expected/copy.out b/src/test/regress/expected/copy.out
index ac66eb55aee..af01e84cea1 100644
--- a/src/test/regress/expected/copy.out
+++ b/src/test/regress/expected/copy.out
@@ -373,3 +373,21 @@ COPY copytest_mv(id) TO stdout WITH (header);
 id
 1
 DROP MATERIALIZED VIEW copytest_mv;
+-- Tests for COPY TO with partitioned tables.
+CREATE TABLE pp (id int,val int) PARTITION BY RANGE (id);
+CREATE TABLE pp_1 (val int, id int) PARTITION BY RANGE (id);
+CREATE TABLE pp_2 (id int, val int) PARTITION BY RANGE (id);
+ALTER TABLE pp ATTACH PARTITION pp_1 FOR VALUES FROM (1) TO (5);
+ALTER TABLE pp ATTACH PARTITION pp_2 FOR VALUES FROM (5) TO (10);
+CREATE TABLE pp_15 PARTITION OF pp_1 FOR VALUES FROM (1) TO (5);
+CREATE TABLE pp_510 PARTITION OF pp_2 FOR VALUES FROM (5) TO (10);
+INSERT INTO pp SELECT g, 10 + g FROM generate_series(1,6) g;
+COPY pp TO stdout(header);
+id	val
+1	11
+2	12
+3	13
+4	14
+5	15
+6	16
+DROP TABLE PP;
diff --git a/src/test/regress/expected/rowsecurity.out b/src/test/regress/expected/rowsecurity.out
index 5a172c5d91c..42b78a24603 100644
--- a/src/test/regress/expected/rowsecurity.out
+++ b/src/test/regress/expected/rowsecurity.out
@@ -986,6 +986,11 @@ NOTICE:  f_leak => my first satire
    9 |  11 |      1 | regress_rls_dave  | awesome science fiction
 (4 rows)
 
+COPY part_document TO stdout WITH (DELIMITER ',');
+1,11,1,regress_rls_bob,my first novel
+6,11,1,regress_rls_carol,great science fiction
+9,11,1,regress_rls_dave,awesome science fiction
+4,55,1,regress_rls_bob,my first satire
 EXPLAIN (COSTS OFF) SELECT * FROM part_document WHERE f_leak(dtitle);
                                QUERY PLAN                                
 -------------------------------------------------------------------------
@@ -1028,6 +1033,17 @@ NOTICE:  f_leak => awesome technology book
   10 |  99 |      2 | regress_rls_dave  | awesome technology book
 (10 rows)
 
+COPY part_document TO stdout WITH (DELIMITER ',');
+1,11,1,regress_rls_bob,my first novel
+2,11,2,regress_rls_bob,my second novel
+6,11,1,regress_rls_carol,great science fiction
+9,11,1,regress_rls_dave,awesome science fiction
+4,55,1,regress_rls_bob,my first satire
+8,55,2,regress_rls_carol,great satire
+3,99,2,regress_rls_bob,my science textbook
+5,99,2,regress_rls_bob,my history book
+7,99,2,regress_rls_carol,great technology book
+10,99,2,regress_rls_dave,awesome technology book
 EXPLAIN (COSTS OFF) SELECT * FROM part_document WHERE f_leak(dtitle);
                                QUERY PLAN                                
 -------------------------------------------------------------------------
@@ -1058,6 +1074,11 @@ NOTICE:  f_leak => awesome science fiction
    9 |  11 |      1 | regress_rls_dave  | awesome science fiction
 (4 rows)
 
+COPY part_document TO stdout WITH (DELIMITER ',');
+1,11,1,regress_rls_bob,my first novel
+2,11,2,regress_rls_bob,my second novel
+6,11,1,regress_rls_carol,great science fiction
+9,11,1,regress_rls_dave,awesome science fiction
 EXPLAIN (COSTS OFF) SELECT * FROM part_document WHERE f_leak(dtitle);
                                     QUERY PLAN                                    
 ----------------------------------------------------------------------------------
diff --git a/src/test/regress/sql/copy.sql b/src/test/regress/sql/copy.sql
index a1316c73bac..56d506ad4c6 100644
--- a/src/test/regress/sql/copy.sql
+++ b/src/test/regress/sql/copy.sql
@@ -405,3 +405,18 @@ COPY copytest_mv(id) TO stdout WITH (header);
 REFRESH MATERIALIZED VIEW copytest_mv;
 COPY copytest_mv(id) TO stdout WITH (header);
 DROP MATERIALIZED VIEW copytest_mv;
+
+-- Tests for COPY TO with partitioned tables.
+CREATE TABLE pp (id int,val int) PARTITION BY RANGE (id);
+CREATE TABLE pp_1 (val int, id int) PARTITION BY RANGE (id);
+CREATE TABLE pp_2 (id int, val int) PARTITION BY RANGE (id);
+ALTER TABLE pp ATTACH PARTITION pp_1 FOR VALUES FROM (1) TO (5);
+ALTER TABLE pp ATTACH PARTITION pp_2 FOR VALUES FROM (5) TO (10);
+
+CREATE TABLE pp_15 PARTITION OF pp_1 FOR VALUES FROM (1) TO (5);
+CREATE TABLE pp_510 PARTITION OF pp_2 FOR VALUES FROM (5) TO (10);
+
+INSERT INTO pp SELECT g, 10 + g FROM generate_series(1,6) g;
+
+COPY pp TO stdout(header);
+DROP TABLE PP;
diff --git a/src/test/regress/sql/rowsecurity.sql b/src/test/regress/sql/rowsecurity.sql
index 21ac0ca51ee..2d1be543391 100644
--- a/src/test/regress/sql/rowsecurity.sql
+++ b/src/test/regress/sql/rowsecurity.sql
@@ -362,16 +362,19 @@ SELECT * FROM pg_policies WHERE schemaname = 'regress_rls_schema' AND tablename
 SET SESSION AUTHORIZATION regress_rls_bob;
 SET row_security TO ON;
 SELECT * FROM part_document WHERE f_leak(dtitle) ORDER BY did;
+COPY part_document TO stdout WITH (DELIMITER ',');
 EXPLAIN (COSTS OFF) SELECT * FROM part_document WHERE f_leak(dtitle);
 
 -- viewpoint from regress_rls_carol
 SET SESSION AUTHORIZATION regress_rls_carol;
 SELECT * FROM part_document WHERE f_leak(dtitle) ORDER BY did;
+COPY part_document TO stdout WITH (DELIMITER ',');
 EXPLAIN (COSTS OFF) SELECT * FROM part_document WHERE f_leak(dtitle);
 
 -- viewpoint from regress_rls_dave
 SET SESSION AUTHORIZATION regress_rls_dave;
 SELECT * FROM part_document WHERE f_leak(dtitle) ORDER BY did;
+COPY part_document TO stdout WITH (DELIMITER ',');
 EXPLAIN (COSTS OFF) SELECT * FROM part_document WHERE f_leak(dtitle);
 
 -- pp1 ERROR
-- 
2.34.1

diff --git a/src/backend/commands/copy.c b/src/backend/commands/copy.c
index 9e12adb81a1..eac501753c8 100644
--- a/src/backend/commands/copy.c
+++ b/src/backend/commands/copy.c
@@ -252,16 +252,14 @@ DoCopy(ParseState *pstate, const CopyStmt *stmt,
 			 * COPY retrieves rows from only the target table not any
 			 * inheritance children, the same as when RLS doesn't apply.
 			 *
-			 * However, when COPY data from a partitioned table, we should not
-			 * use "ONLY", since we also need to retrieve rows from its child
-			 * partitions too.
+			 * However, when copying data from a partitioned table, we don't
+			 * not use "ONLY", since we need to retrieve rows from its
+			 * descendant tables too.
 			 */
 			from = makeRangeVar(get_namespace_name(RelationGetNamespace(rel)),
 								pstrdup(RelationGetRelationName(rel)),
 								-1);
-			from->inh = false;	/* apply ONLY */
-			if (get_rel_relkind(relid) == RELKIND_PARTITIONED_TABLE)
-				from->inh = true;
+			from->inh = (rel->rd_rel->relkind == RELKIND_PARTITIONED_TABLE);
 
 			/* Build query */
 			select = makeNode(SelectStmt);
diff --git a/src/backend/commands/copyto.c b/src/backend/commands/copyto.c
index 74497240105..a1919c6db43 100644
--- a/src/backend/commands/copyto.c
+++ b/src/backend/commands/copyto.c
@@ -84,11 +84,11 @@ typedef struct CopyToStateData
 	List	   *attnumlist;		/* integer list of attnums to copy */
 	char	   *filename;		/* filename, or NULL for STDOUT */
 	bool		is_program;		/* is 'filename' a program to popen? */
-	List	   *partitions;		/* OID list of partitions to copy data from */
 	copy_data_dest_cb data_dest_cb; /* function for writing data */
 
 	CopyFormatOptions opts;
 	Node	   *whereClause;	/* WHERE condition (or NULL) */
+	List	   *partitions;		/* OID list of partitions to copy data from */
 
 	/*
 	 * Working state
@@ -1160,9 +1160,9 @@ CopyRelationTo(CopyToState cstate, Relation rel, Relation root_rel, uint64 *proc
 	slot = table_slot_create(rel, NULL);
 
 	/*
-	 * A partition's rowtype might differ from the root table's. If we are
-	 * exporting partition data here, we must convert it back to the root
-	 * table's rowtype.
+	 * If we are exporting partition data here, we check if converting tuples
+	 * to the root table's rowtype, because a partition might have column
+	 * order different than its root table.
 	 */
 	if (root_rel != NULL)
 	{
@@ -1182,7 +1182,7 @@ CopyRelationTo(CopyToState cstate, Relation rel, Relation root_rel, uint64 *proc
 			copyslot = execute_attr_map_slot(map, slot, root_slot);
 		else
 		{
-			/* Deconstruct the tuple ... */
+			/* Deconstruct the tuple */
 			slot_getallattrs(slot);
 			copyslot = slot;
 		}
diff --git a/src/test/regress/expected/copy.out b/src/test/regress/expected/copy.out
index af01e84cea1..24e0f472f14 100644
--- a/src/test/regress/expected/copy.out
+++ b/src/test/regress/expected/copy.out
@@ -374,6 +374,8 @@ id
 1
 DROP MATERIALIZED VIEW copytest_mv;
 -- Tests for COPY TO with partitioned tables.
+-- The child table pp_2 has a different column order than the root table pp.
+-- Check if COPY TO exports tuples as the root table's column order.
 CREATE TABLE pp (id int,val int) PARTITION BY RANGE (id);
 CREATE TABLE pp_1 (val int, id int) PARTITION BY RANGE (id);
 CREATE TABLE pp_2 (id int, val int) PARTITION BY RANGE (id);
diff --git a/src/test/regress/sql/copy.sql b/src/test/regress/sql/copy.sql
index 56d506ad4c6..676a8b342b5 100644
--- a/src/test/regress/sql/copy.sql
+++ b/src/test/regress/sql/copy.sql
@@ -407,16 +407,15 @@ COPY copytest_mv(id) TO stdout WITH (header);
 DROP MATERIALIZED VIEW copytest_mv;
 
 -- Tests for COPY TO with partitioned tables.
+-- The child table pp_2 has a different column order than the root table pp.
+-- Check if COPY TO exports tuples as the root table's column order.
 CREATE TABLE pp (id int,val int) PARTITION BY RANGE (id);
 CREATE TABLE pp_1 (val int, id int) PARTITION BY RANGE (id);
 CREATE TABLE pp_2 (id int, val int) PARTITION BY RANGE (id);
 ALTER TABLE pp ATTACH PARTITION pp_1 FOR VALUES FROM (1) TO (5);
 ALTER TABLE pp ATTACH PARTITION pp_2 FOR VALUES FROM (5) TO (10);
-
 CREATE TABLE pp_15 PARTITION OF pp_1 FOR VALUES FROM (1) TO (5);
 CREATE TABLE pp_510 PARTITION OF pp_2 FOR VALUES FROM (5) TO (10);
-
 INSERT INTO pp SELECT g, 10 + g FROM generate_series(1,6) g;
-
 COPY pp TO stdout(header);
 DROP TABLE PP;

From 663cb20c6db29765421b8f0cd386d594e208e236 Mon Sep 17 00:00:00 2001
From: jian he <jian.universality@gmail.com>
Date: Thu, 16 Oct 2025 10:53:51 +0800
Subject: [PATCH v19 1/1] support COPY partitioned_table TO
MIME-Version: 1.0
Content-Type: text/plain; charset=UTF-8
Content-Transfer-Encoding: 8bit

This is for implementation of ``COPY (partitioned_table) TO``.  it will be
faster than ``COPY (select * from partitioned_table) TO``.
If the destination table is a partitioned table, COPY table TO copies the same
rows as SELECT * FROM table.

reivewed by: vignesh C <vignesh21@gmail.com>
reivewed by: David Rowley <dgrowleyml@gmail.com>
reivewed by: Melih Mutlu <m.melihmutlu@gmail.com>
reivewed by: Kirill Reshke <reshkekirill@gmail.com>
reivewed by: Atsushi Torikoshi <torikoshia@oss.nttdata.com>
reivewed by: Álvaro Herrera <alvherre@kurilemu.de>
reivewed by: Masahiko Sawada <sawada.mshk@gmail.com>
reivewed by: Chao Li <li.evan.chao@gmail.com>

discussion: https://postgr.es/m/CACJufxEZt+G19Ors3bQUq-42-61__C=y5k2wk=sHEFRusu7=iQ@mail.gmail.com
commitfest entry: https://commitfest.postgresql.org/patch/5467
---
 .../postgres_fdw/expected/postgres_fdw.out    |   5 +
 contrib/postgres_fdw/sql/postgres_fdw.sql     |   3 +
 doc/src/sgml/ref/copy.sgml                    |   9 +-
 src/backend/commands/copy.c                   |   6 +-
 src/backend/commands/copyto.c                 | 153 ++++++++++++++----
 src/test/regress/expected/copy.out            |  20 +++
 src/test/regress/expected/rowsecurity.out     |  21 +++
 src/test/regress/sql/copy.sql                 |  14 ++
 src/test/regress/sql/rowsecurity.sql          |   3 +
 9 files changed, 200 insertions(+), 34 deletions(-)

diff --git a/contrib/postgres_fdw/expected/postgres_fdw.out b/contrib/postgres_fdw/expected/postgres_fdw.out
index 91bbd0d8c73..cd28126049d 100644
--- a/contrib/postgres_fdw/expected/postgres_fdw.out
+++ b/contrib/postgres_fdw/expected/postgres_fdw.out
@@ -11599,6 +11599,11 @@ SELECT * FROM result_tbl ORDER BY a;
 (3 rows)
 
 DELETE FROM result_tbl;
+-- Test COPY TO when foreign table is partition
+COPY async_pt TO stdout; --error
+ERROR:  cannot copy from foreign table "async_p1"
+DETAIL:  Partition "async_p1" is a foreign table in partitioned table "async_pt"
+HINT:  Try the COPY (SELECT ...) TO variant.
 DROP FOREIGN TABLE async_p3;
 DROP TABLE base_tbl3;
 -- Check case where the partitioned table has local/remote partitions
diff --git a/contrib/postgres_fdw/sql/postgres_fdw.sql b/contrib/postgres_fdw/sql/postgres_fdw.sql
index 3b7da128519..9a8f9e28135 100644
--- a/contrib/postgres_fdw/sql/postgres_fdw.sql
+++ b/contrib/postgres_fdw/sql/postgres_fdw.sql
@@ -3941,6 +3941,9 @@ INSERT INTO result_tbl SELECT * FROM async_pt WHERE b === 505;
 SELECT * FROM result_tbl ORDER BY a;
 DELETE FROM result_tbl;
 
+-- Test COPY TO when foreign table is partition
+COPY async_pt TO stdout; --error
+
 DROP FOREIGN TABLE async_p3;
 DROP TABLE base_tbl3;
 
diff --git a/doc/src/sgml/ref/copy.sgml b/doc/src/sgml/ref/copy.sgml
index c2d1fbc1fbe..ecd300097fc 100644
--- a/doc/src/sgml/ref/copy.sgml
+++ b/doc/src/sgml/ref/copy.sgml
@@ -539,13 +539,16 @@ COPY <replaceable class="parameter">count</replaceable>
 
    <para>
     <command>COPY TO</command> can be used with plain
-    tables and populated materialized views.
-    For example,
+    tables, populated materialized views and partitioned tables.
+    For example, if <replaceable class="parameter">table</replaceable> is a plain table,
     <literal>COPY <replaceable class="parameter">table</replaceable>
     TO</literal> copies the same rows as
     <literal>SELECT * FROM ONLY <replaceable class="parameter">table</replaceable></literal>.
+    If <replaceable class="parameter">table</replaceable> is a partitioned table or a materialized view
+    <literal>COPY <replaceable class="parameter">table</replaceable> TO</literal>
+    copies the same rows as <literal>SELECT * FROM <replaceable class="parameter">table</replaceable></literal>.
     However it doesn't directly support other relation types,
-    such as partitioned tables, inheritance child tables, or views.
+    such as inheritance child tables, or views.
     To copy all rows from such relations, use <literal>COPY (SELECT * FROM
     <replaceable class="parameter">table</replaceable>) TO</literal>.
    </para>
diff --git a/src/backend/commands/copy.c b/src/backend/commands/copy.c
index fae9c41db65..ff443fb5605 100644
--- a/src/backend/commands/copy.c
+++ b/src/backend/commands/copy.c
@@ -251,11 +251,15 @@ DoCopy(ParseState *pstate, const CopyStmt *stmt,
 			 * relation which we have opened and locked.  Use "ONLY" so that
 			 * COPY retrieves rows from only the target table not any
 			 * inheritance children, the same as when RLS doesn't apply.
+			 *
+			 * However, when copying data from a partitioned table, we don't use
+			 * "ONLY", since we need to retrieve rows from its descendant tables
+			 * too.
 			 */
 			from = makeRangeVar(get_namespace_name(RelationGetNamespace(rel)),
 								pstrdup(RelationGetRelationName(rel)),
 								-1);
-			from->inh = false;	/* apply ONLY */
+			from->inh = (rel->rd_rel->relkind == RELKIND_PARTITIONED_TABLE);
 
 			/* Build query */
 			select = makeNode(SelectStmt);
diff --git a/src/backend/commands/copyto.c b/src/backend/commands/copyto.c
index e5781155cdf..a1919c6db43 100644
--- a/src/backend/commands/copyto.c
+++ b/src/backend/commands/copyto.c
@@ -18,7 +18,9 @@
 #include <unistd.h>
 #include <sys/stat.h>
 
+#include "access/table.h"
 #include "access/tableam.h"
+#include "catalog/pg_inherits.h"
 #include "commands/copyapi.h"
 #include "commands/progress.h"
 #include "executor/execdesc.h"
@@ -86,6 +88,7 @@ typedef struct CopyToStateData
 
 	CopyFormatOptions opts;
 	Node	   *whereClause;	/* WHERE condition (or NULL) */
+	List	   *partitions;		/* OID list of partitions to copy data from */
 
 	/*
 	 * Working state
@@ -116,6 +119,8 @@ static void CopyOneRowTo(CopyToState cstate, TupleTableSlot *slot);
 static void CopyAttributeOutText(CopyToState cstate, const char *string);
 static void CopyAttributeOutCSV(CopyToState cstate, const char *string,
 								bool use_quote);
+static void CopyRelationTo(CopyToState cstate, Relation rel, Relation root_rel,
+						   uint64 *processed);
 
 /* built-in format-specific routines */
 static void CopyToTextLikeStart(CopyToState cstate, TupleDesc tupDesc);
@@ -602,6 +607,10 @@ EndCopy(CopyToState cstate)
 	pgstat_progress_end_command();
 
 	MemoryContextDelete(cstate->copycontext);
+
+	if (cstate->partitions)
+		list_free(cstate->partitions);
+
 	pfree(cstate);
 }
 
@@ -643,6 +652,7 @@ BeginCopyTo(ParseState *pstate,
 		PROGRESS_COPY_COMMAND_TO,
 		0
 	};
+	List	   *children = NIL;
 
 	if (rel != NULL && rel->rd_rel->relkind != RELKIND_RELATION)
 	{
@@ -673,11 +683,34 @@ BeginCopyTo(ParseState *pstate,
 					 errmsg("cannot copy from sequence \"%s\"",
 							RelationGetRelationName(rel))));
 		else if (rel->rd_rel->relkind == RELKIND_PARTITIONED_TABLE)
-			ereport(ERROR,
-					(errcode(ERRCODE_WRONG_OBJECT_TYPE),
-					 errmsg("cannot copy from partitioned table \"%s\"",
-							RelationGetRelationName(rel)),
-					 errhint("Try the COPY (SELECT ...) TO variant.")));
+		{
+			/*
+			 * Collect OIDs of relation containing data, so that later
+			 * DoCopyTo can copy the data from them.
+			 */
+			children = find_all_inheritors(RelationGetRelid(rel), AccessShareLock, NULL);
+
+			foreach_oid(child, children)
+			{
+				char		relkind = get_rel_relkind(child);
+
+				if (relkind == RELKIND_FOREIGN_TABLE)
+				{
+					char	   *relation_name = get_rel_name(child);
+
+					ereport(ERROR,
+							errcode(ERRCODE_WRONG_OBJECT_TYPE),
+							errmsg("cannot copy from foreign table \"%s\"", relation_name),
+							errdetail("Partition \"%s\" is a foreign table in partitioned table \"%s\"",
+									  relation_name, RelationGetRelationName(rel)),
+							errhint("Try the COPY (SELECT ...) TO variant."));
+				}
+
+				/* Exclude tables with no data */
+				if (RELKIND_HAS_PARTITIONS(relkind))
+					children = foreach_delete_current(children, child);
+			}
+		}
 		else
 			ereport(ERROR,
 					(errcode(ERRCODE_WRONG_OBJECT_TYPE),
@@ -713,6 +746,7 @@ BeginCopyTo(ParseState *pstate,
 		cstate->rel = rel;
 
 		tupDesc = RelationGetDescr(cstate->rel);
+		cstate->partitions = children;
 	}
 	else
 	{
@@ -722,6 +756,7 @@ BeginCopyTo(ParseState *pstate,
 		DestReceiver *dest;
 
 		cstate->rel = NULL;
+		cstate->partitions = NIL;
 
 		/*
 		 * Run parse analysis and rewrite.  Note this also acquires sufficient
@@ -1030,7 +1065,7 @@ DoCopyTo(CopyToState cstate)
 	TupleDesc	tupDesc;
 	int			num_phys_attrs;
 	ListCell   *cur;
-	uint64		processed;
+	uint64		processed = 0;
 
 	if (fe_copy)
 		SendCopyBegin(cstate);
@@ -1070,33 +1105,24 @@ DoCopyTo(CopyToState cstate)
 
 	if (cstate->rel)
 	{
-		TupleTableSlot *slot;
-		TableScanDesc scandesc;
-
-		scandesc = table_beginscan(cstate->rel, GetActiveSnapshot(), 0, NULL);
-		slot = table_slot_create(cstate->rel, NULL);
-
-		processed = 0;
-		while (table_scan_getnextslot(scandesc, ForwardScanDirection, slot))
+		/*
+		 * If COPY TO source table is a partitioned table, then open each
+		 * partition and process each individual partition.
+		 */
+		if (cstate->rel->rd_rel->relkind == RELKIND_PARTITIONED_TABLE)
 		{
-			CHECK_FOR_INTERRUPTS();
+			foreach_oid(child, cstate->partitions)
+			{
+				Relation	scan_rel;
 
-			/* Deconstruct the tuple ... */
-			slot_getallattrs(slot);
-
-			/* Format and send the data */
-			CopyOneRowTo(cstate, slot);
-
-			/*
-			 * Increment the number of processed tuples, and report the
-			 * progress.
-			 */
-			pgstat_progress_update_param(PROGRESS_COPY_TUPLES_PROCESSED,
-										 ++processed);
+				/* We already got the lock in BeginCopyTo */
+				scan_rel = table_open(child, NoLock);
+				CopyRelationTo(cstate, scan_rel, cstate->rel, &processed);
+				table_close(scan_rel, NoLock);
+			}
 		}
-
-		ExecDropSingleTupleTableSlot(slot);
-		table_endscan(scandesc);
+		else
+			CopyRelationTo(cstate, cstate->rel, NULL, &processed);
 	}
 	else
 	{
@@ -1115,6 +1141,73 @@ DoCopyTo(CopyToState cstate)
 	return processed;
 }
 
+/*
+ * Scans a single table and exports its rows to the COPY destination.
+ *
+ * root_rel can be set to the root table of rel if rel is a partition
+ * table so that we can send tuples in root_rel's rowtype, which might
+ * differ from individual partitions.
+*/
+static void
+CopyRelationTo(CopyToState cstate, Relation rel, Relation root_rel, uint64 *processed)
+{
+	TupleTableSlot *slot;
+	TableScanDesc scandesc;
+	AttrMap    *map = NULL;
+	TupleTableSlot *root_slot = NULL;
+
+	scandesc = table_beginscan(rel, GetActiveSnapshot(), 0, NULL);
+	slot = table_slot_create(rel, NULL);
+
+	/*
+	 * If we are exporting partition data here, we check if converting tuples
+	 * to the root table's rowtype, because a partition might have column
+	 * order different than its root table.
+	 */
+	if (root_rel != NULL)
+	{
+		root_slot = table_slot_create(root_rel, NULL);
+		map = build_attrmap_by_name_if_req(RelationGetDescr(root_rel),
+										   RelationGetDescr(rel),
+										   false);
+	}
+
+	while (table_scan_getnextslot(scandesc, ForwardScanDirection, slot))
+	{
+		TupleTableSlot *copyslot;
+
+		CHECK_FOR_INTERRUPTS();
+
+		if (map != NULL)
+			copyslot = execute_attr_map_slot(map, slot, root_slot);
+		else
+		{
+			/* Deconstruct the tuple */
+			slot_getallattrs(slot);
+			copyslot = slot;
+		}
+
+		/* Format and send the data */
+		CopyOneRowTo(cstate, copyslot);
+
+		/*
+		 * Increment the number of processed tuples, and report the progress.
+		 */
+		pgstat_progress_update_param(PROGRESS_COPY_TUPLES_PROCESSED,
+									 ++(*processed));
+	}
+
+	ExecDropSingleTupleTableSlot(slot);
+
+	if (root_slot != NULL)
+		ExecDropSingleTupleTableSlot(root_slot);
+
+	if (map != NULL)
+		free_attrmap(map);
+
+	table_endscan(scandesc);
+}
+
 /*
  * Emit one row during DoCopyTo().
  */
diff --git a/src/test/regress/expected/copy.out b/src/test/regress/expected/copy.out
index ac66eb55aee..24e0f472f14 100644
--- a/src/test/regress/expected/copy.out
+++ b/src/test/regress/expected/copy.out
@@ -373,3 +373,23 @@ COPY copytest_mv(id) TO stdout WITH (header);
 id
 1
 DROP MATERIALIZED VIEW copytest_mv;
+-- Tests for COPY TO with partitioned tables.
+-- The child table pp_2 has a different column order than the root table pp.
+-- Check if COPY TO exports tuples as the root table's column order.
+CREATE TABLE pp (id int,val int) PARTITION BY RANGE (id);
+CREATE TABLE pp_1 (val int, id int) PARTITION BY RANGE (id);
+CREATE TABLE pp_2 (id int, val int) PARTITION BY RANGE (id);
+ALTER TABLE pp ATTACH PARTITION pp_1 FOR VALUES FROM (1) TO (5);
+ALTER TABLE pp ATTACH PARTITION pp_2 FOR VALUES FROM (5) TO (10);
+CREATE TABLE pp_15 PARTITION OF pp_1 FOR VALUES FROM (1) TO (5);
+CREATE TABLE pp_510 PARTITION OF pp_2 FOR VALUES FROM (5) TO (10);
+INSERT INTO pp SELECT g, 10 + g FROM generate_series(1,6) g;
+COPY pp TO stdout(header);
+id	val
+1	11
+2	12
+3	13
+4	14
+5	15
+6	16
+DROP TABLE PP;
diff --git a/src/test/regress/expected/rowsecurity.out b/src/test/regress/expected/rowsecurity.out
index 5a172c5d91c..42b78a24603 100644
--- a/src/test/regress/expected/rowsecurity.out
+++ b/src/test/regress/expected/rowsecurity.out
@@ -986,6 +986,11 @@ NOTICE:  f_leak => my first satire
    9 |  11 |      1 | regress_rls_dave  | awesome science fiction
 (4 rows)
 
+COPY part_document TO stdout WITH (DELIMITER ',');
+1,11,1,regress_rls_bob,my first novel
+6,11,1,regress_rls_carol,great science fiction
+9,11,1,regress_rls_dave,awesome science fiction
+4,55,1,regress_rls_bob,my first satire
 EXPLAIN (COSTS OFF) SELECT * FROM part_document WHERE f_leak(dtitle);
                                QUERY PLAN                                
 -------------------------------------------------------------------------
@@ -1028,6 +1033,17 @@ NOTICE:  f_leak => awesome technology book
   10 |  99 |      2 | regress_rls_dave  | awesome technology book
 (10 rows)
 
+COPY part_document TO stdout WITH (DELIMITER ',');
+1,11,1,regress_rls_bob,my first novel
+2,11,2,regress_rls_bob,my second novel
+6,11,1,regress_rls_carol,great science fiction
+9,11,1,regress_rls_dave,awesome science fiction
+4,55,1,regress_rls_bob,my first satire
+8,55,2,regress_rls_carol,great satire
+3,99,2,regress_rls_bob,my science textbook
+5,99,2,regress_rls_bob,my history book
+7,99,2,regress_rls_carol,great technology book
+10,99,2,regress_rls_dave,awesome technology book
 EXPLAIN (COSTS OFF) SELECT * FROM part_document WHERE f_leak(dtitle);
                                QUERY PLAN                                
 -------------------------------------------------------------------------
@@ -1058,6 +1074,11 @@ NOTICE:  f_leak => awesome science fiction
    9 |  11 |      1 | regress_rls_dave  | awesome science fiction
 (4 rows)
 
+COPY part_document TO stdout WITH (DELIMITER ',');
+1,11,1,regress_rls_bob,my first novel
+2,11,2,regress_rls_bob,my second novel
+6,11,1,regress_rls_carol,great science fiction
+9,11,1,regress_rls_dave,awesome science fiction
 EXPLAIN (COSTS OFF) SELECT * FROM part_document WHERE f_leak(dtitle);
                                     QUERY PLAN                                    
 ----------------------------------------------------------------------------------
diff --git a/src/test/regress/sql/copy.sql b/src/test/regress/sql/copy.sql
index a1316c73bac..676a8b342b5 100644
--- a/src/test/regress/sql/copy.sql
+++ b/src/test/regress/sql/copy.sql
@@ -405,3 +405,17 @@ COPY copytest_mv(id) TO stdout WITH (header);
 REFRESH MATERIALIZED VIEW copytest_mv;
 COPY copytest_mv(id) TO stdout WITH (header);
 DROP MATERIALIZED VIEW copytest_mv;
+
+-- Tests for COPY TO with partitioned tables.
+-- The child table pp_2 has a different column order than the root table pp.
+-- Check if COPY TO exports tuples as the root table's column order.
+CREATE TABLE pp (id int,val int) PARTITION BY RANGE (id);
+CREATE TABLE pp_1 (val int, id int) PARTITION BY RANGE (id);
+CREATE TABLE pp_2 (id int, val int) PARTITION BY RANGE (id);
+ALTER TABLE pp ATTACH PARTITION pp_1 FOR VALUES FROM (1) TO (5);
+ALTER TABLE pp ATTACH PARTITION pp_2 FOR VALUES FROM (5) TO (10);
+CREATE TABLE pp_15 PARTITION OF pp_1 FOR VALUES FROM (1) TO (5);
+CREATE TABLE pp_510 PARTITION OF pp_2 FOR VALUES FROM (5) TO (10);
+INSERT INTO pp SELECT g, 10 + g FROM generate_series(1,6) g;
+COPY pp TO stdout(header);
+DROP TABLE PP;
diff --git a/src/test/regress/sql/rowsecurity.sql b/src/test/regress/sql/rowsecurity.sql
index 21ac0ca51ee..2d1be543391 100644
--- a/src/test/regress/sql/rowsecurity.sql
+++ b/src/test/regress/sql/rowsecurity.sql
@@ -362,16 +362,19 @@ SELECT * FROM pg_policies WHERE schemaname = 'regress_rls_schema' AND tablename
 SET SESSION AUTHORIZATION regress_rls_bob;
 SET row_security TO ON;
 SELECT * FROM part_document WHERE f_leak(dtitle) ORDER BY did;
+COPY part_document TO stdout WITH (DELIMITER ',');
 EXPLAIN (COSTS OFF) SELECT * FROM part_document WHERE f_leak(dtitle);
 
 -- viewpoint from regress_rls_carol
 SET SESSION AUTHORIZATION regress_rls_carol;
 SELECT * FROM part_document WHERE f_leak(dtitle) ORDER BY did;
+COPY part_document TO stdout WITH (DELIMITER ',');
 EXPLAIN (COSTS OFF) SELECT * FROM part_document WHERE f_leak(dtitle);
 
 -- viewpoint from regress_rls_dave
 SET SESSION AUTHORIZATION regress_rls_dave;
 SELECT * FROM part_document WHERE f_leak(dtitle) ORDER BY did;
+COPY part_document TO stdout WITH (DELIMITER ',');
 EXPLAIN (COSTS OFF) SELECT * FROM part_document WHERE f_leak(dtitle);
 
 -- pp1 ERROR
-- 
2.34.1

From 00c6d688da820f1d9794e96280d97c954e57e7be Mon Sep 17 00:00:00 2001
From: jian he <jian.universality@gmail.com>
Date: Thu, 16 Oct 2025 10:53:51 +0800
Subject: [PATCH v20] Support COPY TO for partitioned tables.
MIME-Version: 1.0
Content-Type: text/plain; charset=UTF-8
Content-Transfer-Encoding: 8bit

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
---
 .../postgres_fdw/expected/postgres_fdw.out    |   5 +
 contrib/postgres_fdw/sql/postgres_fdw.sql     |   3 +
 doc/src/sgml/ref/copy.sgml                    |  11 +-
 src/backend/commands/copy.c                   |   6 +-
 src/backend/commands/copyto.c                 | 153 ++++++++++++++----
 src/test/regress/expected/copy.out            |  20 +++
 src/test/regress/expected/rowsecurity.out     |  21 +++
 src/test/regress/sql/copy.sql                 |  14 ++
 src/test/regress/sql/rowsecurity.sql          |   3 +
 9 files changed, 200 insertions(+), 36 deletions(-)

diff --git a/contrib/postgres_fdw/expected/postgres_fdw.out b/contrib/postgres_fdw/expected/postgres_fdw.out
index 91bbd0d8c73..cd28126049d 100644
--- a/contrib/postgres_fdw/expected/postgres_fdw.out
+++ b/contrib/postgres_fdw/expected/postgres_fdw.out
@@ -11599,6 +11599,11 @@ SELECT * FROM result_tbl ORDER BY a;
 (3 rows)
 
 DELETE FROM result_tbl;
+-- Test COPY TO when foreign table is partition
+COPY async_pt TO stdout; --error
+ERROR:  cannot copy from foreign table "async_p1"
+DETAIL:  Partition "async_p1" is a foreign table in partitioned table "async_pt"
+HINT:  Try the COPY (SELECT ...) TO variant.
 DROP FOREIGN TABLE async_p3;
 DROP TABLE base_tbl3;
 -- Check case where the partitioned table has local/remote partitions
diff --git a/contrib/postgres_fdw/sql/postgres_fdw.sql b/contrib/postgres_fdw/sql/postgres_fdw.sql
index 3b7da128519..9a8f9e28135 100644
--- a/contrib/postgres_fdw/sql/postgres_fdw.sql
+++ b/contrib/postgres_fdw/sql/postgres_fdw.sql
@@ -3941,6 +3941,9 @@ INSERT INTO result_tbl SELECT * FROM async_pt WHERE b === 505;
 SELECT * FROM result_tbl ORDER BY a;
 DELETE FROM result_tbl;
 
+-- Test COPY TO when foreign table is partition
+COPY async_pt TO stdout; --error
+
 DROP FOREIGN TABLE async_p3;
 DROP TABLE base_tbl3;
 
diff --git a/doc/src/sgml/ref/copy.sgml b/doc/src/sgml/ref/copy.sgml
index c2d1fbc1fbe..fdc24b36bb8 100644
--- a/doc/src/sgml/ref/copy.sgml
+++ b/doc/src/sgml/ref/copy.sgml
@@ -539,13 +539,14 @@ COPY <replaceable class="parameter">count</replaceable>
 
    <para>
     <command>COPY TO</command> can be used with plain
-    tables and populated materialized views.
-    For example,
-    <literal>COPY <replaceable class="parameter">table</replaceable>
-    TO</literal> copies the same rows as
+    tables, populated materialized views, and partitioned tables.
+    For non-partitioned tables, COPY <replaceable class="parameter">table</replaceable>
+    copies the same rows as
     <literal>SELECT * FROM ONLY <replaceable class="parameter">table</replaceable></literal>.
+    For partitioned tables, it copies the same rows as
+    <literal>SELECT * FROM <replaceable class="parameter">table</replaceable></literal>.
     However it doesn't directly support other relation types,
-    such as partitioned tables, inheritance child tables, or views.
+    such as inheritance child tables, or views.
     To copy all rows from such relations, use <literal>COPY (SELECT * FROM
     <replaceable class="parameter">table</replaceable>) TO</literal>.
    </para>
diff --git a/src/backend/commands/copy.c b/src/backend/commands/copy.c
index fae9c41db65..44020d0ae80 100644
--- a/src/backend/commands/copy.c
+++ b/src/backend/commands/copy.c
@@ -251,11 +251,15 @@ DoCopy(ParseState *pstate, const CopyStmt *stmt,
 			 * relation which we have opened and locked.  Use "ONLY" so that
 			 * COPY retrieves rows from only the target table not any
 			 * inheritance children, the same as when RLS doesn't apply.
+			 *
+			 * However, when copying data from a partitioned table, we don't
+			 * use "ONLY", since we need to retrieve rows from its descendant
+			 * tables too.
 			 */
 			from = makeRangeVar(get_namespace_name(RelationGetNamespace(rel)),
 								pstrdup(RelationGetRelationName(rel)),
 								-1);
-			from->inh = false;	/* apply ONLY */
+			from->inh = (rel->rd_rel->relkind == RELKIND_PARTITIONED_TABLE);
 
 			/* Build query */
 			select = makeNode(SelectStmt);
diff --git a/src/backend/commands/copyto.c b/src/backend/commands/copyto.c
index e5781155cdf..a1919c6db43 100644
--- a/src/backend/commands/copyto.c
+++ b/src/backend/commands/copyto.c
@@ -18,7 +18,9 @@
 #include <unistd.h>
 #include <sys/stat.h>
 
+#include "access/table.h"
 #include "access/tableam.h"
+#include "catalog/pg_inherits.h"
 #include "commands/copyapi.h"
 #include "commands/progress.h"
 #include "executor/execdesc.h"
@@ -86,6 +88,7 @@ typedef struct CopyToStateData
 
 	CopyFormatOptions opts;
 	Node	   *whereClause;	/* WHERE condition (or NULL) */
+	List	   *partitions;		/* OID list of partitions to copy data from */
 
 	/*
 	 * Working state
@@ -116,6 +119,8 @@ static void CopyOneRowTo(CopyToState cstate, TupleTableSlot *slot);
 static void CopyAttributeOutText(CopyToState cstate, const char *string);
 static void CopyAttributeOutCSV(CopyToState cstate, const char *string,
 								bool use_quote);
+static void CopyRelationTo(CopyToState cstate, Relation rel, Relation root_rel,
+						   uint64 *processed);
 
 /* built-in format-specific routines */
 static void CopyToTextLikeStart(CopyToState cstate, TupleDesc tupDesc);
@@ -602,6 +607,10 @@ EndCopy(CopyToState cstate)
 	pgstat_progress_end_command();
 
 	MemoryContextDelete(cstate->copycontext);
+
+	if (cstate->partitions)
+		list_free(cstate->partitions);
+
 	pfree(cstate);
 }
 
@@ -643,6 +652,7 @@ BeginCopyTo(ParseState *pstate,
 		PROGRESS_COPY_COMMAND_TO,
 		0
 	};
+	List	   *children = NIL;
 
 	if (rel != NULL && rel->rd_rel->relkind != RELKIND_RELATION)
 	{
@@ -673,11 +683,34 @@ BeginCopyTo(ParseState *pstate,
 					 errmsg("cannot copy from sequence \"%s\"",
 							RelationGetRelationName(rel))));
 		else if (rel->rd_rel->relkind == RELKIND_PARTITIONED_TABLE)
-			ereport(ERROR,
-					(errcode(ERRCODE_WRONG_OBJECT_TYPE),
-					 errmsg("cannot copy from partitioned table \"%s\"",
-							RelationGetRelationName(rel)),
-					 errhint("Try the COPY (SELECT ...) TO variant.")));
+		{
+			/*
+			 * Collect OIDs of relation containing data, so that later
+			 * DoCopyTo can copy the data from them.
+			 */
+			children = find_all_inheritors(RelationGetRelid(rel), AccessShareLock, NULL);
+
+			foreach_oid(child, children)
+			{
+				char		relkind = get_rel_relkind(child);
+
+				if (relkind == RELKIND_FOREIGN_TABLE)
+				{
+					char	   *relation_name = get_rel_name(child);
+
+					ereport(ERROR,
+							errcode(ERRCODE_WRONG_OBJECT_TYPE),
+							errmsg("cannot copy from foreign table \"%s\"", relation_name),
+							errdetail("Partition \"%s\" is a foreign table in partitioned table \"%s\"",
+									  relation_name, RelationGetRelationName(rel)),
+							errhint("Try the COPY (SELECT ...) TO variant."));
+				}
+
+				/* Exclude tables with no data */
+				if (RELKIND_HAS_PARTITIONS(relkind))
+					children = foreach_delete_current(children, child);
+			}
+		}
 		else
 			ereport(ERROR,
 					(errcode(ERRCODE_WRONG_OBJECT_TYPE),
@@ -713,6 +746,7 @@ BeginCopyTo(ParseState *pstate,
 		cstate->rel = rel;
 
 		tupDesc = RelationGetDescr(cstate->rel);
+		cstate->partitions = children;
 	}
 	else
 	{
@@ -722,6 +756,7 @@ BeginCopyTo(ParseState *pstate,
 		DestReceiver *dest;
 
 		cstate->rel = NULL;
+		cstate->partitions = NIL;
 
 		/*
 		 * Run parse analysis and rewrite.  Note this also acquires sufficient
@@ -1030,7 +1065,7 @@ DoCopyTo(CopyToState cstate)
 	TupleDesc	tupDesc;
 	int			num_phys_attrs;
 	ListCell   *cur;
-	uint64		processed;
+	uint64		processed = 0;
 
 	if (fe_copy)
 		SendCopyBegin(cstate);
@@ -1070,33 +1105,24 @@ DoCopyTo(CopyToState cstate)
 
 	if (cstate->rel)
 	{
-		TupleTableSlot *slot;
-		TableScanDesc scandesc;
-
-		scandesc = table_beginscan(cstate->rel, GetActiveSnapshot(), 0, NULL);
-		slot = table_slot_create(cstate->rel, NULL);
-
-		processed = 0;
-		while (table_scan_getnextslot(scandesc, ForwardScanDirection, slot))
+		/*
+		 * If COPY TO source table is a partitioned table, then open each
+		 * partition and process each individual partition.
+		 */
+		if (cstate->rel->rd_rel->relkind == RELKIND_PARTITIONED_TABLE)
 		{
-			CHECK_FOR_INTERRUPTS();
-
-			/* Deconstruct the tuple ... */
-			slot_getallattrs(slot);
-
-			/* Format and send the data */
-			CopyOneRowTo(cstate, slot);
+			foreach_oid(child, cstate->partitions)
+			{
+				Relation	scan_rel;
 
-			/*
-			 * Increment the number of processed tuples, and report the
-			 * progress.
-			 */
-			pgstat_progress_update_param(PROGRESS_COPY_TUPLES_PROCESSED,
-										 ++processed);
+				/* We already got the lock in BeginCopyTo */
+				scan_rel = table_open(child, NoLock);
+				CopyRelationTo(cstate, scan_rel, cstate->rel, &processed);
+				table_close(scan_rel, NoLock);
+			}
 		}
-
-		ExecDropSingleTupleTableSlot(slot);
-		table_endscan(scandesc);
+		else
+			CopyRelationTo(cstate, cstate->rel, NULL, &processed);
 	}
 	else
 	{
@@ -1115,6 +1141,73 @@ DoCopyTo(CopyToState cstate)
 	return processed;
 }
 
+/*
+ * Scans a single table and exports its rows to the COPY destination.
+ *
+ * root_rel can be set to the root table of rel if rel is a partition
+ * table so that we can send tuples in root_rel's rowtype, which might
+ * differ from individual partitions.
+*/
+static void
+CopyRelationTo(CopyToState cstate, Relation rel, Relation root_rel, uint64 *processed)
+{
+	TupleTableSlot *slot;
+	TableScanDesc scandesc;
+	AttrMap    *map = NULL;
+	TupleTableSlot *root_slot = NULL;
+
+	scandesc = table_beginscan(rel, GetActiveSnapshot(), 0, NULL);
+	slot = table_slot_create(rel, NULL);
+
+	/*
+	 * If we are exporting partition data here, we check if converting tuples
+	 * to the root table's rowtype, because a partition might have column
+	 * order different than its root table.
+	 */
+	if (root_rel != NULL)
+	{
+		root_slot = table_slot_create(root_rel, NULL);
+		map = build_attrmap_by_name_if_req(RelationGetDescr(root_rel),
+										   RelationGetDescr(rel),
+										   false);
+	}
+
+	while (table_scan_getnextslot(scandesc, ForwardScanDirection, slot))
+	{
+		TupleTableSlot *copyslot;
+
+		CHECK_FOR_INTERRUPTS();
+
+		if (map != NULL)
+			copyslot = execute_attr_map_slot(map, slot, root_slot);
+		else
+		{
+			/* Deconstruct the tuple */
+			slot_getallattrs(slot);
+			copyslot = slot;
+		}
+
+		/* Format and send the data */
+		CopyOneRowTo(cstate, copyslot);
+
+		/*
+		 * Increment the number of processed tuples, and report the progress.
+		 */
+		pgstat_progress_update_param(PROGRESS_COPY_TUPLES_PROCESSED,
+									 ++(*processed));
+	}
+
+	ExecDropSingleTupleTableSlot(slot);
+
+	if (root_slot != NULL)
+		ExecDropSingleTupleTableSlot(root_slot);
+
+	if (map != NULL)
+		free_attrmap(map);
+
+	table_endscan(scandesc);
+}
+
 /*
  * Emit one row during DoCopyTo().
  */
diff --git a/src/test/regress/expected/copy.out b/src/test/regress/expected/copy.out
index ac66eb55aee..24e0f472f14 100644
--- a/src/test/regress/expected/copy.out
+++ b/src/test/regress/expected/copy.out
@@ -373,3 +373,23 @@ COPY copytest_mv(id) TO stdout WITH (header);
 id
 1
 DROP MATERIALIZED VIEW copytest_mv;
+-- Tests for COPY TO with partitioned tables.
+-- The child table pp_2 has a different column order than the root table pp.
+-- Check if COPY TO exports tuples as the root table's column order.
+CREATE TABLE pp (id int,val int) PARTITION BY RANGE (id);
+CREATE TABLE pp_1 (val int, id int) PARTITION BY RANGE (id);
+CREATE TABLE pp_2 (id int, val int) PARTITION BY RANGE (id);
+ALTER TABLE pp ATTACH PARTITION pp_1 FOR VALUES FROM (1) TO (5);
+ALTER TABLE pp ATTACH PARTITION pp_2 FOR VALUES FROM (5) TO (10);
+CREATE TABLE pp_15 PARTITION OF pp_1 FOR VALUES FROM (1) TO (5);
+CREATE TABLE pp_510 PARTITION OF pp_2 FOR VALUES FROM (5) TO (10);
+INSERT INTO pp SELECT g, 10 + g FROM generate_series(1,6) g;
+COPY pp TO stdout(header);
+id	val
+1	11
+2	12
+3	13
+4	14
+5	15
+6	16
+DROP TABLE PP;
diff --git a/src/test/regress/expected/rowsecurity.out b/src/test/regress/expected/rowsecurity.out
index 5a172c5d91c..42b78a24603 100644
--- a/src/test/regress/expected/rowsecurity.out
+++ b/src/test/regress/expected/rowsecurity.out
@@ -986,6 +986,11 @@ NOTICE:  f_leak => my first satire
    9 |  11 |      1 | regress_rls_dave  | awesome science fiction
 (4 rows)
 
+COPY part_document TO stdout WITH (DELIMITER ',');
+1,11,1,regress_rls_bob,my first novel
+6,11,1,regress_rls_carol,great science fiction
+9,11,1,regress_rls_dave,awesome science fiction
+4,55,1,regress_rls_bob,my first satire
 EXPLAIN (COSTS OFF) SELECT * FROM part_document WHERE f_leak(dtitle);
                                QUERY PLAN                                
 -------------------------------------------------------------------------
@@ -1028,6 +1033,17 @@ NOTICE:  f_leak => awesome technology book
   10 |  99 |      2 | regress_rls_dave  | awesome technology book
 (10 rows)
 
+COPY part_document TO stdout WITH (DELIMITER ',');
+1,11,1,regress_rls_bob,my first novel
+2,11,2,regress_rls_bob,my second novel
+6,11,1,regress_rls_carol,great science fiction
+9,11,1,regress_rls_dave,awesome science fiction
+4,55,1,regress_rls_bob,my first satire
+8,55,2,regress_rls_carol,great satire
+3,99,2,regress_rls_bob,my science textbook
+5,99,2,regress_rls_bob,my history book
+7,99,2,regress_rls_carol,great technology book
+10,99,2,regress_rls_dave,awesome technology book
 EXPLAIN (COSTS OFF) SELECT * FROM part_document WHERE f_leak(dtitle);
                                QUERY PLAN                                
 -------------------------------------------------------------------------
@@ -1058,6 +1074,11 @@ NOTICE:  f_leak => awesome science fiction
    9 |  11 |      1 | regress_rls_dave  | awesome science fiction
 (4 rows)
 
+COPY part_document TO stdout WITH (DELIMITER ',');
+1,11,1,regress_rls_bob,my first novel
+2,11,2,regress_rls_bob,my second novel
+6,11,1,regress_rls_carol,great science fiction
+9,11,1,regress_rls_dave,awesome science fiction
 EXPLAIN (COSTS OFF) SELECT * FROM part_document WHERE f_leak(dtitle);
                                     QUERY PLAN                                    
 ----------------------------------------------------------------------------------
diff --git a/src/test/regress/sql/copy.sql b/src/test/regress/sql/copy.sql
index a1316c73bac..676a8b342b5 100644
--- a/src/test/regress/sql/copy.sql
+++ b/src/test/regress/sql/copy.sql
@@ -405,3 +405,17 @@ COPY copytest_mv(id) TO stdout WITH (header);
 REFRESH MATERIALIZED VIEW copytest_mv;
 COPY copytest_mv(id) TO stdout WITH (header);
 DROP MATERIALIZED VIEW copytest_mv;
+
+-- Tests for COPY TO with partitioned tables.
+-- The child table pp_2 has a different column order than the root table pp.
+-- Check if COPY TO exports tuples as the root table's column order.
+CREATE TABLE pp (id int,val int) PARTITION BY RANGE (id);
+CREATE TABLE pp_1 (val int, id int) PARTITION BY RANGE (id);
+CREATE TABLE pp_2 (id int, val int) PARTITION BY RANGE (id);
+ALTER TABLE pp ATTACH PARTITION pp_1 FOR VALUES FROM (1) TO (5);
+ALTER TABLE pp ATTACH PARTITION pp_2 FOR VALUES FROM (5) TO (10);
+CREATE TABLE pp_15 PARTITION OF pp_1 FOR VALUES FROM (1) TO (5);
+CREATE TABLE pp_510 PARTITION OF pp_2 FOR VALUES FROM (5) TO (10);
+INSERT INTO pp SELECT g, 10 + g FROM generate_series(1,6) g;
+COPY pp TO stdout(header);
+DROP TABLE PP;
diff --git a/src/test/regress/sql/rowsecurity.sql b/src/test/regress/sql/rowsecurity.sql
index 21ac0ca51ee..2d1be543391 100644
--- a/src/test/regress/sql/rowsecurity.sql
+++ b/src/test/regress/sql/rowsecurity.sql
@@ -362,16 +362,19 @@ SELECT * FROM pg_policies WHERE schemaname = 'regress_rls_schema' AND tablename
 SET SESSION AUTHORIZATION regress_rls_bob;
 SET row_security TO ON;
 SELECT * FROM part_document WHERE f_leak(dtitle) ORDER BY did;
+COPY part_document TO stdout WITH (DELIMITER ',');
 EXPLAIN (COSTS OFF) SELECT * FROM part_document WHERE f_leak(dtitle);
 
 -- viewpoint from regress_rls_carol
 SET SESSION AUTHORIZATION regress_rls_carol;
 SELECT * FROM part_document WHERE f_leak(dtitle) ORDER BY did;
+COPY part_document TO stdout WITH (DELIMITER ',');
 EXPLAIN (COSTS OFF) SELECT * FROM part_document WHERE f_leak(dtitle);
 
 -- viewpoint from regress_rls_dave
 SET SESSION AUTHORIZATION regress_rls_dave;
 SELECT * FROM part_document WHERE f_leak(dtitle) ORDER BY did;
+COPY part_document TO stdout WITH (DELIMITER ',');
 EXPLAIN (COSTS OFF) SELECT * FROM part_document WHERE f_leak(dtitle);
 
 -- pp1 ERROR
-- 
2.47.3