Subject: Re: Emitting JSON to file using COPY TO

From: jian he <[email-masked]>
Date: Mon, 27 Jan 2025 16:16:26 +0800

hi.

There are two ways we can use to represent the new copy format: json.
1.
typedef struct CopyFormatOptions
{
    bool        binary;            /* binary format? */
    bool        freeze;            /* freeze rows on loading? */
    bool        csv_mode;        /* Comma Separated Value format? */
    bool        json_mode;        /* JSON format? */
    ...
}

2.
typedef struct CopyFormatOptions
{
    CopyFormat    format;            /* format of the COPY operation */
    .....
}

typedef enum CopyFormat
{
    COPY_FORMAT_TEXT = 0,
    COPY_FORMAT_BINARY,
    COPY_FORMAT_CSV,
    COPY_FORMAT_JSON,
} CopyFormat;

both the sizeof(cstate->opts) (CopyToStateData.CopyFormatOptions) is 184.
so the struct size will not influence the performance.

I also did some benchmarks when using CopyFormat.
the following are the benchmarks info:
-------------------------------------------------------------------------------------------------------
create unlogged table t as select g from generate_series(1, 1_000_000) g;

build_type=release patch:
copy t to '/dev/null' json \watch i=0.1 c=10
last execution Time: 108.741 ms

copy t to '/dev/null' (format text)  \watch i=0.1 c=10
last execution Time: 42.600 ms

build_type=release master:
copy t to '/dev/null' (format text)  \watch i=0.1 c=10
last execution Time Time: 42.948 ms

---------------------------------------------------------
so a new version is attached, using the struct CopyFormatOptions.

changes mainly in CopyOneRowTo.
now it is:
""""
        if(!cstate->rel)
        {
            memcpy(TupleDescAttr(slot->tts_tupleDescriptor, 0),
                   TupleDescAttr(cstate->queryDesc->tupDesc, 0),
                   cstate->queryDesc->tupDesc->natts *
sizeof(FormData_pg_attribute));
            for (int i = 0; i < cstate->queryDesc->tupDesc->natts; i++)
                populate_compact_attribute(slot->tts_tupleDescriptor, i);
            BlessTupleDesc(slot->tts_tupleDescriptor);
        }
""""
reasoning for change:
for composite_to_json to construct json key, we only need
FormData_pg_attribute.attname
but code path
composite_to_json->fastgetattr->TupleDescCompactAttr->verify_compact_attribute
means we also need to call populate_compact_attribute to populate
other attributes.

v14-0001-Introduce-CopyFormat-and-replace-csv_mode-and-bi.patch,
author is by Joel Jacobson.
As I mentioned in above,
replacing 3 bool fields by an enum didn't change the struct CopyFormatOptions.
but consolidated 3 bool fields into one enum to make code more lean.
I think the refactoring (v14-0001) is worth it.
From 38fe90d00746b7441faef4bdc392090aea1b9ee3 Mon Sep 17 00:00:00 2001
From: jian he <[email-masked]>
Date: Mon, 27 Jan 2025 16:05:37 +0800
Subject: [PATCH v14 3/3] Add option force_array for COPY JSON FORMAT

force_array option can only be used in COPY TO with JSON format.
it make the output json output behave like json array type.

discussion: https://postgr.es/m/CALvfUkBxTYy5uWPFVwpk_7ii2zgT07t3d-yR_cy4sfrrLU%3Dkcg%40mail.gmail.com
discussion: https://postgr.es/m/[email-masked]
---
 doc/src/sgml/ref/copy.sgml         | 14 ++++++++++++++
 src/backend/commands/copy.c        | 13 +++++++++++++
 src/backend/commands/copyto.c      | 28 ++++++++++++++++++++++++++++
 src/bin/psql/tab-complete.in.c     |  2 +-
 src/include/commands/copy.h        |  1 +
 src/test/regress/expected/copy.out | 23 +++++++++++++++++++++++
 src/test/regress/sql/copy.sql      |  9 +++++++++
 7 files changed, 89 insertions(+), 1 deletion(-)

diff --git a/doc/src/sgml/ref/copy.sgml b/doc/src/sgml/ref/copy.sgml
index 5bf0f38d90..50cebec0ce 100644
--- a/doc/src/sgml/ref/copy.sgml
+++ b/doc/src/sgml/ref/copy.sgml
@@ -43,6 +43,7 @@ COPY { <replaceable class="parameter">table_name</replaceable> [ ( <replaceable
     FORCE_QUOTE { ( <replaceable class="parameter">column_name</replaceable> [, ...] ) | * }
     FORCE_NOT_NULL { ( <replaceable class="parameter">column_name</replaceable> [, ...] ) | * }
     FORCE_NULL { ( <replaceable class="parameter">column_name</replaceable> [, ...] ) | * }
+    FORCE_ARRAY [ <replaceable class="parameter">boolean</replaceable> ]
     ON_ERROR <replaceable class="parameter">error_action</replaceable>
     REJECT_LIMIT <replaceable class="parameter">maxerror</replaceable>
     ENCODING '<replaceable class="parameter">encoding_name</replaceable>'
@@ -392,6 +393,19 @@ COPY { <replaceable class="parameter">table_name</replaceable> [ ( <replaceable
     </listitem>
    </varlistentry>
 
+   <varlistentry>
+    <term><literal>FORCE_ARRAY</literal></term>
+    <listitem>
+     <para>
+      Force output of square brackets as array decorations at the beginning
+      and end of output, and commas between the rows. It is allowed only in
+      <command>COPY TO</command>, and only when using
+      <literal>JSON</literal> format. The default is
+      <literal>false</literal>.
+     </para>
+    </listitem>
+   </varlistentry>
+
    <varlistentry>
     <term><literal>ON_ERROR</literal></term>
     <listitem>
diff --git a/src/backend/commands/copy.c b/src/backend/commands/copy.c
index c0234d22e5..2dbbd4007e 100644
--- a/src/backend/commands/copy.c
+++ b/src/backend/commands/copy.c
@@ -504,6 +504,7 @@ ProcessCopyOptions(ParseState *pstate,
 	bool		on_error_specified = false;
 	bool		log_verbosity_specified = false;
 	bool		reject_limit_specified = false;
+	bool		force_array_specified = false;
 	ListCell   *option;
 
 	/* Support external use for option sanity checking */
@@ -658,6 +659,13 @@ ProcessCopyOptions(ParseState *pstate,
 								defel->defname),
 						 parser_errposition(pstate, defel->location)));
 		}
+		else if (strcmp(defel->defname, "force_array") == 0)
+		{
+			if (force_array_specified)
+				errorConflictingDefElem(defel, pstate);
+			force_array_specified = true;
+			opts_out->force_array = defGetBoolean(defel);
+		}
 		else if (strcmp(defel->defname, "on_error") == 0)
 		{
 			if (on_error_specified)
@@ -907,6 +915,11 @@ ProcessCopyOptions(ParseState *pstate,
 				errcode(ERRCODE_INVALID_PARAMETER_VALUE),
 				errmsg("COPY json mode cannot be used with %s", "COPY FROM"));
 
+	if (opts_out->format != COPY_FORMAT_JSON && opts_out->force_array)
+		ereport(ERROR,
+				errcode(ERRCODE_INVALID_PARAMETER_VALUE),
+				errmsg("COPY %s can only used with JSON mode", "FORCE_ARRAY"));
+
 	if (opts_out->default_print)
 	{
 		if (!is_from)
diff --git a/src/backend/commands/copyto.c b/src/backend/commands/copyto.c
index d5d1bc4ec9..764ed4766d 100644
--- a/src/backend/commands/copyto.c
+++ b/src/backend/commands/copyto.c
@@ -81,6 +81,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? */
+	bool		json_row_delim_needed;	/* need delimiter to start next json array element */
 	copy_data_dest_cb data_dest_cb; /* function for writing data */
 
 	CopyFormatOptions opts;
@@ -858,6 +859,15 @@ DoCopyTo(CopyToState cstate)
 
 			CopySendEndOfRow(cstate);
 		}
+		/*
+		 * If JSON has been requested, and FORCE_ARRAY has been specified then
+		 * send the opening bracket.
+		*/
+		if (cstate->opts.format == COPY_FORMAT_JSON && cstate->opts.force_array)
+		{
+			CopySendChar(cstate, '[');
+			CopySendEndOfRow(cstate);
+		}
 	}
 
 	if (cstate->rel)
@@ -905,6 +915,15 @@ DoCopyTo(CopyToState cstate)
 		CopySendEndOfRow(cstate);
 	}
 
+	/*
+	 * If JSON has been requested, and FORCE_ARRAY has been specified then
+	 * send the closing bracket.
+	*/
+	if (cstate->opts.format == COPY_FORMAT_JSON && cstate->opts.force_array)
+	{
+		CopySendChar(cstate, ']');
+		CopySendEndOfRow(cstate);
+	}
 	MemoryContextDelete(cstate->rowcontext);
 
 	if (fe_copy)
@@ -1008,6 +1027,15 @@ CopyOneRowTo(CopyToState cstate, TupleTableSlot *slot)
 		result = makeStringInfo();
 		composite_to_json(rowdata, result, false);
 
+		if (cstate->json_row_delim_needed && cstate->opts.force_array)
+			CopySendChar(cstate, ',');
+		else if (cstate->opts.force_array)
+		{
+			/* first row needs no delimiter */
+			CopySendChar(cstate, ' ');
+			cstate->json_row_delim_needed = true;
+		}
+
 		CopySendData(cstate, result->data, result->len);
 	}
 
diff --git a/src/bin/psql/tab-complete.in.c b/src/bin/psql/tab-complete.in.c
index 5fb5cb2daa..02d0d4e3cf 100644
--- a/src/bin/psql/tab-complete.in.c
+++ b/src/bin/psql/tab-complete.in.c
@@ -3249,7 +3249,7 @@ match_previous_words(int pattern_id,
 	else if (Matches("COPY|\\copy", MatchAny, "FROM|TO", MatchAny, "WITH", "("))
 		COMPLETE_WITH("FORMAT", "FREEZE", "DELIMITER", "NULL",
 					  "HEADER", "QUOTE", "ESCAPE", "FORCE_QUOTE",
-					  "FORCE_NOT_NULL", "FORCE_NULL", "ENCODING", "DEFAULT",
+					  "FORCE_NOT_NULL", "FORCE_NULL", "FORCE_ARRAY", "ENCODING", "DEFAULT",
 					  "ON_ERROR", "LOG_VERBOSITY");
 
 	/* Complete COPY <sth> FROM|TO filename WITH (FORMAT */
diff --git a/src/include/commands/copy.h b/src/include/commands/copy.h
index 07fcc2bc9a..466ab76dac 100644
--- a/src/include/commands/copy.h
+++ b/src/include/commands/copy.h
@@ -92,6 +92,7 @@ typedef struct CopyFormatOptions
 	List	   *force_null;		/* list of column names */
 	bool		force_null_all; /* FORCE_NULL *? */
 	bool	   *force_null_flags;	/* per-column CSV FN flags */
+	bool		force_array;	/* add JSON array decorations */
 	bool		convert_selectively;	/* do selective binary conversion? */
 	CopyOnErrorChoice on_error; /* what to do when error happened */
 	CopyLogVerbosityChoice log_verbosity;	/* verbosity of logged messages */
diff --git a/src/test/regress/expected/copy.out b/src/test/regress/expected/copy.out
index b4479bc72a..ffee7bff00 100644
--- a/src/test/regress/expected/copy.out
+++ b/src/test/regress/expected/copy.out
@@ -110,6 +110,29 @@ LINE 1: copy copytest to stdout (format json, on_error ignore);
 copy copytest from stdin(format json);
 ERROR:  COPY json mode cannot be used with COPY FROM
 -- all of the above should yield error
+--Error
+copy copytest to stdout (format csv, force_array true);
+ERROR:  COPY FORCE_ARRAY can only used with JSON mode
+--ok
+copy copytest to stdout (format json, force_array);
+[
+ {"style":"DOS","test":"abc\r\ndef","filler":1}
+,{"style":"Unix","test":"abc\ndef","filler":2}
+,{"style":"Mac","test":"abc\rdef","filler":3}
+,{"style":"esc\\ape","test":"a\\r\\\r\\\n\\nb","filler":4}
+]
+copy copytest to stdout (format json, force_array true);
+[
+ {"style":"DOS","test":"abc\r\ndef","filler":1}
+,{"style":"Unix","test":"abc\ndef","filler":2}
+,{"style":"Mac","test":"abc\rdef","filler":3}
+,{"style":"esc\\ape","test":"a\\r\\\r\\\n\\nb","filler":4}
+]
+copy copytest to stdout (format json, force_array false);
+{"style":"DOS","test":"abc\r\ndef","filler":1}
+{"style":"Unix","test":"abc\ndef","filler":2}
+{"style":"Mac","test":"abc\rdef","filler":3}
+{"style":"esc\\ape","test":"a\\r\\\r\\\n\\nb","filler":4}
 -- embedded escaped characters
 create temp table copyjsontest (
     id bigserial,
diff --git a/src/test/regress/sql/copy.sql b/src/test/regress/sql/copy.sql
index 054fce95cb..7a175ca76a 100644
--- a/src/test/regress/sql/copy.sql
+++ b/src/test/regress/sql/copy.sql
@@ -101,6 +101,15 @@ copy copytest to stdout (format json, on_error ignore);
 copy copytest from stdin(format json);
 -- all of the above should yield error
 
+--Error
+copy copytest to stdout (format csv, force_array true);
+
+--ok
+copy copytest to stdout (format json, force_array);
+
+copy copytest to stdout (format json, force_array true);
+
+copy copytest to stdout (format json, force_array false);
 -- embedded escaped characters
 create temp table copyjsontest (
     id bigserial,
-- 
2.34.1

From e42e0696f6362fc8aba01ef1c546c4224aaf65c1 Mon Sep 17 00:00:00 2001
From: jian he <[email-masked]>
Date: Mon, 27 Jan 2025 15:05:48 +0800
Subject: [PATCH v14 2/3] introduce json format for COPY TO

json format is only allowed in COPY TO operation.
also cannot be used with {header, default, null, delimiter} options and many other options.
fully tested on src/test/regress/sql/copy.sql.

discussion: https://postgr.es/m/CALvfUkBxTYy5uWPFVwpk_7ii2zgT07t3d-yR_cy4sfrrLU%3Dkcg%40mail.gmail.com
discussion: https://postgr.es/m/[email-masked]
---
 doc/src/sgml/ref/copy.sgml         | 13 ++++--
 src/backend/commands/copy.c        | 29 ++++++++++++
 src/backend/commands/copyto.c      | 51 ++++++++++++++++++--
 src/backend/parser/gram.y          |  8 ++++
 src/backend/utils/adt/json.c       |  5 +-
 src/bin/psql/tab-complete.in.c     |  2 +-
 src/include/commands/copy.h        |  1 +
 src/include/utils/json.h           |  2 +
 src/test/regress/expected/copy.out | 74 ++++++++++++++++++++++++++++++
 src/test/regress/sql/copy.sql      | 47 +++++++++++++++++++
 10 files changed, 219 insertions(+), 13 deletions(-)

diff --git a/doc/src/sgml/ref/copy.sgml b/doc/src/sgml/ref/copy.sgml
index 8394402f09..5bf0f38d90 100644
--- a/doc/src/sgml/ref/copy.sgml
+++ b/doc/src/sgml/ref/copy.sgml
@@ -219,10 +219,15 @@ COPY { <replaceable class="parameter">table_name</replaceable> [ ( <replaceable
       Selects the data format to be read or written:
       <literal>text</literal>,
       <literal>csv</literal> (Comma Separated Values),
+      <literal>json</literal> (JavaScript Object Notation),
       or <literal>binary</literal>.
       The default is <literal>text</literal>.
       See <xref linkend="sql-copy-file-formats"/> below for details.
      </para>
+     <para>
+      The <literal>json</literal> option is allowed only in
+      <command>COPY TO</command>.
+     </para>
     </listitem>
    </varlistentry>
 
@@ -257,7 +262,7 @@ COPY { <replaceable class="parameter">table_name</replaceable> [ ( <replaceable
       (line) of the file.  The default is a tab character in text format,
       a comma in <literal>CSV</literal> format.
       This must be a single one-byte character.
-      This option is not allowed when using <literal>binary</literal> format.
+      This option is not allowed when using <literal>binary</literal> or <literal>json</literal> format.
      </para>
     </listitem>
    </varlistentry>
@@ -271,7 +276,7 @@ COPY { <replaceable class="parameter">table_name</replaceable> [ ( <replaceable
       string in <literal>CSV</literal> format. You might prefer an
       empty string even in text format for cases where you don't want to
       distinguish nulls from empty strings.
-      This option is not allowed when using <literal>binary</literal> format.
+      This option is not allowed when using <literal>binary</literal> or <literal>json</literal> format.
      </para>
 
      <note>
@@ -294,7 +299,7 @@ COPY { <replaceable class="parameter">table_name</replaceable> [ ( <replaceable
       is found in the input file, the default value of the corresponding column
       will be used.
       This option is allowed only in <command>COPY FROM</command>, and only when
-      not using <literal>binary</literal> format.
+      not using <literal>binary</literal> or <literal>json</literal> format.
      </para>
     </listitem>
    </varlistentry>
@@ -310,7 +315,7 @@ COPY { <replaceable class="parameter">table_name</replaceable> [ ( <replaceable
       If this option is set to <literal>MATCH</literal>, the number and names
       of the columns in the header line must match the actual column names of
       the table, in order;  otherwise an error is raised.
-      This option is not allowed when using <literal>binary</literal> format.
+      This option is not allowed when using <literal>binary</literal> or <literal>json</literal> format.
       The <literal>MATCH</literal> option is only valid for <command>COPY
       FROM</command> commands.
      </para>
diff --git a/src/backend/commands/copy.c b/src/backend/commands/copy.c
index e7e7815853..c0234d22e5 100644
--- a/src/backend/commands/copy.c
+++ b/src/backend/commands/copy.c
@@ -530,6 +530,8 @@ ProcessCopyOptions(ParseState *pstate,
 				opts_out->format = COPY_FORMAT_CSV;
 			else if (strcmp(fmt, "binary") == 0)
 				opts_out->format = COPY_FORMAT_BINARY;
+			else if (strcmp(fmt, "json") == 0)
+				opts_out->format = COPY_FORMAT_JSON;
 			else
 				ereport(ERROR,
 						(errcode(ERRCODE_INVALID_PARAMETER_VALUE),
@@ -695,16 +697,32 @@ ProcessCopyOptions(ParseState *pstate,
 		/*- translator: %s is the name of a COPY option, e.g. ON_ERROR */
 				 errmsg("cannot specify %s in BINARY mode", "DELIMITER")));
 
+	if (opts_out->format == COPY_FORMAT_JSON && opts_out->delim)
+		ereport(ERROR,
+				errcode(ERRCODE_SYNTAX_ERROR),
+		/*- translator: %s is the name of a COPY option, e.g. ON_ERROR */
+				errmsg("cannot specify %s in JSON mode", "DELIMITER"));
+
 	if (opts_out->format == COPY_FORMAT_BINARY && opts_out->null_print)
 		ereport(ERROR,
 				(errcode(ERRCODE_SYNTAX_ERROR),
 				 errmsg("cannot specify %s in BINARY mode", "NULL")));
 
+	if (opts_out->format == COPY_FORMAT_JSON && opts_out->null_print)
+		ereport(ERROR,
+				errcode(ERRCODE_SYNTAX_ERROR),
+				errmsg("cannot specify %s in JSON mode", "NULL"));
+
 	if (opts_out->format == COPY_FORMAT_BINARY && opts_out->default_print)
 		ereport(ERROR,
 				(errcode(ERRCODE_SYNTAX_ERROR),
 				 errmsg("cannot specify %s in BINARY mode", "DEFAULT")));
 
+	if (opts_out->format == COPY_FORMAT_JSON && opts_out->default_print)
+		ereport(ERROR,
+				errcode(ERRCODE_SYNTAX_ERROR),
+				errmsg("cannot specify %s in JSON mode", "DEFAULT"));
+
 	/* Set defaults for omitted options */
 	if (!opts_out->delim)
 		opts_out->delim = opts_out->format == COPY_FORMAT_CSV ? "," : "\t";
@@ -775,6 +793,11 @@ ProcessCopyOptions(ParseState *pstate,
 		/*- translator: %s is the name of a COPY option, e.g. ON_ERROR */
 				 errmsg("cannot specify %s in BINARY mode", "HEADER")));
 
+	if (opts_out->format == COPY_FORMAT_JSON && opts_out->header_line)
+		ereport(ERROR,
+				errcode(ERRCODE_INVALID_PARAMETER_VALUE),
+				errmsg("cannot specify %s in JSON mode", "HEADER"));
+
 	/* Check quote */
 	if (opts_out->format != COPY_FORMAT_CSV && opts_out->quote != NULL)
 		ereport(ERROR,
@@ -878,6 +901,12 @@ ProcessCopyOptions(ParseState *pstate,
 				 errmsg("COPY %s cannot be used with %s", "FREEZE",
 						"COPY TO")));
 
+	/* Check json format  */
+	if (opts_out->format == COPY_FORMAT_JSON && is_from)
+		ereport(ERROR,
+				errcode(ERRCODE_INVALID_PARAMETER_VALUE),
+				errmsg("COPY json mode cannot be used with %s", "COPY FROM"));
+
 	if (opts_out->default_print)
 	{
 		if (!is_from)
diff --git a/src/backend/commands/copyto.c b/src/backend/commands/copyto.c
index ea90af28a9..d5d1bc4ec9 100644
--- a/src/backend/commands/copyto.c
+++ b/src/backend/commands/copyto.c
@@ -24,6 +24,7 @@
 #include "executor/execdesc.h"
 #include "executor/executor.h"
 #include "executor/tuptable.h"
+#include "funcapi.h"
 #include "libpq/libpq.h"
 #include "libpq/pqformat.h"
 #include "mb/pg_wchar.h"
@@ -31,6 +32,7 @@
 #include "pgstat.h"
 #include "storage/fd.h"
 #include "tcop/tcopprot.h"
+#include "utils/json.h"
 #include "utils/lsyscache.h"
 #include "utils/memutils.h"
 #include "utils/rel.h"
@@ -139,9 +141,20 @@ SendCopyBegin(CopyToState cstate)
 
 	pq_beginmessage(&buf, PqMsg_CopyOutResponse);
 	pq_sendbyte(&buf, format);	/* overall format */
-	pq_sendint16(&buf, natts);
-	for (i = 0; i < natts; i++)
-		pq_sendint16(&buf, format); /* per-column formats */
+	if (cstate->opts.format != COPY_FORMAT_JSON)
+	{
+		pq_sendint16(&buf, natts);
+		for (i = 0; i < natts; i++)
+			pq_sendint16(&buf, format); /* per-column formats */
+	}
+	else
+	{
+		/*
+		 * JSON format is always one non-binary column
+		 */
+		pq_sendint16(&buf, 1);
+		pq_sendint16(&buf, 0);
+	}
 	pq_endmessage(&buf);
 	cstate->copy_dest = COPY_FRONTEND;
 }
@@ -921,7 +934,7 @@ CopyOneRowTo(CopyToState cstate, TupleTableSlot *slot)
 	/* Make sure the tuple is fully deconstructed */
 	slot_getallattrs(slot);
 
-	if (cstate->opts.format != COPY_FORMAT_BINARY)
+	if (cstate->opts.format == COPY_FORMAT_TEXT || cstate->opts.format == COPY_FORMAT_CSV)
 	{
 		bool		need_delim = false;
 
@@ -949,7 +962,7 @@ CopyOneRowTo(CopyToState cstate, TupleTableSlot *slot)
 			}
 		}
 	}
-	else
+	else if (cstate->opts.format == COPY_FORMAT_BINARY)
 	{
 		foreach_int(attnum, cstate->attnumlist)
 		{
@@ -969,6 +982,34 @@ CopyOneRowTo(CopyToState cstate, TupleTableSlot *slot)
 			}
 		}
 	}
+	else
+	{
+		Datum		rowdata;
+		StringInfo	result;
+
+		/*
+		 * if COPY TO source data is from a query, not a plain table, then we
+		 * need copy CopyToState->TupleDesc to slot->tts_tupleDescriptor.
+		 * because the slot's TupleDesc->attrs may change during query
+		 * execution.
+		*/
+		if(!cstate->rel)
+		{
+			memcpy(TupleDescAttr(slot->tts_tupleDescriptor, 0),
+				   TupleDescAttr(cstate->queryDesc->tupDesc, 0),
+				   cstate->queryDesc->tupDesc->natts * sizeof(FormData_pg_attribute));
+
+			for (int i = 0; i < cstate->queryDesc->tupDesc->natts; i++)
+				populate_compact_attribute(slot->tts_tupleDescriptor, i);
+
+			BlessTupleDesc(slot->tts_tupleDescriptor);
+		}
+		rowdata = ExecFetchSlotHeapTupleDatum(slot);
+		result = makeStringInfo();
+		composite_to_json(rowdata, result, false);
+
+		CopySendData(cstate, result->data, result->len);
+	}
 
 	CopySendEndOfRow(cstate);
 
diff --git a/src/backend/parser/gram.y b/src/backend/parser/gram.y
index d7f9c00c40..cfcb200b6e 100644
--- a/src/backend/parser/gram.y
+++ b/src/backend/parser/gram.y
@@ -3465,6 +3465,10 @@ copy_opt_item:
 				{
 					$$ = makeDefElem("format", (Node *) makeString("csv"), @1);
 				}
+			| JSON
+				{
+					$$ = makeDefElem("format", (Node *) makeString("json"), @1);
+				}
 			| HEADER_P
 				{
 					$$ = makeDefElem("header", (Node *) makeBoolean(true), @1);
@@ -3547,6 +3551,10 @@ copy_generic_opt_elem:
 				{
 					$$ = makeDefElem($1, $2, @1);
 				}
+			| FORMAT_LA copy_generic_opt_arg
+			{
+				$$ = makeDefElem("format", $2, @1);
+			}
 		;
 
 copy_generic_opt_arg:
diff --git a/src/backend/utils/adt/json.c b/src/backend/utils/adt/json.c
index 51452755f5..bf69347fa9 100644
--- a/src/backend/utils/adt/json.c
+++ b/src/backend/utils/adt/json.c
@@ -85,8 +85,6 @@ typedef struct JsonAggState
 	JsonUniqueBuilderState unique_check;
 } JsonAggState;
 
-static void composite_to_json(Datum composite, StringInfo result,
-							  bool use_line_feeds);
 static void array_dim_to_json(StringInfo result, int dim, int ndims, int *dims,
 							  Datum *vals, bool *nulls, int *valcount,
 							  JsonTypeCategory tcategory, Oid outfuncoid,
@@ -516,8 +514,9 @@ array_to_json_internal(Datum array, StringInfo result, bool use_line_feeds)
 
 /*
  * Turn a composite / record into JSON.
+ * Exported so COPY TO can use it.
  */
-static void
+void
 composite_to_json(Datum composite, StringInfo result, bool use_line_feeds)
 {
 	HeapTupleHeader td;
diff --git a/src/bin/psql/tab-complete.in.c b/src/bin/psql/tab-complete.in.c
index 81cbf10aa2..5fb5cb2daa 100644
--- a/src/bin/psql/tab-complete.in.c
+++ b/src/bin/psql/tab-complete.in.c
@@ -3254,7 +3254,7 @@ match_previous_words(int pattern_id,
 
 	/* Complete COPY <sth> FROM|TO filename WITH (FORMAT */
 	else if (Matches("COPY|\\copy", MatchAny, "FROM|TO", MatchAny, "WITH", "(", "FORMAT"))
-		COMPLETE_WITH("binary", "csv", "text");
+		COMPLETE_WITH("binary", "csv", "text", "json");
 
 	/* Complete COPY <sth> FROM filename WITH (ON_ERROR */
 	else if (Matches("COPY|\\copy", MatchAny, "FROM|TO", MatchAny, "WITH", "(", "ON_ERROR"))
diff --git a/src/include/commands/copy.h b/src/include/commands/copy.h
index 7a1ee65601..07fcc2bc9a 100644
--- a/src/include/commands/copy.h
+++ b/src/include/commands/copy.h
@@ -59,6 +59,7 @@ typedef enum CopyFormat
 	COPY_FORMAT_TEXT = 0,
 	COPY_FORMAT_BINARY,
 	COPY_FORMAT_CSV,
+	COPY_FORMAT_JSON,
 } CopyFormat;
 
 /*
diff --git a/src/include/utils/json.h b/src/include/utils/json.h
index 49bbda7ac0..1fa8e2ce8e 100644
--- a/src/include/utils/json.h
+++ b/src/include/utils/json.h
@@ -17,6 +17,8 @@
 #include "lib/stringinfo.h"
 
 /* functions in json.c */
+extern void composite_to_json(Datum composite, StringInfo result,
+							  bool use_line_feeds);
 extern void escape_json(StringInfo buf, const char *str);
 extern void escape_json_with_len(StringInfo buf, const char *str, int len);
 extern void escape_json_text(StringInfo buf, const text *txt);
diff --git a/src/test/regress/expected/copy.out b/src/test/regress/expected/copy.out
index f554d42c84..b4479bc72a 100644
--- a/src/test/regress/expected/copy.out
+++ b/src/test/regress/expected/copy.out
@@ -73,6 +73,80 @@ copy copytest3 to stdout csv header;
 c1,"col with , comma","col with "" quote"
 1,a,1
 2,b,2
+--- test copying in JSON mode with various styles
+copy copytest to stdout json;
+{"style":"DOS","test":"abc\r\ndef","filler":1}
+{"style":"Unix","test":"abc\ndef","filler":2}
+{"style":"Mac","test":"abc\rdef","filler":3}
+{"style":"esc\\ape","test":"a\\r\\\r\\\n\\nb","filler":4}
+copy copytest to stdout (format json);
+{"style":"DOS","test":"abc\r\ndef","filler":1}
+{"style":"Unix","test":"abc\ndef","filler":2}
+{"style":"Mac","test":"abc\rdef","filler":3}
+{"style":"esc\\ape","test":"a\\r\\\r\\\n\\nb","filler":4}
+-- all of the following should yield error
+copy copytest to stdout (format json, delimiter '|');
+ERROR:  cannot specify DELIMITER in JSON mode
+copy copytest to stdout (format json, null '\N');
+ERROR:  cannot specify NULL in JSON mode
+copy copytest to stdout (format json, default '|');
+ERROR:  cannot specify DEFAULT in JSON mode
+copy copytest to stdout (format json, header);
+ERROR:  cannot specify HEADER in JSON mode
+copy copytest to stdout (format json, quote '"');
+ERROR:  COPY QUOTE requires CSV mode
+copy copytest to stdout (format json, escape '"');
+ERROR:  COPY ESCAPE requires CSV mode
+copy copytest to stdout (format json, force_quote *);
+ERROR:  COPY FORCE_QUOTE requires CSV mode
+copy copytest to stdout (format json, force_not_null *);
+ERROR:  COPY FORCE_NOT_NULL requires CSV mode
+copy copytest to stdout (format json, force_null *);
+ERROR:  COPY FORCE_NULL requires CSV mode
+copy copytest to stdout (format json, on_error ignore);
+ERROR:  COPY ON_ERROR cannot be used with COPY TO
+LINE 1: copy copytest to stdout (format json, on_error ignore);
+                                              ^
+copy copytest from stdin(format json);
+ERROR:  COPY json mode cannot be used with COPY FROM
+-- all of the above should yield error
+-- embedded escaped characters
+create temp table copyjsontest (
+    id bigserial,
+    f1 text,
+    f2 timestamptz);
+insert into copyjsontest
+  select g.i,
+         CASE WHEN g.i % 2 = 0 THEN
+           'line with '' in it: ' || g.i::text
+         ELSE
+           'line with " in it: ' || g.i::text
+         END,
+         'Mon Feb 10 17:32:01 1997 PST'
+  from generate_series(1,5) as g(i);
+insert into copyjsontest (f1) values
+(E'aaa\"bbb'::text),
+(E'aaa\\bbb'::text),
+(E'aaa\/bbb'::text),
+(E'aaa\bbbb'::text),
+(E'aaa\fbbb'::text),
+(E'aaa\nbbb'::text),
+(E'aaa\rbbb'::text),
+(E'aaa\tbbb'::text);
+copy copyjsontest to stdout json;
+{"id":1,"f1":"line with \" in it: 1","f2":"1997-02-10T17:32:01-08:00"}
+{"id":2,"f1":"line with ' in it: 2","f2":"1997-02-10T17:32:01-08:00"}
+{"id":3,"f1":"line with \" in it: 3","f2":"1997-02-10T17:32:01-08:00"}
+{"id":4,"f1":"line with ' in it: 4","f2":"1997-02-10T17:32:01-08:00"}
+{"id":5,"f1":"line with \" in it: 5","f2":"1997-02-10T17:32:01-08:00"}
+{"id":1,"f1":"aaa\"bbb","f2":null}
+{"id":2,"f1":"aaa\\bbb","f2":null}
+{"id":3,"f1":"aaa/bbb","f2":null}
+{"id":4,"f1":"aaa\bbbb","f2":null}
+{"id":5,"f1":"aaa\fbbb","f2":null}
+{"id":6,"f1":"aaa\nbbb","f2":null}
+{"id":7,"f1":"aaa\rbbb","f2":null}
+{"id":8,"f1":"aaa\tbbb","f2":null}
 create temp table copytest4 (
 	c1 int,
 	"colname with tab: 	" text);
diff --git a/src/test/regress/sql/copy.sql b/src/test/regress/sql/copy.sql
index f1699b66b0..054fce95cb 100644
--- a/src/test/regress/sql/copy.sql
+++ b/src/test/regress/sql/copy.sql
@@ -82,6 +82,53 @@ this is just a line full of junk that would error out if parsed
 
 copy copytest3 to stdout csv header;
 
+--- test copying in JSON mode with various styles
+copy copytest to stdout json;
+
+copy copytest to stdout (format json);
+
+-- all of the following should yield error
+copy copytest to stdout (format json, delimiter '|');
+copy copytest to stdout (format json, null '\N');
+copy copytest to stdout (format json, default '|');
+copy copytest to stdout (format json, header);
+copy copytest to stdout (format json, quote '"');
+copy copytest to stdout (format json, escape '"');
+copy copytest to stdout (format json, force_quote *);
+copy copytest to stdout (format json, force_not_null *);
+copy copytest to stdout (format json, force_null *);
+copy copytest to stdout (format json, on_error ignore);
+copy copytest from stdin(format json);
+-- all of the above should yield error
+
+-- embedded escaped characters
+create temp table copyjsontest (
+    id bigserial,
+    f1 text,
+    f2 timestamptz);
+
+insert into copyjsontest
+  select g.i,
+         CASE WHEN g.i % 2 = 0 THEN
+           'line with '' in it: ' || g.i::text
+         ELSE
+           'line with " in it: ' || g.i::text
+         END,
+         'Mon Feb 10 17:32:01 1997 PST'
+  from generate_series(1,5) as g(i);
+
+insert into copyjsontest (f1) values
+(E'aaa\"bbb'::text),
+(E'aaa\\bbb'::text),
+(E'aaa\/bbb'::text),
+(E'aaa\bbbb'::text),
+(E'aaa\fbbb'::text),
+(E'aaa\nbbb'::text),
+(E'aaa\rbbb'::text),
+(E'aaa\tbbb'::text);
+
+copy copyjsontest to stdout json;
+
 create temp table copytest4 (
 	c1 int,
 	"colname with tab: 	" text);
-- 
2.34.1

From 7f475554a544b1e031522e21477731286deb9917 Mon Sep 17 00:00:00 2001
From: Joel Jacobson <[email-masked]>
Date: Thu, 24 Oct 2024 08:24:13 +0300
Subject: [PATCH v14 1/3] Introduce CopyFormat and replace csv_mode and binary
 fields with it.

---
 src/backend/commands/copy.c          | 50 +++++++++++++++-------------
 src/backend/commands/copyfrom.c      | 10 +++---
 src/backend/commands/copyfromparse.c | 34 +++++++++----------
 src/backend/commands/copyto.c        | 20 +++++------
 src/include/commands/copy.h          | 13 ++++++--
 src/tools/pgindent/typedefs.list     |  1 +
 6 files changed, 70 insertions(+), 58 deletions(-)

diff --git a/src/backend/commands/copy.c b/src/backend/commands/copy.c
index cfca9d9dc2..e7e7815853 100644
--- a/src/backend/commands/copy.c
+++ b/src/backend/commands/copy.c
@@ -525,11 +525,11 @@ ProcessCopyOptions(ParseState *pstate,
 				errorConflictingDefElem(defel, pstate);
 			format_specified = true;
 			if (strcmp(fmt, "text") == 0)
-				 /* default format */ ;
+				opts_out->format = COPY_FORMAT_TEXT;
 			else if (strcmp(fmt, "csv") == 0)
-				opts_out->csv_mode = true;
+				opts_out->format = COPY_FORMAT_CSV;
 			else if (strcmp(fmt, "binary") == 0)
-				opts_out->binary = true;
+				opts_out->format = COPY_FORMAT_BINARY;
 			else
 				ereport(ERROR,
 						(errcode(ERRCODE_INVALID_PARAMETER_VALUE),
@@ -689,31 +689,31 @@ ProcessCopyOptions(ParseState *pstate,
 	 * Check for incompatible options (must do these three before inserting
 	 * defaults)
 	 */
-	if (opts_out->binary && opts_out->delim)
+	if (opts_out->format == COPY_FORMAT_BINARY && opts_out->delim)
 		ereport(ERROR,
 				(errcode(ERRCODE_SYNTAX_ERROR),
 		/*- translator: %s is the name of a COPY option, e.g. ON_ERROR */
 				 errmsg("cannot specify %s in BINARY mode", "DELIMITER")));
 
-	if (opts_out->binary && opts_out->null_print)
+	if (opts_out->format == COPY_FORMAT_BINARY && opts_out->null_print)
 		ereport(ERROR,
 				(errcode(ERRCODE_SYNTAX_ERROR),
 				 errmsg("cannot specify %s in BINARY mode", "NULL")));
 
-	if (opts_out->binary && opts_out->default_print)
+	if (opts_out->format == COPY_FORMAT_BINARY && opts_out->default_print)
 		ereport(ERROR,
 				(errcode(ERRCODE_SYNTAX_ERROR),
 				 errmsg("cannot specify %s in BINARY mode", "DEFAULT")));
 
 	/* Set defaults for omitted options */
 	if (!opts_out->delim)
-		opts_out->delim = opts_out->csv_mode ? "," : "\t";
+		opts_out->delim = opts_out->format == COPY_FORMAT_CSV ? "," : "\t";
 
 	if (!opts_out->null_print)
-		opts_out->null_print = opts_out->csv_mode ? "" : "\\N";
+		opts_out->null_print = opts_out->format == COPY_FORMAT_CSV ? "" : "\\N";
 	opts_out->null_print_len = strlen(opts_out->null_print);
 
-	if (opts_out->csv_mode)
+	if (opts_out->format == COPY_FORMAT_CSV)
 	{
 		if (!opts_out->quote)
 			opts_out->quote = "\"";
@@ -761,7 +761,7 @@ ProcessCopyOptions(ParseState *pstate,
 	 * future-proofing.  Likewise we disallow all digits though only octal
 	 * digits are actually dangerous.
 	 */
-	if (!opts_out->csv_mode &&
+	if (opts_out->format != COPY_FORMAT_CSV &&
 		strchr("\\.abcdefghijklmnopqrstuvwxyz0123456789",
 			   opts_out->delim[0]) != NULL)
 		ereport(ERROR,
@@ -769,43 +769,44 @@ ProcessCopyOptions(ParseState *pstate,
 				 errmsg("COPY delimiter cannot be \"%s\"", opts_out->delim)));
 
 	/* Check header */
-	if (opts_out->binary && opts_out->header_line)
+	if (opts_out->format == COPY_FORMAT_BINARY && opts_out->header_line)
 		ereport(ERROR,
 				(errcode(ERRCODE_FEATURE_NOT_SUPPORTED),
 		/*- translator: %s is the name of a COPY option, e.g. ON_ERROR */
 				 errmsg("cannot specify %s in BINARY mode", "HEADER")));
 
 	/* Check quote */
-	if (!opts_out->csv_mode && opts_out->quote != NULL)
+	if (opts_out->format != COPY_FORMAT_CSV && opts_out->quote != NULL)
 		ereport(ERROR,
 				(errcode(ERRCODE_FEATURE_NOT_SUPPORTED),
 		/*- translator: %s is the name of a COPY option, e.g. ON_ERROR */
 				 errmsg("COPY %s requires CSV mode", "QUOTE")));
 
-	if (opts_out->csv_mode && strlen(opts_out->quote) != 1)
+	if (opts_out->format == COPY_FORMAT_CSV && strlen(opts_out->quote) != 1)
 		ereport(ERROR,
 				(errcode(ERRCODE_FEATURE_NOT_SUPPORTED),
 				 errmsg("COPY quote must be a single one-byte character")));
 
-	if (opts_out->csv_mode && opts_out->delim[0] == opts_out->quote[0])
+	if (opts_out->format == COPY_FORMAT_CSV && opts_out->delim[0] == opts_out->quote[0])
 		ereport(ERROR,
 				(errcode(ERRCODE_INVALID_PARAMETER_VALUE),
 				 errmsg("COPY delimiter and quote must be different")));
 
 	/* Check escape */
-	if (!opts_out->csv_mode && opts_out->escape != NULL)
+	if (opts_out->format != COPY_FORMAT_CSV && opts_out->escape != NULL)
 		ereport(ERROR,
 				(errcode(ERRCODE_FEATURE_NOT_SUPPORTED),
 		/*- translator: %s is the name of a COPY option, e.g. ON_ERROR */
 				 errmsg("COPY %s requires CSV mode", "ESCAPE")));
 
-	if (opts_out->csv_mode && strlen(opts_out->escape) != 1)
+	if (opts_out->format == COPY_FORMAT_CSV && strlen(opts_out->escape) != 1)
 		ereport(ERROR,
 				(errcode(ERRCODE_FEATURE_NOT_SUPPORTED),
 				 errmsg("COPY escape must be a single one-byte character")));
 
 	/* Check force_quote */
-	if (!opts_out->csv_mode && (opts_out->force_quote || opts_out->force_quote_all))
+	if (opts_out->format != COPY_FORMAT_CSV && (opts_out->force_quote ||
+												opts_out->force_quote_all))
 		ereport(ERROR,
 				(errcode(ERRCODE_FEATURE_NOT_SUPPORTED),
 		/*- translator: %s is the name of a COPY option, e.g. ON_ERROR */
@@ -819,8 +820,8 @@ ProcessCopyOptions(ParseState *pstate,
 						"COPY FROM")));
 
 	/* Check force_notnull */
-	if (!opts_out->csv_mode && (opts_out->force_notnull != NIL ||
-								opts_out->force_notnull_all))
+	if (opts_out->format != COPY_FORMAT_CSV &&
+		(opts_out->force_notnull != NIL || opts_out->force_notnull_all))
 		ereport(ERROR,
 				(errcode(ERRCODE_FEATURE_NOT_SUPPORTED),
 		/*- translator: %s is the name of a COPY option, e.g. ON_ERROR */
@@ -835,8 +836,8 @@ ProcessCopyOptions(ParseState *pstate,
 						"COPY TO")));
 
 	/* Check force_null */
-	if (!opts_out->csv_mode && (opts_out->force_null != NIL ||
-								opts_out->force_null_all))
+	if (opts_out->format != COPY_FORMAT_CSV && (opts_out->force_null != NIL ||
+												opts_out->force_null_all))
 		ereport(ERROR,
 				(errcode(ERRCODE_FEATURE_NOT_SUPPORTED),
 		/*- translator: %s is the name of a COPY option, e.g. ON_ERROR */
@@ -860,7 +861,7 @@ ProcessCopyOptions(ParseState *pstate,
 						"NULL")));
 
 	/* Don't allow the CSV quote char to appear in the null string. */
-	if (opts_out->csv_mode &&
+	if (opts_out->format == COPY_FORMAT_CSV &&
 		strchr(opts_out->null_print, opts_out->quote[0]) != NULL)
 		ereport(ERROR,
 				(errcode(ERRCODE_INVALID_PARAMETER_VALUE),
@@ -896,7 +897,7 @@ ProcessCopyOptions(ParseState *pstate,
 							"DEFAULT")));
 
 		/* Don't allow the CSV quote char to appear in the default string. */
-		if (opts_out->csv_mode &&
+		if (opts_out->format == COPY_FORMAT_CSV &&
 			strchr(opts_out->default_print, opts_out->quote[0]) != NULL)
 			ereport(ERROR,
 					(errcode(ERRCODE_FEATURE_NOT_SUPPORTED),
@@ -913,7 +914,8 @@ ProcessCopyOptions(ParseState *pstate,
 					 errmsg("NULL specification and DEFAULT specification cannot be the same")));
 	}
 	/* Check on_error */
-	if (opts_out->binary && opts_out->on_error != COPY_ON_ERROR_STOP)
+	if (opts_out->format == COPY_FORMAT_BINARY &&
+		opts_out->on_error != COPY_ON_ERROR_STOP)
 		ereport(ERROR,
 				(errcode(ERRCODE_SYNTAX_ERROR),
 				 errmsg("only ON_ERROR STOP is allowed in BINARY mode")));
diff --git a/src/backend/commands/copyfrom.c b/src/backend/commands/copyfrom.c
index 0cbd05f560..aea807d13c 100644
--- a/src/backend/commands/copyfrom.c
+++ b/src/backend/commands/copyfrom.c
@@ -122,7 +122,7 @@ CopyFromErrorCallback(void *arg)
 				   cstate->cur_relname);
 		return;
 	}
-	if (cstate->opts.binary)
+	if (cstate->opts.format == COPY_FORMAT_BINARY)
 	{
 		/* can't usefully display the data */
 		if (cstate->cur_attname)
@@ -1583,7 +1583,7 @@ BeginCopyFrom(ParseState *pstate,
 	cstate->raw_buf_index = cstate->raw_buf_len = 0;
 	cstate->raw_reached_eof = false;
 
-	if (!cstate->opts.binary)
+	if (cstate->opts.format != COPY_FORMAT_BINARY)
 	{
 		/*
 		 * If encoding conversion is needed, we need another buffer to hold
@@ -1634,7 +1634,7 @@ BeginCopyFrom(ParseState *pstate,
 			continue;
 
 		/* Fetch the input function and typioparam info */
-		if (cstate->opts.binary)
+		if (cstate->opts.format == COPY_FORMAT_BINARY)
 			getTypeBinaryInputInfo(att->atttypid,
 								   &in_func_oid, &typioparams[attnum - 1]);
 		else
@@ -1775,14 +1775,14 @@ BeginCopyFrom(ParseState *pstate,
 
 	pgstat_progress_update_multi_param(3, progress_cols, progress_vals);
 
-	if (cstate->opts.binary)
+	if (cstate->opts.format == COPY_FORMAT_BINARY)
 	{
 		/* Read and verify binary header */
 		ReceiveCopyBinaryHeader(cstate);
 	}
 
 	/* create workspace for CopyReadAttributes results */
-	if (!cstate->opts.binary)
+	if (cstate->opts.format != COPY_FORMAT_BINARY)
 	{
 		AttrNumber	attr_count = list_length(cstate->attnumlist);
 
diff --git a/src/backend/commands/copyfromparse.c b/src/backend/commands/copyfromparse.c
index caccdc8563..5b56aa89e3 100644
--- a/src/backend/commands/copyfromparse.c
+++ b/src/backend/commands/copyfromparse.c
@@ -162,7 +162,7 @@ ReceiveCopyBegin(CopyFromState cstate)
 {
 	StringInfoData buf;
 	int			natts = list_length(cstate->attnumlist);
-	int16		format = (cstate->opts.binary ? 1 : 0);
+	int16		format = (cstate->opts.format == COPY_FORMAT_BINARY ? 1 : 0);
 	int			i;
 
 	pq_beginmessage(&buf, PqMsg_CopyInResponse);
@@ -748,7 +748,7 @@ NextCopyFromRawFields(CopyFromState cstate, char ***fields, int *nfields)
 	bool		done;
 
 	/* only available for text or csv input */
-	Assert(!cstate->opts.binary);
+	Assert(cstate->opts.format != COPY_FORMAT_BINARY);
 
 	/* on input check that the header line is correct if needed */
 	if (cstate->cur_lineno == 0 && cstate->opts.header_line)
@@ -765,7 +765,7 @@ NextCopyFromRawFields(CopyFromState cstate, char ***fields, int *nfields)
 		{
 			int			fldnum;
 
-			if (cstate->opts.csv_mode)
+			if (cstate->opts.format == COPY_FORMAT_CSV)
 				fldct = CopyReadAttributesCSV(cstate);
 			else
 				fldct = CopyReadAttributesText(cstate);
@@ -820,7 +820,7 @@ NextCopyFromRawFields(CopyFromState cstate, char ***fields, int *nfields)
 		return false;
 
 	/* Parse the line into de-escaped field values */
-	if (cstate->opts.csv_mode)
+	if (cstate->opts.format == COPY_FORMAT_CSV)
 		fldct = CopyReadAttributesCSV(cstate);
 	else
 		fldct = CopyReadAttributesText(cstate);
@@ -864,7 +864,7 @@ NextCopyFrom(CopyFromState cstate, ExprContext *econtext,
 	MemSet(nulls, true, num_phys_attrs * sizeof(bool));
 	MemSet(cstate->defaults, false, num_phys_attrs * sizeof(bool));
 
-	if (!cstate->opts.binary)
+	if (cstate->opts.format != COPY_FORMAT_BINARY)
 	{
 		char	  **field_strings;
 		ListCell   *cur;
@@ -905,7 +905,7 @@ NextCopyFrom(CopyFromState cstate, ExprContext *econtext,
 				continue;
 			}
 
-			if (cstate->opts.csv_mode)
+			if (cstate->opts.format == COPY_FORMAT_CSV)
 			{
 				if (string == NULL &&
 					cstate->opts.force_notnull_flags[m])
@@ -1178,7 +1178,7 @@ CopyReadLineText(CopyFromState cstate)
 	char		quotec = '\0';
 	char		escapec = '\0';
 
-	if (cstate->opts.csv_mode)
+	if (cstate->opts.format == COPY_FORMAT_CSV)
 	{
 		quotec = cstate->opts.quote[0];
 		escapec = cstate->opts.escape[0];
@@ -1255,7 +1255,7 @@ CopyReadLineText(CopyFromState cstate)
 		prev_raw_ptr = input_buf_ptr;
 		c = copy_input_buf[input_buf_ptr++];
 
-		if (cstate->opts.csv_mode)
+		if (cstate->opts.format == COPY_FORMAT_CSV)
 		{
 			/*
 			 * If character is '\r', we may need to look ahead below.  Force
@@ -1294,7 +1294,7 @@ CopyReadLineText(CopyFromState cstate)
 		}
 
 		/* Process \r */
-		if (c == '\r' && (!cstate->opts.csv_mode || !in_quote))
+		if (c == '\r' && (cstate->opts.format != COPY_FORMAT_CSV || !in_quote))
 		{
 			/* Check for \r\n on first line, _and_ handle \r\n. */
 			if (cstate->eol_type == EOL_UNKNOWN ||
@@ -1322,10 +1322,10 @@ CopyReadLineText(CopyFromState cstate)
 					if (cstate->eol_type == EOL_CRNL)
 						ereport(ERROR,
 								(errcode(ERRCODE_BAD_COPY_FILE_FORMAT),
-								 !cstate->opts.csv_mode ?
+								 cstate->opts.format != COPY_FORMAT_CSV ?
 								 errmsg("literal carriage return found in data") :
 								 errmsg("unquoted carriage return found in data"),
-								 !cstate->opts.csv_mode ?
+								 cstate->opts.format != COPY_FORMAT_CSV ?
 								 errhint("Use \"\\r\" to represent carriage return.") :
 								 errhint("Use quoted CSV field to represent carriage return.")));
 
@@ -1339,10 +1339,10 @@ CopyReadLineText(CopyFromState cstate)
 			else if (cstate->eol_type == EOL_NL)
 				ereport(ERROR,
 						(errcode(ERRCODE_BAD_COPY_FILE_FORMAT),
-						 !cstate->opts.csv_mode ?
+						 cstate->opts.format != COPY_FORMAT_CSV ?
 						 errmsg("literal carriage return found in data") :
 						 errmsg("unquoted carriage return found in data"),
-						 !cstate->opts.csv_mode ?
+						 cstate->opts.format != COPY_FORMAT_CSV ?
 						 errhint("Use \"\\r\" to represent carriage return.") :
 						 errhint("Use quoted CSV field to represent carriage return.")));
 			/* If reach here, we have found the line terminator */
@@ -1350,15 +1350,15 @@ CopyReadLineText(CopyFromState cstate)
 		}
 
 		/* Process \n */
-		if (c == '\n' && (!cstate->opts.csv_mode || !in_quote))
+		if (c == '\n' && (cstate->opts.format != COPY_FORMAT_CSV || !in_quote))
 		{
 			if (cstate->eol_type == EOL_CR || cstate->eol_type == EOL_CRNL)
 				ereport(ERROR,
 						(errcode(ERRCODE_BAD_COPY_FILE_FORMAT),
-						 !cstate->opts.csv_mode ?
+						 cstate->opts.format != COPY_FORMAT_CSV ?
 						 errmsg("literal newline found in data") :
 						 errmsg("unquoted newline found in data"),
-						 !cstate->opts.csv_mode ?
+						 cstate->opts.format != COPY_FORMAT_CSV ?
 						 errhint("Use \"\\n\" to represent newline.") :
 						 errhint("Use quoted CSV field to represent newline.")));
 			cstate->eol_type = EOL_NL;	/* in case not set yet */
@@ -1370,7 +1370,7 @@ CopyReadLineText(CopyFromState cstate)
 		 * Process backslash, except in CSV mode where backslash is a normal
 		 * character.
 		 */
-		if (c == '\\' && !cstate->opts.csv_mode)
+		if (c == '\\' && cstate->opts.format != COPY_FORMAT_CSV)
 		{
 			char		c2;
 
diff --git a/src/backend/commands/copyto.c b/src/backend/commands/copyto.c
index 99cb23cb34..ea90af28a9 100644
--- a/src/backend/commands/copyto.c
+++ b/src/backend/commands/copyto.c
@@ -134,7 +134,7 @@ SendCopyBegin(CopyToState cstate)
 {
 	StringInfoData buf;
 	int			natts = list_length(cstate->attnumlist);
-	int16		format = (cstate->opts.binary ? 1 : 0);
+	int16		format = (cstate->opts.format == COPY_FORMAT_BINARY ? 1 : 0);
 	int			i;
 
 	pq_beginmessage(&buf, PqMsg_CopyOutResponse);
@@ -191,7 +191,7 @@ CopySendEndOfRow(CopyToState cstate)
 	switch (cstate->copy_dest)
 	{
 		case COPY_FILE:
-			if (!cstate->opts.binary)
+			if (cstate->opts.format != COPY_FORMAT_BINARY)
 			{
 				/* Default line termination depends on platform */
 #ifndef WIN32
@@ -236,7 +236,7 @@ CopySendEndOfRow(CopyToState cstate)
 			break;
 		case COPY_FRONTEND:
 			/* The FE/BE protocol uses \n as newline for all platforms */
-			if (!cstate->opts.binary)
+			if (cstate->opts.format != COPY_FORMAT_BINARY)
 				CopySendChar(cstate, '\n');
 
 			/* Dump the accumulated row as one CopyData message */
@@ -775,7 +775,7 @@ DoCopyTo(CopyToState cstate)
 		bool		isvarlena;
 		Form_pg_attribute attr = TupleDescAttr(tupDesc, attnum - 1);
 
-		if (cstate->opts.binary)
+		if (cstate->opts.format == COPY_FORMAT_BINARY)
 			getTypeBinaryOutputInfo(attr->atttypid,
 									&out_func_oid,
 									&isvarlena);
@@ -796,7 +796,7 @@ DoCopyTo(CopyToState cstate)
 											   "COPY TO",
 											   ALLOCSET_DEFAULT_SIZES);
 
-	if (cstate->opts.binary)
+	if (cstate->opts.format == COPY_FORMAT_BINARY)
 	{
 		/* Generate header for a binary copy */
 		int32		tmp;
@@ -837,7 +837,7 @@ DoCopyTo(CopyToState cstate)
 
 				colname = NameStr(TupleDescAttr(tupDesc, attnum - 1)->attname);
 
-				if (cstate->opts.csv_mode)
+				if (cstate->opts.format == COPY_FORMAT_CSV)
 					CopyAttributeOutCSV(cstate, colname, false);
 				else
 					CopyAttributeOutText(cstate, colname);
@@ -884,7 +884,7 @@ DoCopyTo(CopyToState cstate)
 		processed = ((DR_copy *) cstate->queryDesc->dest)->processed;
 	}
 
-	if (cstate->opts.binary)
+	if (cstate->opts.format == COPY_FORMAT_BINARY)
 	{
 		/* Generate trailer for a binary copy */
 		CopySendInt16(cstate, -1);
@@ -912,7 +912,7 @@ CopyOneRowTo(CopyToState cstate, TupleTableSlot *slot)
 	MemoryContextReset(cstate->rowcontext);
 	oldcontext = MemoryContextSwitchTo(cstate->rowcontext);
 
-	if (cstate->opts.binary)
+	if (cstate->opts.format == COPY_FORMAT_BINARY)
 	{
 		/* Binary per-tuple header */
 		CopySendInt16(cstate, list_length(cstate->attnumlist));
@@ -921,7 +921,7 @@ CopyOneRowTo(CopyToState cstate, TupleTableSlot *slot)
 	/* Make sure the tuple is fully deconstructed */
 	slot_getallattrs(slot);
 
-	if (!cstate->opts.binary)
+	if (cstate->opts.format != COPY_FORMAT_BINARY)
 	{
 		bool		need_delim = false;
 
@@ -941,7 +941,7 @@ CopyOneRowTo(CopyToState cstate, TupleTableSlot *slot)
 			{
 				string = OutputFunctionCall(&out_functions[attnum - 1],
 											value);
-				if (cstate->opts.csv_mode)
+				if (cstate->opts.format == COPY_FORMAT_CSV)
 					CopyAttributeOutCSV(cstate, string,
 										cstate->opts.force_quote_flags[attnum - 1]);
 				else
diff --git a/src/include/commands/copy.h b/src/include/commands/copy.h
index 06dfdfef72..7a1ee65601 100644
--- a/src/include/commands/copy.h
+++ b/src/include/commands/copy.h
@@ -51,6 +51,16 @@ typedef enum CopyLogVerbosityChoice
 	COPY_LOG_VERBOSITY_VERBOSE, /* logs additional messages */
 } CopyLogVerbosityChoice;
 
+/*
+ * Represents the format of the COPY operation.
+ */
+typedef enum CopyFormat
+{
+	COPY_FORMAT_TEXT = 0,
+	COPY_FORMAT_BINARY,
+	COPY_FORMAT_CSV,
+} CopyFormat;
+
 /*
  * A struct to hold COPY options, in a parsed form. All of these are related
  * to formatting, except for 'freeze', which doesn't really belong here, but
@@ -61,9 +71,8 @@ typedef struct CopyFormatOptions
 	/* parameters from the COPY command */
 	int			file_encoding;	/* file or remote side's character encoding,
 								 * -1 if not specified */
-	bool		binary;			/* binary format? */
+	CopyFormat	format;			/* format of the COPY operation */
 	bool		freeze;			/* freeze rows on loading? */
-	bool		csv_mode;		/* Comma Separated Value format? */
 	CopyHeaderChoice header_line;	/* header line? */
 	char	   *null_print;		/* NULL marker string (server encoding!) */
 	int			null_print_len; /* length of same */
diff --git a/src/tools/pgindent/typedefs.list b/src/tools/pgindent/typedefs.list
index a2644a2e65..d88382cdcc 100644
--- a/src/tools/pgindent/typedefs.list
+++ b/src/tools/pgindent/typedefs.list
@@ -496,6 +496,7 @@ ConversionLocation
 ConvertRowtypeExpr
 CookedConstraint
 CopyDest
+CopyFormat
 CopyFormatOptions
 CopyFromState
 CopyFromStateData
-- 
2.34.1

---

From: Junwang Zhao <[email-masked]>
Date: Sun, 2 Mar 2025 13:28:07 +0800

> the following are the benchmarks info:

I've refactored the patch to adapt the newly introduced CopyToRoutine struct,
see 2e4127b6d2.

v15-0001 is the merged one of v14-0001 and v14-0002

There are some other ongoing *copy to/from* refactors[1] which we can benefit
to make the code cleaner, especially the checks done in ProcessCopyOptions.

[1]: https://www.postgresql.org/message-id/20250301.115009.424844407736647598.kou%40clear-code.com

-- 
Regards
Junwang Zhao

---

From: jian he <[email-masked]>
Date: Tue, 11 Mar 2025 16:23:42 +0800

hi.

git apply --check $PATCHES/v15-0001-Introduce-json-format-for-COPY-TO.patch
error: patch failed: src/backend/commands/copyfrom.c:155
error: src/backend/commands/copyfrom.c: patch does not apply
error: patch failed: src/backend/commands/copyto.c:176
error: src/backend/commands/copyto.c: patch does not apply

seems to need rebase.
the attachment is the rebase, minor comments tweaks, and commit message tweaks.

another issue is this patch entry in commitfest [1] status is: Not processed,
which means no cfbots CI tests, seems not great.
not sure how to resolve this issue....

[1] https://commitfest.postgresql.org/patch/4716/
From 24e1858722dbb25c4842d0ec2dee5b1047edcc23 Mon Sep 17 00:00:00 2001
From: jian he <[email-masked]>
Date: Tue, 11 Mar 2025 16:19:55 +0800
Subject: [PATCH v16 2/2] Add option force_array for COPY JSON FORMAT

refactored by Junwang Zhao to adapt the newly introduced CopyToRoutine struct(2e4127b6d2).

Author: Joe Conway <[email-masked]>
discussion: https://postgr.es/m/CALvfUkBxTYy5uWPFVwpk_7ii2zgT07t3d-yR_cy4sfrrLU%3Dkcg%40mail.gmail.com
discussion: https://postgr.es/m/[email-masked]
---
 doc/src/sgml/ref/copy.sgml         | 14 ++++++++++++
 src/backend/commands/copy.c        | 13 ++++++++++++
 src/backend/commands/copyto.c      | 34 +++++++++++++++++++++++++++++-
 src/bin/psql/tab-complete.in.c     |  2 +-
 src/include/commands/copy.h        |  1 +
 src/test/regress/expected/copy.out | 23 ++++++++++++++++++++
 src/test/regress/sql/copy.sql      |  8 +++++++
 7 files changed, 93 insertions(+), 2 deletions(-)

index 9c519d8a9e2..7b3c913d4ee 100644
 
+   <varlistentry>
index b6f74c798d0..7b4c64ea97e 100644
 
@@ -906,6 +914,11 @@ ProcessCopyOptions(ParseState *pstate,
 
index c1d4cbeedea..393c0440ad7 100644
@@ -84,6 +84,7 @@ typedef struct CopyToStateData
 
 	CopyFormatOptions opts;
@@ -128,6 +129,7 @@ static void CopyToTextLikeOneRow(CopyToState cstate, TupleTableSlot *slot,
 								 bool is_csv);
 static void CopyToTextLikeEnd(CopyToState cstate);
 static void CopyToJsonOneRow(CopyToState cstate, TupleTableSlot *slot);
+static void CopyToJsonEnd(CopyToState cstate);
 static void CopyToBinaryStart(CopyToState cstate, TupleDesc tupDesc);
 static void CopyToBinaryOutFunc(CopyToState cstate, Oid atttypid, FmgrInfo *finfo);
 static void CopyToBinaryOneRow(CopyToState cstate, TupleTableSlot *slot);
@@ -172,7 +174,7 @@ static const CopyToRoutine CopyToRoutineJson = {
 	.CopyToStart = CopyToTextLikeStart,
 	.CopyToOutFunc = CopyToTextLikeOutFunc,
 	.CopyToOneRow = CopyToJsonOneRow,
-	.CopyToEnd = CopyToTextLikeEnd,
+	.CopyToEnd = CopyToJsonEnd,
 };
 
 /* binary format */
@@ -238,6 +240,16 @@ CopyToTextLikeStart(CopyToState cstate, TupleDesc tupDesc)
 
 		CopySendTextLikeEndOfRow(cstate);
 	}
+
+	/*
+	 * If JSON has been requested, and FORCE_ARRAY has been specified send
+	 * the opening bracket.
+	 */
+	if (cstate->opts.format == COPY_FORMAT_JSON && cstate->opts.force_array)
+	{
+		CopySendChar(cstate, '[');
+		CopySendTextLikeEndOfRow(cstate);
+	}
 }
 
 /*
@@ -349,11 +361,31 @@ CopyToJsonOneRow(CopyToState cstate, TupleTableSlot *slot)
 	result = makeStringInfo();
 	composite_to_json(rowdata, result, false);

 	CopySendTextLikeEndOfRow(cstate);
 }
 
+/* Implementation of the end callback for json format */
+static void
+CopyToJsonEnd(CopyToState cstate)
+{
+	if (cstate->opts.force_array)
+	{
+		CopySendChar(cstate, ']');
+		CopySendTextLikeEndOfRow(cstate);
+	}
+}
+
 /*
  * Implementation of the start callback for binary format. Send a header
  * for a binary copy.
diff --git a/src/bin/psql/tab-complete.in.c b/src/bin/psql/tab-complete.in.c
index 6069b118834..e0c7412ec0a 100644
--- a/src/bin/psql/tab-complete.in.c
+++ b/src/bin/psql/tab-complete.in.c
@@ -3278,7 +3278,7 @@ match_previous_words(int pattern_id,
 	else if (Matches("COPY|\\copy", MatchAny, "FROM|TO", MatchAny, "WITH", "("))
 		COMPLETE_WITH("FORMAT", "FREEZE", "DELIMITER", "NULL",
 					  "HEADER", "QUOTE", "ESCAPE", "FORCE_QUOTE",
-					  "FORCE_NOT_NULL", "FORCE_NULL", "ENCODING", "DEFAULT",
+					  "FORCE_NOT_NULL", "FORCE_NULL", "FORCE_ARRAY", "ENCODING", "DEFAULT",
 					  "ON_ERROR", "LOG_VERBOSITY");
 
index 07fcc2bc9ac..fa8a8ab7e31 100644
@@ -89,6 +89,7 @@ typedef struct CopyFormatOptions
 	List	   *force_notnull;	/* list of column names */
 	bool		force_notnull_all;	/* FORCE_NOT_NULL *? */
 	bool	   *force_notnull_flags;	/* per-column CSV FNN flags */
index 0d4cfc0b60a..3d0781700b3 100644
index 6ee96f5aa51..2781c24bd84 100644
@@ -101,6 +101,14 @@ copy copytest to stdout (format json, on_error ignore);
 
+--Error

From 71cf17c9d1aefecc89cc388b979bbfc9952898c8 Mon Sep 17 00:00:00 2001
From: jian he <[email-masked]>
Date: Tue, 11 Mar 2025 15:53:37 +0800
Subject: [PATCH v16 1/2] Introduce json format for COPY TO

CopyFormat enum part was coied from Joel Jacobson <[email-masked]>
refactored by Jian He to fix some miscellaneous issue.
refactored by Junwang Zhao to adapt the newly introduced CopyToRoutine struct(2e4127b6d2).

Author: Joe Conway <[email-masked]>
Reviewed-by: "Andrey M. Borodin" <[email-masked]>,
Reviewed-by: Dean Rasheed <[email-masked]>,
Reviewed-by: Daniel Verite <[email-masked]>,
Reviewed-by: Andrew Dunstan <[email-masked]>,
Reviewed-by: Davin Shearer <[email-masked]>,
Reviewed-by: Masahiko Sawada <[email-masked]>,
Reviewed-by: Alvaro Herrera <[email-masked]>

discussion: https://postgr.es/m/CALvfUkBxTYy5uWPFVwpk_7ii2zgT07t3d-yR_cy4sfrrLU%3Dkcg%40mail.gmail.com
discussion: https://postgr.es/m/[email-masked]
---
 doc/src/sgml/ref/copy.sgml           | 13 +++--
 src/backend/commands/copy.c          | 77 ++++++++++++++++++--------
 src/backend/commands/copyfrom.c      |  6 +-
 src/backend/commands/copyfromparse.c |  6 +-
 src/backend/commands/copyto.c        | 83 ++++++++++++++++++++++++----
 src/backend/parser/gram.y            |  8 +++
 src/backend/utils/adt/json.c         |  5 +-
 src/bin/psql/tab-complete.in.c       |  2 +-
 src/include/commands/copy.h          | 14 ++++-
 src/include/utils/json.h             |  2 +
 src/test/regress/expected/copy.out   | 74 +++++++++++++++++++++++++
 src/test/regress/sql/copy.sql        | 47 ++++++++++++++++
 src/tools/pgindent/typedefs.list     |  1 +
 13 files changed, 286 insertions(+), 52 deletions(-)

index df093da97c5..9c519d8a9e2 100644

      <note>
index cfca9d9dc29..b6f74c798d0 100644
@@ -525,11 +525,13 @@ ProcessCopyOptions(ParseState *pstate,
@@ -689,31 +691,47 @@ ProcessCopyOptions(ParseState *pstate,

 

@@ -761,7 +779,7 @@ ProcessCopyOptions(ParseState *pstate,
@@ -769,43 +787,48 @@ ProcessCopyOptions(ParseState *pstate,
 
 	/* Check header */

 
 
 	/* Check escape */

 	/* Check force_quote */
-	if (!opts_out->csv_mode && (opts_out->force_quote || opts_out->force_quote_all))
+	if (opts_out->format != COPY_FORMAT_CSV && (opts_out->force_quote || opts_out->force_quote_all))
 		ereport(ERROR,
 				(errcode(ERRCODE_FEATURE_NOT_SUPPORTED),
 		/*- translator: %s is the name of a COPY option, e.g. ON_ERROR */
@@ -819,8 +842,8 @@ ProcessCopyOptions(ParseState *pstate,
 						"COPY FROM")));
 
 	/* Check force_notnull */
-	if (!opts_out->csv_mode && (opts_out->force_notnull != NIL ||
-								opts_out->force_notnull_all))
+	if (opts_out->format != COPY_FORMAT_CSV && (opts_out->force_notnull != NIL ||
+												opts_out->force_notnull_all))
 		ereport(ERROR,
 				(errcode(ERRCODE_FEATURE_NOT_SUPPORTED),
 		/*- translator: %s is the name of a COPY option, e.g. ON_ERROR */
@@ -835,8 +858,8 @@ ProcessCopyOptions(ParseState *pstate,
 						"COPY TO")));
 
 	/* Check force_null */
@@ -860,7 +883,7 @@ ProcessCopyOptions(ParseState *pstate,
 						"NULL")));
 
@@ -877,6 +900,12 @@ ProcessCopyOptions(ParseState *pstate,
 
@@ -896,7 +925,7 @@ ProcessCopyOptions(ParseState *pstate,
 							"DEFAULT")));
 
 		/* Don't allow the CSV quote char to appear in the default string. */
-		if (opts_out->csv_mode &&
+		if (opts_out->format == COPY_FORMAT_CSV &&
 			strchr(opts_out->default_print, opts_out->quote[0]) != NULL)
 			ereport(ERROR,
 					(errcode(ERRCODE_FEATURE_NOT_SUPPORTED),
@@ -913,7 +942,7 @@ ProcessCopyOptions(ParseState *pstate,
 					 errmsg("NULL specification and DEFAULT specification cannot be the same")));
 	}
 	/* Check on_error */
-	if (opts_out->binary && opts_out->on_error != COPY_ON_ERROR_STOP)
+	if (opts_out->format == COPY_FORMAT_BINARY && opts_out->on_error != COPY_ON_ERROR_STOP)
 		ereport(ERROR,
 				(errcode(ERRCODE_SYNTAX_ERROR),
 				 errmsg("only ON_ERROR STOP is allowed in BINARY mode")));
diff --git a/src/backend/commands/copyfrom.c b/src/backend/commands/copyfrom.c
index bcf66f0adf8..bfe1937539b 100644
--- a/src/backend/commands/copyfrom.c
+++ b/src/backend/commands/copyfrom.c
@@ -155,9 +155,9 @@ static const CopyFromRoutine CopyFromRoutineBinary = {
 static const CopyFromRoutine *
 CopyFromGetRoutine(const CopyFormatOptions *opts)
 {
-	if (opts->csv_mode)
+	if (opts->format == COPY_FORMAT_CSV)
 		return &CopyFromRoutineCSV;
-	else if (opts->binary)
+	else if (opts->format == COPY_FORMAT_BINARY)
 		return &CopyFromRoutineBinary;
 
 	/* default is text */
@@ -261,7 +261,7 @@ CopyFromErrorCallback(void *arg)
 				   cstate->cur_relname);
 		return;
 	}
-	if (cstate->opts.binary)
+	if (cstate->opts.format == COPY_FORMAT_BINARY)
 	{
 		/* can't usefully display the data */
 		if (cstate->cur_attname)
diff --git a/src/backend/commands/copyfromparse.c b/src/backend/commands/copyfromparse.c
index e8128f85e6b..02263f1b1f5 100644
--- a/src/backend/commands/copyfromparse.c
+++ b/src/backend/commands/copyfromparse.c
@@ -171,7 +171,7 @@ ReceiveCopyBegin(CopyFromState cstate)
 {
 	StringInfoData buf;
 	int			natts = list_length(cstate->attnumlist);
-	int16		format = (cstate->opts.binary ? 1 : 0);
+	int16		format = (cstate->opts.format == COPY_FORMAT_BINARY ? 1 : 0);
 	int			i;
 
 	pq_beginmessage(&buf, PqMsg_CopyInResponse);
@@ -747,7 +747,7 @@ bool
 NextCopyFromRawFields(CopyFromState cstate, char ***fields, int *nfields)
 {
 	return NextCopyFromRawFieldsInternal(cstate, fields, nfields,
-										 cstate->opts.csv_mode);
+										 cstate->opts.format == COPY_FORMAT_CSV);
 }
 
 /*
@@ -774,7 +774,7 @@ NextCopyFromRawFieldsInternal(CopyFromState cstate, char ***fields, int *nfields
 	bool		done;
 
 	/* only available for text or csv input */
-	Assert(!cstate->opts.binary);
+	Assert(!(cstate->opts.format == COPY_FORMAT_BINARY));
 
 	/* on input check that the header line is correct if needed */
 	if (cstate->cur_lineno == 0 && cstate->opts.header_line)
diff --git a/src/backend/commands/copyto.c b/src/backend/commands/copyto.c
index 84a3f3879a8..c1d4cbeedea 100644
--- a/src/backend/commands/copyto.c
+++ b/src/backend/commands/copyto.c
@@ -24,6 +24,7 @@
 #include "executor/execdesc.h"
 #include "executor/executor.h"
 #include "executor/tuptable.h"
+#include "funcapi.h"
 #include "libpq/libpq.h"
 #include "libpq/pqformat.h"
 #include "mb/pg_wchar.h"
@@ -31,6 +32,7 @@
 #include "pgstat.h"
 #include "storage/fd.h"
 #include "tcop/tcopprot.h"
+#include "utils/json.h"
 #include "utils/lsyscache.h"
 #include "utils/memutils.h"
 #include "utils/rel.h"
@@ -125,6 +127,7 @@ static void CopyToCSVOneRow(CopyToState cstate, TupleTableSlot *slot);
 static void CopyToTextLikeOneRow(CopyToState cstate, TupleTableSlot *slot,
 								 bool is_csv);
 static void CopyToTextLikeEnd(CopyToState cstate);
+static void CopyToJsonOneRow(CopyToState cstate, TupleTableSlot *slot);
 static void CopyToBinaryStart(CopyToState cstate, TupleDesc tupDesc);
 static void CopyToBinaryOutFunc(CopyToState cstate, Oid atttypid, FmgrInfo *finfo);
 static void CopyToBinaryOneRow(CopyToState cstate, TupleTableSlot *slot);
@@ -144,7 +147,7 @@ static void CopySendInt16(CopyToState cstate, int16 val);
 /*
  * COPY TO routines for built-in formats.
  *
- * CSV and text formats share the same TextLike routines except for the
+ * CSV and text, json formats share the same TextLike routines except for the
  * one-row callback.
  */
 
@@ -164,6 +167,14 @@ static const CopyToRoutine CopyToRoutineCSV = {
 	.CopyToEnd = CopyToTextLikeEnd,
 };
 
+/* json format */
+static const CopyToRoutine CopyToRoutineJson = {
+	.CopyToStart = CopyToTextLikeStart,
+	.CopyToOutFunc = CopyToTextLikeOutFunc,
+	.CopyToOneRow = CopyToJsonOneRow,
+	.CopyToEnd = CopyToTextLikeEnd,
+};
+
 /* binary format */
 static const CopyToRoutine CopyToRoutineBinary = {
 	.CopyToStart = CopyToBinaryStart,
@@ -176,16 +187,18 @@ static const CopyToRoutine CopyToRoutineBinary = {
 static const CopyToRoutine *
 CopyToGetRoutine(const CopyFormatOptions *opts)
 {
-	if (opts->csv_mode)
+	if (opts->format == COPY_FORMAT_CSV)
 		return &CopyToRoutineCSV;
-	else if (opts->binary)
+	else if (opts->format == COPY_FORMAT_BINARY)
 		return &CopyToRoutineBinary;
+	else if (opts->format == COPY_FORMAT_JSON)
+		return &CopyToRoutineJson;
 
 	/* default is text */
 	return &CopyToRoutineText;
 }
 
-/* Implementation of the start callback for text and CSV formats */
+/* Implementation of the start callback for text, CSV, and json formats */
 static void
 CopyToTextLikeStart(CopyToState cstate, TupleDesc tupDesc)
 {
@@ -204,6 +217,8 @@ CopyToTextLikeStart(CopyToState cstate, TupleDesc tupDesc)
 		ListCell   *cur;
 		bool		hdr_delim = false;
 
+		Assert(cstate->opts.format != COPY_FORMAT_JSON);
+
 		foreach(cur, cstate->attnumlist)
 		{
 			int			attnum = lfirst_int(cur);
@@ -215,7 +230,7 @@ CopyToTextLikeStart(CopyToState cstate, TupleDesc tupDesc)

@@ -226,7 +241,7 @@ CopyToTextLikeStart(CopyToState cstate, TupleDesc tupDesc)
 }
 
 /*
- * Implementation of the outfunc callback for text and CSV formats. Assign
+ * Implementation of the outfunc callback for text, CSV, and json formats. Assign
  * the output function data to the given *finfo.
  */
 static void
@@ -299,13 +314,46 @@ CopyToTextLikeOneRow(CopyToState cstate,
 	CopySendTextLikeEndOfRow(cstate);
 }
 
-/* Implementation of the end callback for text and CSV formats */
+/* Implementation of the end callback for text, CSV, and json formats */
 static void
 CopyToTextLikeEnd(CopyToState cstate)
 {
 	/* Nothing to do here */
 }
 
+/* Implementation of per-row callback for json format */
+static void
+CopyToJsonOneRow(CopyToState cstate, TupleTableSlot *slot)
+{
+	Datum		rowdata;
+	StringInfo	result;
+
+	/*
+	 * if the COPY TO source data come from query rather than plain table, we need
+	 * copy CopyToState->QueryDesc->TupleDesc to slot->tts_tupleDescriptor.
+	 * This is necessary because the slot's TupleDesc may change during query execution,
+	 * and we depend on it when calling composite_to_json.
+	 */
+	if (!cstate->rel)
+	{
+		memcpy(TupleDescAttr(slot->tts_tupleDescriptor, 0),
+			   TupleDescAttr(cstate->queryDesc->tupDesc, 0),
+			   cstate->queryDesc->tupDesc->natts * sizeof(FormData_pg_attribute));
+
+		for (int i = 0; i < cstate->queryDesc->tupDesc->natts; i++)
+			populate_compact_attribute(slot->tts_tupleDescriptor, i);
+
+		BlessTupleDesc(slot->tts_tupleDescriptor);
+	}
+	rowdata = ExecFetchSlotHeapTupleDatum(slot);
+	result = makeStringInfo();
+	composite_to_json(rowdata, result, false);
+
+	CopySendData(cstate, result->data, result->len);
+
+	CopySendTextLikeEndOfRow(cstate);
+}
+
 /*
  * Implementation of the start callback for binary format. Send a header
  * for a binary copy.
@@ -392,14 +440,25 @@ SendCopyBegin(CopyToState cstate)
 {
 	StringInfoData buf;
 	int			natts = list_length(cstate->attnumlist);
-	int16		format = (cstate->opts.binary ? 1 : 0);
+	int16		format = (cstate->opts.format == COPY_FORMAT_BINARY ? 1 : 0);
 	int			i;
 
@@ -499,7 +558,7 @@ CopySendEndOfRow(CopyToState cstate)
 }
 
 /*
- * Wrapper function of CopySendEndOfRow for text and CSV formats. Sends the
+ * Wrapper function of CopySendEndOfRow for text, CSV, and json formats. Sends the
  * line termination and do common appropriate things for the end of row.
  */
 static inline void
diff --git a/src/backend/parser/gram.y b/src/backend/parser/gram.y
index 271ae26cbaf..e26881bb13f 100644
--- a/src/backend/parser/gram.y
+++ b/src/backend/parser/gram.y
@@ -3493,6 +3493,10 @@ copy_opt_item:
 				{
 					$$ = makeDefElem("format", (Node *) makeString("csv"), @1);
 				}
+			| JSON
+				{
+					$$ = makeDefElem("format", (Node *) makeString("json"), @1);
+				}
 			| HEADER_P
 				{
 					$$ = makeDefElem("header", (Node *) makeBoolean(true), @1);
@@ -3575,6 +3579,10 @@ copy_generic_opt_elem:
 				{
 					$$ = makeDefElem($1, $2, @1);
 				}
+			| FORMAT_LA copy_generic_opt_arg
+				{
+					$$ = makeDefElem("format", $2, @1);
+				}
 		;
 
 copy_generic_opt_arg:
index 51452755f58..bf69347fa94 100644

 /*
  * Turn a composite / record into JSON.
+ * Exported so COPY TO can use it.
  */
-static void
+void
 composite_to_json(Datum composite, StringInfo result, bool use_line_feeds)
 {
 	HeapTupleHeader td;
diff --git a/src/bin/psql/tab-complete.in.c b/src/bin/psql/tab-complete.in.c
index 8432be641ac..6069b118834 100644
--- a/src/bin/psql/tab-complete.in.c
+++ b/src/bin/psql/tab-complete.in.c
@@ -3283,7 +3283,7 @@ match_previous_words(int pattern_id,

 	/* Complete COPY <sth> FROM filename WITH (ON_ERROR */
 	else if (Matches("COPY|\\copy", MatchAny, "FROM|TO", MatchAny, "WITH", "(", "ON_ERROR"))
diff --git a/src/include/commands/copy.h b/src/include/commands/copy.h
index 06dfdfef721..07fcc2bc9ac 100644
--- a/src/include/commands/copy.h
+++ b/src/include/commands/copy.h
@@ -51,6 +51,17 @@ typedef enum CopyLogVerbosityChoice
 	COPY_LOG_VERBOSITY_VERBOSE, /* logs additional messages */
 } CopyLogVerbosityChoice;
 
+/*
@@ -61,9 +72,8 @@ typedef struct CopyFormatOptions
index 49bbda7ac06..1fa8e2ce8e2 100644
 
index 06bae8c61ae..0d4cfc0b60a 100644
index 3009bdfdf89..6ee96f5aa51 100644

index 9840060997f..a2e4bfee0e2 100644
@@ -500,6 +500,7 @@ ConversionLocation
 ConvertRowtypeExpr
 CookedConstraint
 CopyDest
+CopyFormat
 CopyFormatOptions
 CopyFromRoutine
 CopyFromState
-- 
2.34.1

---

From: jian he <[email-masked]>
Date: Fri, 4 Jul 2025 13:27:08 +0800

On Tue, Mar 11, 2025 at 4:23 PM jian he <[email-masked]> wrote:

hi.
rebase and minor tweaks.
From 44c494fd8d7fdb9d8fd5d2d2a48f49b779d1bcb9 Mon Sep 17 00:00:00 2001
From: jian he <[email-masked]>
Date: Fri, 4 Jul 2025 13:25:00 +0800
Subject: [PATCH v17 2/2] Add option force_array for COPY JSON FORMAT

force_array option can only be used in COPY TO with JSON format.  it make the
output json output behave like json array type.  refactored by Junwang Zhao to
adapt the newly introduced CopyToRoutine struct(2e4127b6d2).

Author: Joe Conway <[email-masked]>
discussion: https://postgr.es/m/CALvfUkBxTYy5uWPFVwpk_7ii2zgT07t3d-yR_cy4sfrrLU%3Dkcg%40mail.gmail.com
discussion: https://postgr.es/m/[email-masked]
---
 doc/src/sgml/ref/copy.sgml         | 14 +++++++++++
 src/backend/commands/copy.c        | 13 +++++++++++
 src/backend/commands/copyto.c      | 37 +++++++++++++++++++++++++++++-
 src/bin/psql/tab-complete.in.c     |  2 +-
 src/include/commands/copy.h        |  1 +
 src/test/regress/expected/copy.out | 23 +++++++++++++++++++
 src/test/regress/sql/copy.sql      |  8 +++++++
 7 files changed, 96 insertions(+), 2 deletions(-)

diff --git a/doc/src/sgml/ref/copy.sgml b/doc/src/sgml/ref/copy.sgml
index 219604ad306..c01927864bd 100644
--- a/doc/src/sgml/ref/copy.sgml
+++ b/doc/src/sgml/ref/copy.sgml
@@ -40,6 +40,7 @@ COPY { <replaceable class="parameter">table_name</replaceable> [ ( <replaceable
     HEADER [ <replaceable class="parameter">boolean</replaceable> | <replaceable class="parameter">integer</replaceable> | MATCH ]
     QUOTE '<replaceable class="parameter">quote_character</replaceable>'
     ESCAPE '<replaceable class="parameter">escape_character</replaceable>'
+    FORCE_ARRAY [ <replaceable class="parameter">boolean</replaceable> ]
     FORCE_QUOTE { ( <replaceable class="parameter">column_name</replaceable> [, ...] ) | * }
     FORCE_NOT_NULL { ( <replaceable class="parameter">column_name</replaceable> [, ...] ) | * }
     FORCE_NULL { ( <replaceable class="parameter">column_name</replaceable> [, ...] ) | * }
@@ -366,6 +367,19 @@ COPY { <replaceable class="parameter">table_name</replaceable> [ ( <replaceable
     </listitem>
    </varlistentry>
 
+   <varlistentry>
+    <term><literal>FORCE_ARRAY</literal></term>
+    <listitem>
+     <para>
+      Force output of square brackets as array decorations at the beginning
+      and end of output, and commas between the rows. It is allowed only in
+      <command>COPY TO</command>, and only when using
+      <literal>json</literal> format. The default is
+      <literal>false</literal>.
+     </para>
+    </listitem>
+   </varlistentry>
+
    <varlistentry>
     <term><literal>FORCE_QUOTE</literal></term>
     <listitem>
diff --git a/src/backend/commands/copy.c b/src/backend/commands/copy.c
index 213e59cc435..d23ef99e395 100644
--- a/src/backend/commands/copy.c
+++ b/src/backend/commands/copy.c
@@ -514,6 +514,7 @@ ProcessCopyOptions(ParseState *pstate,
 	bool		on_error_specified = false;
 	bool		log_verbosity_specified = false;
 	bool		reject_limit_specified = false;
+	bool		force_array_specified = false;
 	ListCell   *option;
 
 	/* Support external use for option sanity checking */
@@ -670,6 +671,13 @@ ProcessCopyOptions(ParseState *pstate,
 								defel->defname),
 						 parser_errposition(pstate, defel->location)));
 		}
+		else if (strcmp(defel->defname, "force_array") == 0)
+		{
+			if (force_array_specified)
+				errorConflictingDefElem(defel, pstate);
+			force_array_specified = true;
+			opts_out->force_array = defGetBoolean(defel);
+		}
 		else if (strcmp(defel->defname, "on_error") == 0)
 		{
 			if (on_error_specified)
@@ -918,6 +926,11 @@ ProcessCopyOptions(ParseState *pstate,
 				errcode(ERRCODE_INVALID_PARAMETER_VALUE),
 				errmsg("COPY %s mode cannot be used with %s", "json", "COPY FROM"));
 
+	if (opts_out->format != COPY_FORMAT_JSON && opts_out->force_array)
+		ereport(ERROR,
+				errcode(ERRCODE_INVALID_PARAMETER_VALUE),
+				errmsg("COPY %s can only used with JSON mode", "FORCE_ARRAY"));
+
 	if (opts_out->default_print)
 	{
 		if (!is_from)
diff --git a/src/backend/commands/copyto.c b/src/backend/commands/copyto.c
index 34b72936bca..18061661fb1 100644
--- a/src/backend/commands/copyto.c
+++ b/src/backend/commands/copyto.c
@@ -84,6 +84,10 @@ 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? */
+
+	/* need delimiter to start next json array element */
+	bool		json_row_delim_needed;
+
 	copy_data_dest_cb data_dest_cb; /* function for writing data */
 
 	CopyFormatOptions opts;
@@ -128,6 +132,7 @@ static void CopyToTextLikeOneRow(CopyToState cstate, TupleTableSlot *slot,
 								 bool is_csv);
@@ -172,7 +177,7 @@ static const CopyToRoutine CopyToRoutineJson = {
 
 /* binary format */
@@ -238,6 +243,16 @@ CopyToTextLikeStart(CopyToState cstate, TupleDesc tupDesc)
 
+	 * If JSON has been requested, and FORCE_ARRAY has been specified send the
+	 * opening bracket.
 
 /*
@@ -349,11 +364,31 @@ CopyToJsonOneRow(CopyToState cstate, TupleTableSlot *slot)
 	result = makeStringInfo();
 	composite_to_json(rowdata, result, false);

 
index bd4c03be050..55313612398 100644
@@ -3303,7 +3303,7 @@ match_previous_words(int pattern_id,
 					  "ON_ERROR", "LOG_VERBOSITY", "REJECT_LIMIT");
 
index 85aedc267d6..7274b0d3ca5 100644
@@ -87,6 +87,7 @@ typedef struct CopyFormatOptions
index fcb8823e101..f3196fd5609 100644
index 80bd4a59239..c55aa08e99d 100644
@@ -100,6 +100,14 @@ copy copytest to stdout (format json, on_error ignore);
 
+--Error

From 984846c42dee4da6bd718cc24031bc0272d62f12 Mon Sep 17 00:00:00 2001
From: jian he <[email-masked]>
Date: Fri, 4 Jul 2025 13:23:30 +0800
Subject: [PATCH v17 1/2] json format for COPY TO

JSON format is only supported with the COPY TO operation. It is incompatible
with options such as HEADER, DEFAULT, NULL, DELIMITER, and several others. This
has been thoroughly tested in src/test/regress/sql/copy.sql

The CopyFormat enum was originally contributed by Joel Jacobson
[email-masked], later refactored by Jian He to address various issues, and
further adapted by Junwang Zhao to support the newly introduced CopyToRoutine
struct (commit 2e4127b6d2).

discussion: https://postgr.es/m/CALvfUkBxTYy5uWPFVwpk_7ii2zgT07t3d-yR_cy4sfrrLU%3Dkcg%40mail.gmail.com
discussion: https://postgr.es/m/[email-masked]
---
 doc/src/sgml/ref/copy.sgml           | 13 +++--
 src/backend/commands/copy.c          | 79 ++++++++++++++++++--------
 src/backend/commands/copyfrom.c      |  6 +-
 src/backend/commands/copyfromparse.c |  7 ++-
 src/backend/commands/copyto.c        | 83 ++++++++++++++++++++++++----
 src/backend/parser/gram.y            |  8 +++
 src/backend/utils/adt/json.c         |  5 +-
 src/bin/psql/tab-complete.in.c       |  2 +-
 src/include/commands/copy.h          | 14 ++++-
 src/include/utils/json.h             |  2 +
 src/test/regress/expected/copy.out   | 74 +++++++++++++++++++++++++
 src/test/regress/sql/copy.sql        | 46 +++++++++++++++
 src/tools/pgindent/typedefs.list     |  1 +
 13 files changed, 288 insertions(+), 52 deletions(-)

index c2d1fbc1fbe..219604ad306 100644
@@ -228,10 +228,15 @@ COPY { <replaceable class="parameter">table_name</replaceable> [ ( <replaceable
 
@@ -266,7 +271,7 @@ COPY { <replaceable class="parameter">table_name</replaceable> [ ( <replaceable
@@ -280,7 +285,7 @@ COPY { <replaceable class="parameter">table_name</replaceable> [ ( <replaceable
 
      <note>
@@ -303,7 +308,7 @@ COPY { <replaceable class="parameter">table_name</replaceable> [ ( <replaceable
       is found in the input file, the default value of the corresponding column
       will be used.
       This option is allowed only in <command>COPY FROM</command>, and only when
-      not using <literal>binary</literal> format.
+      not using <literal>binary</literal> or <literal>json</literal> format.
      </para>
     </listitem>
    </varlistentry>
@@ -330,7 +335,7 @@ COPY { <replaceable class="parameter">table_name</replaceable> [ ( <replaceable
       <command>COPY FROM</command> commands.
      </para>
      <para>
-      This option is not allowed when using <literal>binary</literal> format.
+      This option is not allowed when using <literal>binary</literal> or <literal>json</literal> format.
      </para>
     </listitem>
    </varlistentry>
diff --git a/src/backend/commands/copy.c b/src/backend/commands/copy.c
index fae9c41db65..213e59cc435 100644
--- a/src/backend/commands/copy.c
+++ b/src/backend/commands/copy.c
@@ -521,6 +521,8 @@ ProcessCopyOptions(ParseState *pstate,
 		opts_out = (CopyFormatOptions *) palloc0(sizeof(CopyFormatOptions));
 
 	opts_out->file_encoding = -1;
+	/* default format */
+	opts_out->format = COPY_FORMAT_TEXT;
 
 	/* Extract options from the statement node tree */
 	foreach(option, options)
@@ -535,11 +537,13 @@ ProcessCopyOptions(ParseState *pstate,
@@ -699,31 +703,47 @@ ProcessCopyOptions(ParseState *pstate,

 

@@ -771,7 +791,7 @@ ProcessCopyOptions(ParseState *pstate,
@@ -779,43 +799,48 @@ ProcessCopyOptions(ParseState *pstate,
 
 	/* Check header */
-	if (opts_out->binary && opts_out->header_line != COPY_HEADER_FALSE)
+	if (opts_out->format == COPY_FORMAT_BINARY && opts_out->header_line != COPY_HEADER_FALSE)
 		ereport(ERROR,
 				(errcode(ERRCODE_FEATURE_NOT_SUPPORTED),
 		/*- translator: %s is the name of a COPY option, e.g. ON_ERROR */
 				 errmsg("cannot specify %s in BINARY mode", "HEADER")));

 
 
 	/* Check escape */

 	/* Check force_quote */
@@ -829,8 +854,8 @@ ProcessCopyOptions(ParseState *pstate,
 						"COPY FROM")));
 
@@ -845,8 +870,8 @@ ProcessCopyOptions(ParseState *pstate,
 						"COPY TO")));
 
 	/* Check force_null */
@@ -870,7 +895,7 @@ ProcessCopyOptions(ParseState *pstate,
 						"NULL")));
 
@@ -887,6 +912,12 @@ ProcessCopyOptions(ParseState *pstate,
 
+	/* Check json format  */
+	if (opts_out->format == COPY_FORMAT_JSON && is_from)
+		ereport(ERROR,
+				errcode(ERRCODE_INVALID_PARAMETER_VALUE),
+				errmsg("COPY %s mode cannot be used with %s", "json", "COPY FROM"));
+
 	if (opts_out->default_print)
 	{
 		if (!is_from)
@@ -906,7 +937,7 @@ ProcessCopyOptions(ParseState *pstate,
 							"DEFAULT")));
 
@@ -923,7 +954,7 @@ ProcessCopyOptions(ParseState *pstate,
index fbbbc09a97b..6c4bd303841 100644
 
 	/* default is text */
index b1ae97b833d..578e6c0c9a2 100644

 /*
@@ -774,7 +774,8 @@ NextCopyFromRawFieldsInternal(CopyFromState cstate, char ***fields, int *nfields
 	bool		done = false;
 
 	/* only available for text or csv input */
-	Assert(!cstate->opts.binary);
+	Assert(cstate->opts.format == COPY_FORMAT_TEXT ||
+		   cstate->opts.format == COPY_FORMAT_CSV);
 
 	if (cstate->cur_lineno == 0 && cstate->opts.header_line != COPY_HEADER_FALSE)
index 67b94b91cae..34b72936bca 100644

+/* json format */
 
 	/* default is text */

 

 /*

+	 * if the COPY TO source data come from query rather than plain table, we
+	 * need copy CopyToState->QueryDesc->TupleDesc to slot->tts_tupleDescriptor.
+	 * This is necessary because the slot's TupleDesc may change during query
+	 * execution, and we depend on it when calling composite_to_json.

 /*
index 70a0d832a11..de0f5eb4118 100644
@@ -3492,6 +3492,10 @@ copy_opt_item:
@@ -3574,6 +3578,10 @@ copy_generic_opt_elem:
 
 copy_generic_opt_arg:

 /*
  * Turn a composite / record into JSON.
+ * Exported so COPY TO can use it.
  */
-static void
+void
 composite_to_json(Datum composite, StringInfo result, bool use_line_feeds)
 {
 	HeapTupleHeader td;
diff --git a/src/bin/psql/tab-complete.in.c b/src/bin/psql/tab-complete.in.c
index 53e7d35fe98..bd4c03be050 100644
--- a/src/bin/psql/tab-complete.in.c
+++ b/src/bin/psql/tab-complete.in.c
@@ -3308,7 +3308,7 @@ match_previous_words(int pattern_id,

 	/* Complete COPY <sth> FROM filename WITH (ON_ERROR */
 	else if (Matches("COPY|\\copy", MatchAny, "FROM|TO", MatchAny, "WITH", "(", "ON_ERROR"))
diff --git a/src/include/commands/copy.h b/src/include/commands/copy.h
index 541176e1980..85aedc267d6 100644
--- a/src/include/commands/copy.h
+++ b/src/include/commands/copy.h
@@ -48,6 +48,17 @@ typedef enum CopyLogVerbosityChoice
 	COPY_LOG_VERBOSITY_VERBOSE, /* logs additional messages */
 } CopyLogVerbosityChoice;
 
+/*
@@ -58,9 +69,8 @@ typedef struct CopyFormatOptions
 	int			header_line;	/* number of lines to skip or COPY_HEADER_XXX
 								 * value (see the above) */
 
index ac66eb55aee..fcb8823e101 100644
index a1316c73bac..80bd4a59239 100644
@@ -82,6 +82,52 @@ this is just a line full of junk that would error out if parsed

index 114bdafafdf..fa7d7b9244a 100644
@@ -518,6 +518,7 @@ ConversionLocation
 ConvertRowtypeExpr
 CookedConstraint
 CopyDest
+CopyFormat
 CopyFormatOptions
 CopyFromRoutine
 CopyFromState
-- 
2.34.1

---

From: jian he <[email-masked]>
Date: Sun, 10 Aug 2025 23:20:12 +0800

hi.

rebase and splitted into 3 patches.

v18-0001
+typedef enum CopyFormat
+{
+ COPY_FORMAT_TEXT = 0,
+ COPY_FORMAT_BINARY,
+ COPY_FORMAT_CSV,
+} CopyFormat;
remove CopyFormatOptions two boolean field
(binary, csv_mode)

v18-0002, v18-0003 is refactoring based on prior patch.
From e9104bcc0a6c4ca96df5ff3fdd3ae659885dd664 Mon Sep 17 00:00:00 2001
From: jian he <[email-masked]>
Date: Wed, 30 Jul 2025 19:50:41 +0800
Subject: [PATCH v18 3/3] Add option force_array for COPY JSON FORMAT

 
+   <varlistentry>
index 7fd41dba250..0a22272f3fe 100644
 
@@ -925,6 +933,11 @@ ProcessCopyOptions(ParseState *pstate,
 
index 13eb14debbd..6fc1d3e9fee 100644
 
 	CopyFormatOptions opts;
 
 /* binary format */

 /*

 
index 16db5373c5f..9c65376fc7e 100644
@@ -1212,7 +1212,7 @@ Copy_common_options, "DEFAULT", "FORCE_NOT_NULL", "FORCE_NULL", "FREEZE", \
 
 /* COPY TO options */
 #define Copy_to_options \
-Copy_common_options, "FORCE_QUOTE"
+Copy_common_options, "FORCE_QUOTE", "FORCE_ARRAY"
 
 /*
  * These object types were introduced later than our support cutoff of
index 0fc6e84352c..22626a13ba5 100644
@@ -112,6 +112,29 @@ LINE 1: copy copytest to stdout (format json, on_error ignore);
index 071986d427a..0f121b48f71 100644
 
+--Error

From e6bafe5eab9463b253e9697e0fbd82246c316a12 Mon Sep 17 00:00:00 2001
From: jian he <[email-masked]>
Date: Sun, 10 Aug 2025 23:13:38 +0800
Subject: [PATCH v18 2/3] json format for COPY TO

discussion: https://postgr.es/m/CALvfUkBxTYy5uWPFVwpk_7ii2zgT07t3d-yR_cy4sfrrLU%3Dkcg%40mail.gmail.com
discussion: https://postgr.es/m/[email-masked]
---
 doc/src/sgml/ref/copy.sgml         | 13 +++--
 src/backend/commands/copy.c        | 72 +++++++++++++++++++++-------
 src/backend/commands/copyto.c      | 76 ++++++++++++++++++++++++++----
 src/backend/parser/gram.y          |  8 ++++
 src/backend/utils/adt/json.c       |  5 +-
 src/bin/psql/tab-complete.in.c     |  4 +-
 src/include/commands/copy.h        |  1 +
 src/include/utils/json.h           |  2 +
 src/test/regress/expected/copy.out | 76 ++++++++++++++++++++++++++++++
 src/test/regress/sql/copy.sql      | 47 ++++++++++++++++++
 src/tools/pgindent/typedefs.list   |  1 +
 11 files changed, 271 insertions(+), 34 deletions(-)

 
      <note>
@@ -303,7 +308,7 @@ COPY { <replaceable class="parameter">table_name</replaceable> [ ( <replaceable
       is found in the input file, the default value of the corresponding column
       will be used.
       This option is allowed only in <command>COPY FROM</command>, and only when
-      not using <literal>binary</literal> format.
+      not using <literal>binary</literal> or <literal>json</literal> format.
      </para>
     </listitem>
    </varlistentry>
@@ -330,7 +335,7 @@ COPY { <replaceable class="parameter">table_name</replaceable> [ ( <replaceable
       <command>COPY FROM</command> commands.
      </para>
      <para>
-      This option is not allowed when using <literal>binary</literal> format.
+      This option is not allowed when using <literal>binary</literal> or <literal>json</literal> format.
      </para>
     </listitem>
    </varlistentry>
diff --git a/src/backend/commands/copy.c b/src/backend/commands/copy.c
index 68f69cfb9df..7fd41dba250 100644
--- a/src/backend/commands/copy.c
+++ b/src/backend/commands/copy.c
@@ -542,6 +542,8 @@ ProcessCopyOptions(ParseState *pstate,
 				opts_out->format = COPY_FORMAT_CSV;
 			else if (strcmp(fmt, "binary") == 0)
 				opts_out->format = COPY_FORMAT_BINARY;
+			else if (strcmp(fmt, "json") == 0)
+				opts_out->format = COPY_FORMAT_JSON;
 			else
 				ereport(ERROR,
 						(errcode(ERRCODE_INVALID_PARAMETER_VALUE),
@@ -701,21 +703,42 @@ ProcessCopyOptions(ParseState *pstate,
 	 * Check for incompatible options (must do these three before inserting
 	 * defaults)
 	 */
-	if (opts_out->format == COPY_FORMAT_BINARY && opts_out->delim)
-		ereport(ERROR,
-				(errcode(ERRCODE_SYNTAX_ERROR),
-		/*- translator: %s is the name of a COPY option, e.g. ON_ERROR */
-				 errmsg("cannot specify %s in BINARY mode", "DELIMITER")));
+	if (opts_out->delim)
+	{
+		if (opts_out->format == COPY_FORMAT_BINARY)
+			ereport(ERROR,
+					errcode(ERRCODE_SYNTAX_ERROR),
+			/*- translator: %s is the name of a COPY option, e.g. ON_ERROR */
+					errmsg("cannot specify %s in BINARY mode", "DELIMITER"));
+		else if (opts_out->format == COPY_FORMAT_JSON)
+			ereport(ERROR,
+					errcode(ERRCODE_SYNTAX_ERROR),
+					errmsg("cannot specify %s in JSON mode", "DELIMITER"));
+	}
 
-	if (opts_out->format == COPY_FORMAT_BINARY && opts_out->null_print)
-		ereport(ERROR,
-				(errcode(ERRCODE_SYNTAX_ERROR),
-				 errmsg("cannot specify %s in BINARY mode", "NULL")));
+	if (opts_out->null_print)
+	{
+		if (opts_out->format == COPY_FORMAT_BINARY)
+			ereport(ERROR,
+					errcode(ERRCODE_SYNTAX_ERROR),
+					errmsg("cannot specify %s in BINARY mode", "NULL"));
+		else if (opts_out->format == COPY_FORMAT_JSON)
+			ereport(ERROR,
+					errcode(ERRCODE_SYNTAX_ERROR),
+					errmsg("cannot specify %s in JSON mode", "NULL"));
+	}
 
-	if (opts_out->format == COPY_FORMAT_BINARY && opts_out->default_print)
-		ereport(ERROR,
-				(errcode(ERRCODE_SYNTAX_ERROR),
-				 errmsg("cannot specify %s in BINARY mode", "DEFAULT")));
+	if (opts_out->default_print)
+	{
+		if (opts_out->format == COPY_FORMAT_BINARY)
+			ereport(ERROR,
+					errcode(ERRCODE_SYNTAX_ERROR),
+					errmsg("cannot specify %s in BINARY mode", "DEFAULT"));
+		else if (opts_out->format == COPY_FORMAT_JSON)
+			ereport(ERROR,
+					errcode(ERRCODE_SYNTAX_ERROR),
+					errmsg("cannot specify %s in JSON mode", "DEFAULT"));
+	}
 
 	/* Set defaults for omitted options */
 	if (!opts_out->delim)
@@ -781,11 +804,18 @@ ProcessCopyOptions(ParseState *pstate,
 				 errmsg("COPY delimiter cannot be \"%s\"", opts_out->delim)));
 
 	/* Check header */
-	if (opts_out->format == COPY_FORMAT_BINARY && opts_out->header_line != COPY_HEADER_FALSE)
-		ereport(ERROR,
-				(errcode(ERRCODE_FEATURE_NOT_SUPPORTED),
-		/*- translator: %s is the name of a COPY option, e.g. ON_ERROR */
-				 errmsg("cannot specify %s in BINARY mode", "HEADER")));
+	if (opts_out->header_line != COPY_HEADER_FALSE)
+	{
+		if (opts_out->format == COPY_FORMAT_BINARY)
+			ereport(ERROR,
+					errcode(ERRCODE_FEATURE_NOT_SUPPORTED),
+			/*- translator: %s is the name of a COPY option, e.g. ON_ERROR */
+					errmsg("cannot specify %s in BINARY mode", "HEADER"));
+		else if(opts_out->format == COPY_FORMAT_JSON)
+			ereport(ERROR,
+					errcode(ERRCODE_FEATURE_NOT_SUPPORTED),
+					errmsg("cannot specify %s in JSON mode", "HEADER"));
+	}
 
 	/* Check quote */
 	if (opts_out->format != COPY_FORMAT_CSV && opts_out->quote != NULL)
@@ -889,6 +919,12 @@ ProcessCopyOptions(ParseState *pstate,
 				 errmsg("COPY %s cannot be used with %s", "FREEZE",
 						"COPY TO")));
 
index e990343bab0..13eb14debbd 100644

+/* json format */
@@ -180,12 +191,14 @@ CopyToGetRoutine(const CopyFormatOptions *opts)
 	else if (opts->format == COPY_FORMAT_BINARY)
 
 	/* default is text */

 
 /*

+	 * If COPY TO source data come from query rather than plain table, we need
@@ -397,9 +445,21 @@ SendCopyBegin(CopyToState cstate)
 
@@ -499,7 +559,7 @@ CopySendEndOfRow(CopyToState cstate)
 }
 
 /*
index db43034b9db..48e2242327e 100644
@@ -3528,6 +3528,10 @@ copy_opt_item:
@@ -3610,6 +3614,10 @@ copy_generic_opt_elem:
 
 copy_generic_opt_arg:
index e9d370cb3da..e517470bbc7 100644

 /*
  * Turn a composite / record into JSON.
+ * Exported so COPY TO can use it.
  */
-static void
+void
 composite_to_json(Datum composite, StringInfo result, bool use_line_feeds)
 {
 	HeapTupleHeader td;
diff --git a/src/bin/psql/tab-complete.in.c b/src/bin/psql/tab-complete.in.c
index 1f2ca946fc5..16db5373c5f 100644
--- a/src/bin/psql/tab-complete.in.c
+++ b/src/bin/psql/tab-complete.in.c
@@ -3344,8 +3344,10 @@ match_previous_words(int pattern_id,
 		COMPLETE_WITH(Copy_to_options);
 
 	/* Complete COPY <sth> FROM|TO filename WITH (FORMAT */
-	else if (Matches("COPY|\\copy", MatchAny, "FROM|TO", MatchAny, "WITH", "(", "FORMAT"))
+	else if (Matches("COPY|\\copy", MatchAny, "FROM", MatchAny, "WITH", "(", "FORMAT"))
 		COMPLETE_WITH("binary", "csv", "text");
+	else if (Matches("COPY|\\copy", MatchAny, "TO", MatchAny, "WITH", "(", "FORMAT"))
+		COMPLETE_WITH("binary", "csv", "text", "json");
 
 	/* Complete COPY <sth> FROM filename WITH (ON_ERROR */
 	else if (Matches("COPY|\\copy", MatchAny, "FROM", MatchAny, "WITH", "(", "ON_ERROR"))
diff --git a/src/include/commands/copy.h b/src/include/commands/copy.h
index 686653233b2..85aedc267d6 100644
--- a/src/include/commands/copy.h
+++ b/src/include/commands/copy.h
@@ -56,6 +56,7 @@ typedef enum CopyFormat
 	COPY_FORMAT_TEXT = 0,
 	COPY_FORMAT_BINARY,
 	COPY_FORMAT_CSV,
+	COPY_FORMAT_JSON,
 } CopyFormat;
 
 /*
 
index ac66eb55aee..0fc6e84352c 100644
@@ -73,6 +73,82 @@ copy copytest3 to stdout csv header;
+copy copytest to stdout (format json, header 1);
index a1316c73bac..071986d427a 100644

+copy copytest to stdout (format json, header 1);
index e6f2e93b2d6..374b40d14de 100644
@@ -515,6 +515,7 @@ ConversionLocation
 ConvertRowtypeExpr
 CookedConstraint
 CopyDest
+CopyFormat
 CopyFormatOptions
 CopyFromRoutine
 CopyFromState
-- 
2.34.1

From 1b30cb9b34b770cebc0bb20af867baec2f72aedb Mon Sep 17 00:00:00 2001
From: jian he <[email-masked]>
Date: Wed, 30 Jul 2025 16:48:56 +0800
Subject: [PATCH v18 1/3] introduce CopyFormat refactor CopyFormatOptions

Currently, COPY command format is determined by two booleans, binary and
csv_mode, within CopyFormatOptions. This approach, while functional, isn't ideal
for future expansion.

To simplify adding new formats, we've introduced an enum CopyFormat.  This makes
the code cleaner and more maintainable, allowing for easier integration of
additional formats down the line.

The CopyFormat enum was originally contributed by Joel Jacobson
[email-masked], later refactored by Jian He to address various issues.

discussion: https://postgr.es/m/CALvfUkBxTYy5uWPFVwpk_7ii2zgT07t3d-yR_cy4sfrrLU%3Dkcg%40mail.gmail.com
discussion: https://postgr.es/m/[email-masked]
---
 src/backend/commands/copy.c          | 50 +++++++++++++++-------------
 src/backend/commands/copyfrom.c      |  6 ++--
 src/backend/commands/copyfromparse.c |  7 ++--
 src/backend/commands/copyto.c        |  8 ++---
 src/include/commands/copy.h          | 13 ++++++--
 5 files changed, 48 insertions(+), 36 deletions(-)

index fae9c41db65..68f69cfb9df 100644

@@ -535,11 +537,11 @@ ProcessCopyOptions(ParseState *pstate,
@@ -699,31 +701,31 @@ ProcessCopyOptions(ParseState *pstate,

 
 	/* Set defaults for omitted options */
 	if (!opts_out->delim)
-		opts_out->delim = opts_out->csv_mode ? "," : "\t";
+		opts_out->delim = (opts_out->format == COPY_FORMAT_CSV) ? "," : "\t";
 
 	if (!opts_out->null_print)
-		opts_out->null_print = opts_out->csv_mode ? "" : "\\N";
+		opts_out->null_print = (opts_out->format == COPY_FORMAT_CSV) ? "" : "\\N";
 	opts_out->null_print_len = strlen(opts_out->null_print);
 
@@ -771,7 +773,7 @@ ProcessCopyOptions(ParseState *pstate,
@@ -779,43 +781,43 @@ ProcessCopyOptions(ParseState *pstate,
 
 	/* Check header */
 
 	/* Check quote */

 
 	/* Check escape */

 	/* Check force_quote */
@@ -829,8 +831,8 @@ ProcessCopyOptions(ParseState *pstate,
 						"COPY FROM")));
 
@@ -845,8 +847,8 @@ ProcessCopyOptions(ParseState *pstate,
 						"COPY TO")));
 
 	/* Check force_null */
@@ -870,7 +872,7 @@ ProcessCopyOptions(ParseState *pstate,
 						"NULL")));
 
@@ -906,7 +908,7 @@ ProcessCopyOptions(ParseState *pstate,
 							"DEFAULT")));
 
@@ -923,7 +925,7 @@ ProcessCopyOptions(ParseState *pstate,
 
 	/* default is text */

 /*

index 67b94b91cae..e990343bab0 100644
@@ -176,9 +176,9 @@ static const CopyToRoutine CopyToRoutineBinary = {
 
 	/* default is text */
@@ -215,7 +215,7 @@ CopyToTextLikeStart(CopyToState cstate, TupleDesc tupDesc)

@@ -392,7 +392,7 @@ SendCopyBegin(CopyToState cstate)
 
 	pq_beginmessage(&buf, PqMsg_CopyOutResponse);
diff --git a/src/include/commands/copy.h b/src/include/commands/copy.h
index 541176e1980..686653233b2 100644
--- a/src/include/commands/copy.h
+++ b/src/include/commands/copy.h
@@ -48,6 +48,16 @@ typedef enum CopyLogVerbosityChoice
 	COPY_LOG_VERBOSITY_VERBOSE, /* logs additional messages */
 } CopyLogVerbosityChoice;
 
+/*
@@ -58,9 +68,8 @@ typedef struct CopyFormatOptions

---

From: jian he <[email-masked]>
Date: Wed, 1 Oct 2025 14:16:42 +0800

> v18-0002, v18-0003 is refactoring based on prior patch.

hi.
v19 attached, same as v18.
repost it so that CFbot can pick up the latest patchset.
From 3869884bd47aaf681cc3c2bf96fe59d11069155c Mon Sep 17 00:00:00 2001
From: jian he <[email-masked]>
Date: Sun, 10 Aug 2025 23:13:38 +0800
Subject: [PATCH v19 2/3] json format for COPY TO

 
 
      <note>

 
 
 	/* Check header */
 
 	/* Check quote */

 
+/* json format */
 
 	/* default is text */

 
 /*

 
 
 /*
index f1def67ac7c..664b0483dbd 100644
@@ -3530,6 +3530,10 @@ copy_opt_item:
@@ -3612,6 +3616,10 @@ copy_generic_opt_elem:
 
 copy_generic_opt_arg:

 /*
  * Turn a composite / record into JSON.
+ * Exported so COPY TO can use it.
  */
-static void
+void
 composite_to_json(Datum composite, StringInfo result, bool use_line_feeds)
 {
 	HeapTupleHeader td;
diff --git a/src/bin/psql/tab-complete.in.c b/src/bin/psql/tab-complete.in.c
index 6176741d20b..15fe7b37b0e 100644
--- a/src/bin/psql/tab-complete.in.c
+++ b/src/bin/psql/tab-complete.in.c
@@ -3347,8 +3347,10 @@ match_previous_words(int pattern_id,
 		COMPLETE_WITH(Copy_to_options);

 
 /*

 
index 37f26f6c6b7..d85c2ec3c70 100644

From 2abacfaebc0fd5a37a3b797df92c3b3a85761afa Mon Sep 17 00:00:00 2001
From: jian he <[email-masked]>
Date: Wed, 30 Jul 2025 19:50:41 +0800
Subject: [PATCH v19 3/3] Add option force_array for COPY JSON FORMAT

 
+   <varlistentry>

 
 	CopyFormatOptions opts;
 
 /* binary format */

 /*

 
index 15fe7b37b0e..a42fb6c0740 100644
@@ -1222,7 +1222,7 @@ Copy_common_options, "DEFAULT", "FORCE_NOT_NULL", "FORCE_NULL", "FREEZE", \
 
 /* COPY TO options */
 
 /*
 
+--Error

From 6c3e515ce84bdba4e0c96f46d8d5543481d3e1ca Mon Sep 17 00:00:00 2001
From: jian he <[email-masked]>
Date: Wed, 30 Jul 2025 16:48:56 +0800
Subject: [PATCH v19 1/3] introduce CopyFormat refactor CopyFormatOptions

 

 

 
 
 	/* Check header */
 
 	/* Check quote */

 
 	/* Check escape */

 	/* Check force_quote */

 	/* Check force_null */

index 12781963b4f..ba31b227d5f 100644
 
 	/* default is text */

 /*

 
 	/* default is text */

 
 
+/*