>
On 2026-03-05 Th 1:06 PM, Joe Conway wrote:
Then I reworked the way this works. In order to support column lists
with JSON output, we need to deal with individual columns instead of
whole records. This involved quite a number of changes, as can be
seen in patch 4. This involved exporting a new small function from
json.c.
The result is a lot cleaner, I believe, and in my benchmarking is
faster by a factor of almost 2.
Andrew,
I don't see the actual patches. Did I miss it somewhere?
Nope. Bad hair day apparently.
cheers
andrew
--
Andrew Dunstan
EDB: https://www.enterprisedb.com
From b50c26e9e2a76001315fb3e5000f2d33e254c741 Mon Sep 17 00:00:00 2001
From: jian he <[email protected]>
Date: Wed, 21 Jan 2026 18:38:24 +0800
Subject: [PATCH v25 1/4] introduce CopyFormat refactor CopyFormatOptions
Currently, COPY command format is determined by two booleans (binary, csv_mode)
fields in CopyFormatOptions This approach, while functional, isn't ideal for
future other implement other format.
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 protected]>,
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 protected]
---
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 ++++++--
src/tools/pgindent/typedefs.list | 1 +
6 files changed, 49 insertions(+), 36 deletions(-)
diff --git a/src/backend/commands/copy.c b/src/backend/commands/copy.c
index 63b86802ba2..2f46be516f2 100644
--- a/src/backend/commands/copy.c
+++ b/src/backend/commands/copy.c
@@ -576,6 +576,8 @@ ProcessCopyOptions(ParseState *pstate,
opts_out = palloc0_object(CopyFormatOptions);
opts_out->file_encoding = -1;
+ /* default format */
+ opts_out->format = COPY_FORMAT_TEXT;
/* Extract options from the statement node tree */
foreach(option, options)
@@ -590,11 +592,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),
@@ -754,31 +756,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 = "\"";
@@ -826,7 +828,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,
@@ -834,43 +836,43 @@ ProcessCopyOptions(ParseState *pstate,
errmsg("COPY delimiter cannot be \"%s\"", opts_out->delim)));
/* 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 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 */
@@ -884,8 +886,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 */
@@ -900,8 +902,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 */
@@ -925,7 +927,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),
@@ -961,7 +963,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),
@@ -978,7 +980,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 2f42f55e229..4d927410159 100644
--- a/src/backend/commands/copyfrom.c
+++ b/src/backend/commands/copyfrom.c
@@ -156,9 +156,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 */
@@ -262,7 +262,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 fbd13353efc..c366874bd95 100644
--- a/src/backend/commands/copyfromparse.c
+++ b/src/backend/commands/copyfromparse.c
@@ -172,7 +172,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);
@@ -750,7 +750,7 @@ bool
NextCopyFromRawFields(CopyFromState cstate, char ***fields, int *nfields)
{
return NextCopyFromRawFieldsInternal(cstate, fields, nfields,
- cstate->opts.csv_mode);
+ cstate->opts.format == COPY_FORMAT_CSV);
}
/*
@@ -777,7 +777,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);
/* on input check that the header line is correct if needed */
if (cstate->cur_lineno == 0 && cstate->opts.header_line != COPY_HEADER_FALSE)
diff --git a/src/backend/commands/copyto.c b/src/backend/commands/copyto.c
index 9ceeff6d99e..0325a16f82a 100644
--- a/src/backend/commands/copyto.c
+++ b/src/backend/commands/copyto.c
@@ -181,9 +181,9 @@ 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;
/* default is text */
@@ -220,7 +220,7 @@ CopyToTextLikeStart(CopyToState cstate, TupleDesc tupDesc)
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);
@@ -397,7 +397,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);
diff --git a/src/include/commands/copy.h b/src/include/commands/copy.h
index 877202af67b..2430fb0b2e5 100644
--- a/src/include/commands/copy.h
+++ b/src/include/commands/copy.h
@@ -49,6 +49,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
@@ -59,9 +69,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? */
int header_line; /* number of lines to skip or COPY_HEADER_XXX
* value (see the above) */
char *null_print; /* NULL marker string (server encoding!) */
diff --git a/src/tools/pgindent/typedefs.list b/src/tools/pgindent/typedefs.list
index 77e3c04144e..8399be97fd5 100644
--- a/src/tools/pgindent/typedefs.list
+++ b/src/tools/pgindent/typedefs.list
@@ -528,6 +528,7 @@ ConversionLocation
ConvertRowtypeExpr
CookedConstraint
CopyDest
+CopyFormat
CopyFormatOptions
CopyFromRoutine
CopyFromState
--
2.43.0
From 285be0d7935b5505b6cc1509742f3fc87e19ea09 Mon Sep 17 00:00:00 2001
From: jian he <[email protected]>
Date: Mon, 9 Feb 2026 11:00:06 +0800
Subject: [PATCH v25 2/4] json format for COPY TO
---
doc/src/sgml/ref/copy.sgml | 13 ++--
src/backend/commands/copy.c | 49 ++++++++++----
src/backend/commands/copyto.c | 101 ++++++++++++++++++++++++++---
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 | 78 ++++++++++++++++++++++
src/test/regress/sql/copy.sql | 48 ++++++++++++++
10 files changed, 278 insertions(+), 29 deletions(-)
diff --git a/doc/src/sgml/ref/copy.sgml b/doc/src/sgml/ref/copy.sgml
index 0ad890ef95f..75f55bbf6f8 100644
--- a/doc/src/sgml/ref/copy.sgml
+++ b/doc/src/sgml/ref/copy.sgml
@@ -228,10 +228,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>
@@ -266,7 +271,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>
@@ -280,7 +285,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>
@@ -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 2f46be516f2..29c121c7f08 100644
--- a/src/backend/commands/copy.c
+++ b/src/backend/commands/copy.c
@@ -597,6 +597,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),
@@ -756,21 +758,32 @@ ProcessCopyOptions(ParseState *pstate,
* Check for incompatible options (must do these three before inserting
* defaults)
*/
- if (opts_out->format == COPY_FORMAT_BINARY && opts_out->delim)
+ if (opts_out->delim &&
+ (opts_out->format == COPY_FORMAT_BINARY ||
+ opts_out->format == COPY_FORMAT_JSON))
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")));
+ errcode(ERRCODE_SYNTAX_ERROR),
+ opts_out->format == COPY_FORMAT_BINARY
+ ? errmsg("cannot specify %s in BINARY mode", "DELIMITER")
+ : errmsg("cannot specify %s in JSON mode", "DELIMITER"));
- if (opts_out->format == COPY_FORMAT_BINARY && opts_out->null_print)
+ if (opts_out->null_print &&
+ (opts_out->format == COPY_FORMAT_BINARY ||
+ opts_out->format == COPY_FORMAT_JSON))
ereport(ERROR,
- (errcode(ERRCODE_SYNTAX_ERROR),
- errmsg("cannot specify %s in BINARY mode", "NULL")));
+ errcode(ERRCODE_SYNTAX_ERROR),
+ opts_out->format == COPY_FORMAT_BINARY
+ ? errmsg("cannot specify %s in BINARY mode", "NULL")
+ : errmsg("cannot specify %s in JSON mode", "NULL"));
- if (opts_out->format == COPY_FORMAT_BINARY && opts_out->default_print)
+ if (opts_out->default_print &&
+ (opts_out->format == COPY_FORMAT_BINARY ||
+ opts_out->format == COPY_FORMAT_JSON))
ereport(ERROR,
- (errcode(ERRCODE_SYNTAX_ERROR),
- errmsg("cannot specify %s in BINARY mode", "DEFAULT")));
+ errcode(ERRCODE_SYNTAX_ERROR),
+ opts_out->format == COPY_FORMAT_BINARY
+ ? errmsg("cannot specify %s in BINARY mode", "DEFAULT")
+ : errmsg("cannot specify %s in JSON mode", "DEFAULT"));
/* Set defaults for omitted options */
if (!opts_out->delim)
@@ -836,11 +849,15 @@ 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)
+ if (opts_out->header_line != COPY_HEADER_FALSE &&
+ (opts_out->format == COPY_FORMAT_BINARY ||
+ opts_out->format == COPY_FORMAT_JSON))
ereport(ERROR,
- (errcode(ERRCODE_FEATURE_NOT_SUPPORTED),
+ 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")));
+ opts_out->format == COPY_FORMAT_BINARY
+ ? errmsg("cannot specify %s in BINARY mode", "HEADER")
+ : errmsg("cannot specify %s in JSON mode", "HEADER"));
/* Check quote */
if (opts_out->format != COPY_FORMAT_CSV && opts_out->quote != NULL)
@@ -944,6 +961,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 %s mode cannot be used with %s", "JSON", "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 0325a16f82a..96605079eeb 100644
--- a/src/backend/commands/copyto.c
+++ b/src/backend/commands/copyto.c
@@ -26,6 +26,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"
@@ -33,6 +34,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"
@@ -84,6 +86,11 @@ typedef struct CopyToStateData
List *attnumlist; /* integer list of attnums to copy */
char *filename; /* filename, or NULL for STDOUT */
bool is_program; /* is 'filename' a program to popen? */
+
+ /* JSON format state */
+ bool json_tupledesc_ready; /* TupleDesc setup done for JSON */
+ StringInfoData json_buf; /* reusable buffer for JSON output */
+
copy_data_dest_cb data_dest_cb; /* function for writing data */
CopyFormatOptions opts;
@@ -130,6 +137,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);
@@ -149,7 +157,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, text and json formats share the same TextLike routines except for the
* one-row callback.
*/
@@ -169,6 +177,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,
@@ -185,12 +201,14 @@ CopyToGetRoutine(const CopyFormatOptions *opts)
return &CopyToRoutineCSV;
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)
{
@@ -209,6 +227,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);
@@ -228,10 +248,21 @@ CopyToTextLikeStart(CopyToState cstate, TupleDesc tupDesc)
CopySendTextLikeEndOfRow(cstate);
}
+
+ /* JSON-specific initialization */
+ if (cstate->opts.format == COPY_FORMAT_JSON)
+ {
+ MemoryContext oldcxt;
+
+ /* Allocate reusable JSON output buffer in long-lived context */
+ oldcxt = MemoryContextSwitchTo(cstate->copycontext);
+ initStringInfo(&cstate->json_buf);
+ MemoryContextSwitchTo(oldcxt);
+ }
}
/*
- * 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
@@ -304,13 +335,47 @@ 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;
+
+ /*
+ * For query-based COPY, copy the query's TupleDesc attributes into the
+ * slot's TupleDesc once. BlessTupleDesc registers the RECORDOID
+ * descriptor so that lookup_rowtype_tupdesc inside composite_to_json can
+ * find it.
+ */
+ if (!cstate->rel && !cstate->json_tupledesc_ready)
+ {
+ 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);
+ cstate->json_tupledesc_ready = true;
+ }
+
+ rowdata = ExecFetchSlotHeapTupleDatum(slot);
+ resetStringInfo(&cstate->json_buf);
+ composite_to_json(rowdata, &cstate->json_buf, false);
+
+ CopySendData(cstate, cstate->json_buf.data, cstate->json_buf.len);
+
+ CopySendTextLikeEndOfRow(cstate);
+}
+
/*
* Implementation of the start callback for binary format. Send a header
* for a binary copy.
@@ -402,9 +467,23 @@ 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
+ {
+ /*
+ * For JSON format, report one text-format column. Each CopyData
+ * message contains one complete JSON object, not individual column
+ * values, so the per-column count is always 1.
+ */
+ pq_sendint16(&buf, 1);
+ pq_sendint16(&buf, 0);
+ }
+
pq_endmessage(&buf);
cstate->copy_dest = COPY_FRONTEND;
}
@@ -506,7 +585,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
@@ -890,6 +969,12 @@ BeginCopyTo(ParseState *pstate,
/* Generate or convert list of attributes to process */
cstate->attnumlist = CopyGetAttnums(tupDesc, cstate->rel, attnamelist);
+ /* JSON outputs whole rows; a column list doesn't make sense */
+ if (cstate->opts.format == COPY_FORMAT_JSON && attnamelist != NIL)
+ ereport(ERROR,
+ (errcode(ERRCODE_FEATURE_NOT_SUPPORTED),
+ errmsg("column selection is not supported in JSON mode")));
+
num_phys_attrs = tupDesc->natts;
/* Convert FORCE_QUOTE name list to per-column flags, check validity */
diff --git a/src/backend/parser/gram.y b/src/backend/parser/gram.y
index c567252acc4..db98f2d91bf 100644
--- a/src/backend/parser/gram.y
+++ b/src/backend/parser/gram.y
@@ -3609,6 +3609,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);
@@ -3691,6 +3695,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 0b161398465..f609d7b9417 100644
--- a/src/backend/utils/adt/json.c
+++ b/src/backend/utils/adt/json.c
@@ -86,8 +86,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,
const Datum *vals, const bool *nulls, int *valcount,
JsonTypeCategory tcategory, Oid outfuncoid,
@@ -517,8 +515,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 905c076763c..d257837b0c5 100644
--- a/src/bin/psql/tab-complete.in.c
+++ b/src/bin/psql/tab-complete.in.c
@@ -3425,7 +3425,7 @@ match_previous_words(int pattern_id,
/* Complete COPY <sth> FROM|TO filename WITH (FORMAT */
else if (TailMatches("FORMAT"))
- COMPLETE_WITH("binary", "csv", "text");
+ COMPLETE_WITH("binary", "csv", "text", "json");
/* Complete COPY <sth> FROM|TO filename WITH (FREEZE */
else if (TailMatches("FREEZE"))
diff --git a/src/include/commands/copy.h b/src/include/commands/copy.h
index 2430fb0b2e5..2b5bef6738e 100644
--- a/src/include/commands/copy.h
+++ b/src/include/commands/copy.h
@@ -57,6 +57,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 f8cc52b1e78..2f4be40518d 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 d0d563e0fa8..4ea658a45de 100644
--- a/src/test/regress/expected/copy.out
+++ b/src/test/regress/expected/copy.out
@@ -73,6 +73,84 @@ 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, header 1);
+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
+copy copytest (style) to stdout (format json);
+ERROR: column selection is not supported in JSON mode
+-- 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 65cbdaf7f3e..c558eba202a 100644
--- a/src/test/regress/sql/copy.sql
+++ b/src/test/regress/sql/copy.sql
@@ -82,6 +82,54 @@ 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, header 1);
+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);
+copy copytest (style) to stdout (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.43.0
From 312dedc0a3aea1833bb992f096121db4086fd075 Mon Sep 17 00:00:00 2001
From: jian he <[email protected]>
Date: Mon, 9 Feb 2026 11:14:15 +0800
Subject: [PATCH v25 3/4] Add option force_array for COPY JSON FORMAT
---
doc/src/sgml/ref/copy.sgml | 30 +++++++++++++++++++++
src/backend/commands/copy.c | 13 +++++++++
src/backend/commands/copyto.c | 43 +++++++++++++++++++++++++++---
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, 115 insertions(+), 5 deletions(-)
diff --git a/doc/src/sgml/ref/copy.sgml b/doc/src/sgml/ref/copy.sgml
index 75f55bbf6f8..a79587f7613 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 id="sql-copy-params-force-array">
+ <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 id="sql-copy-params-force-quote">
<term><literal>FORCE_QUOTE</literal></term>
<listitem>
@@ -1103,6 +1117,22 @@ COPY country TO STDOUT (DELIMITER '|');
</programlisting>
</para>
+<para>
+ When the <literal>FORCE_ARRAY</literal> option is enabled,
+ the entire output is wrapped in a single JSON array with rows separated by commas:
+<programlisting>
+COPY (SELECT * FROM (VALUES(1),(2)) val(id)) TO STDOUT (FORMAT JSON, FORCE_ARRAY);
+</programlisting>
+The output is as follows:
+<screen>
+[
+ {"id":1}
+,{"id":2}
+]
+</screen>
+</para>
+
+
<para>
To copy data from a file into the <literal>country</literal> table:
<programlisting>
diff --git a/src/backend/commands/copy.c b/src/backend/commands/copy.c
index 29c121c7f08..84254d46a67 100644
--- a/src/backend/commands/copy.c
+++ b/src/backend/commands/copy.c
@@ -569,6 +569,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 */
@@ -725,6 +726,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)
@@ -967,6 +975,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 be 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 96605079eeb..a7615cc34ec 100644
--- a/src/backend/commands/copyto.c
+++ b/src/backend/commands/copyto.c
@@ -88,6 +88,7 @@ typedef struct CopyToStateData
bool is_program; /* is 'filename' a program to popen? */
/* JSON format state */
+ bool json_row_delim_needed; /* need delimiter before next row */
bool json_tupledesc_ready; /* TupleDesc setup done for JSON */
StringInfoData json_buf; /* reusable buffer for JSON output */
@@ -138,6 +139,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);
@@ -157,8 +159,9 @@ static void CopySendInt16(CopyToState cstate, int16 val);
/*
* COPY TO routines for built-in formats.
*
- * CSV, text and json formats share the same TextLike routines except for the
- * one-row callback.
+ * Text and CSV formats share the same TextLike routines except for the
+ * one-row callback. JSON shares the start and outfunc callbacks with
+ * text/CSV, but has its own one-row and end callbacks.
*/
/* text format */
@@ -182,7 +185,7 @@ static const CopyToRoutine CopyToRoutineJson = {
.CopyToStart = CopyToTextLikeStart,
.CopyToOutFunc = CopyToTextLikeOutFunc,
.CopyToOneRow = CopyToJsonOneRow,
- .CopyToEnd = CopyToTextLikeEnd,
+ .CopyToEnd = CopyToJsonEnd,
};
/* binary format */
@@ -258,6 +261,15 @@ CopyToTextLikeStart(CopyToState cstate, TupleDesc tupDesc)
oldcxt = MemoryContextSwitchTo(cstate->copycontext);
initStringInfo(&cstate->json_buf);
MemoryContextSwitchTo(oldcxt);
+
+ /*
+ * If FORCE_ARRAY has been specified, send the opening bracket.
+ */
+ if (cstate->opts.force_array)
+ {
+ CopySendChar(cstate, '[');
+ CopySendTextLikeEndOfRow(cstate);
+ }
}
}
@@ -335,7 +347,7 @@ CopyToTextLikeOneRow(CopyToState cstate,
CopySendTextLikeEndOfRow(cstate);
}
-/* Implementation of the end callback for text, CSV, and json formats */
+/* Implementation of the end callback for text and CSV formats */
static void
CopyToTextLikeEnd(CopyToState cstate)
{
@@ -371,11 +383,34 @@ CopyToJsonOneRow(CopyToState cstate, TupleTableSlot *slot)
resetStringInfo(&cstate->json_buf);
composite_to_json(rowdata, &cstate->json_buf, false);
+ if (cstate->opts.force_array)
+ {
+ if (cstate->json_row_delim_needed)
+ CopySendChar(cstate, ',');
+ else
+ {
+ /* first row needs no delimiter */
+ CopySendChar(cstate, ' ');
+ cstate->json_row_delim_needed = true;
+ }
+ }
+
CopySendData(cstate, cstate->json_buf.data, cstate->json_buf.len);
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 d257837b0c5..76d3258e92b 100644
--- a/src/bin/psql/tab-complete.in.c
+++ b/src/bin/psql/tab-complete.in.c
@@ -1232,7 +1232,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
diff --git a/src/include/commands/copy.h b/src/include/commands/copy.h
index 2b5bef6738e..abecfe51098 100644
--- a/src/include/commands/copy.h
+++ b/src/include/commands/copy.h
@@ -88,6 +88,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 */
+ bool force_array; /* add JSON array decorations */
List *force_null; /* list of column names */
bool force_null_all; /* FORCE_NULL *? */
bool *force_null_flags; /* per-column CSV FN flags */
diff --git a/src/test/regress/expected/copy.out b/src/test/regress/expected/copy.out
index 4ea658a45de..a7e88b711d7 100644
--- a/src/test/regress/expected/copy.out
+++ b/src/test/regress/expected/copy.out
@@ -114,6 +114,29 @@ ERROR: COPY JSON mode cannot be used with COPY FROM
copy copytest (style) to stdout (format json);
ERROR: column selection is not supported in JSON mode
-- all of the above should yield error
+-- should fail: force_array requires json format
+copy copytest to stdout (format csv, force_array true);
+ERROR: COPY FORCE_ARRAY can only be used with JSON mode
+-- force_array variants
+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 c558eba202a..ae202fc5e8d 100644
--- a/src/test/regress/sql/copy.sql
+++ b/src/test/regress/sql/copy.sql
@@ -102,6 +102,14 @@ copy copytest from stdin(format json);
copy copytest (style) to stdout (format json);
-- all of the above should yield error
+-- should fail: force_array requires json format
+copy copytest to stdout (format csv, force_array true);
+
+-- force_array variants
+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.43.0
From d043adcbb2032eb2c1218df99992008838f2e200 Mon Sep 17 00:00:00 2001
From: Andrew Dunstan <[email protected]>
Date: Wed, 4 Mar 2026 09:40:10 -0500
Subject: [PATCH v25 4/4] COPY TO JSON: build JSON per-column, support column
lists
Rework CopyToJsonOneRow to iterate attnumlist and build JSON objects
column-by-column using datum_to_json_append, instead of converting the
whole row via ExecFetchSlotHeapTupleDatum + composite_to_json.
This has several benefits:
- Column lists now work with JSON format (previously rejected)
- Per-column JSON type categorization is done once at startup rather
than on every row (composite_to_json called json_categorize_type
per column per row)
- The TupleDesc memcpy/BlessTupleDesc hack for query-based COPY is
eliminated entirely
- Pre-escaped column key strings avoid repeated escape_json calls
Add CopyToJsonStart (pre-computes escaped key strings and json_buf),
CopyToJsonOutFunc (calls json_categorize_type once per column), and
export datum_to_json_append from json.c for efficient append-to-
StringInfo JSON serialization.
---
src/backend/commands/copyto.c | 159 ++++++++++++++++++-----------
src/backend/utils/adt/json.c | 14 +++
src/include/utils/jsonfuncs.h | 2 +
src/test/regress/expected/copy.out | 8 +-
src/test/regress/sql/copy.sql | 4 +-
5 files changed, 126 insertions(+), 61 deletions(-)
diff --git a/src/backend/commands/copyto.c b/src/backend/commands/copyto.c
index a7615cc34ec..9502c910b43 100644
--- a/src/backend/commands/copyto.c
+++ b/src/backend/commands/copyto.c
@@ -26,7 +26,6 @@
#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"
@@ -35,6 +34,7 @@
#include "storage/fd.h"
#include "tcop/tcopprot.h"
#include "utils/json.h"
+#include "utils/jsonfuncs.h"
#include "utils/lsyscache.h"
#include "utils/memutils.h"
#include "utils/rel.h"
@@ -89,8 +89,10 @@ typedef struct CopyToStateData
/* JSON format state */
bool json_row_delim_needed; /* need delimiter before next row */
- bool json_tupledesc_ready; /* TupleDesc setup done for JSON */
StringInfoData json_buf; /* reusable buffer for JSON output */
+ JsonTypeCategory *json_categories; /* per-column JSON type categories */
+ Oid *json_outfuncoids; /* per-column JSON output func OIDs */
+ char **json_col_keys; /* per-column pre-escaped "key": strings */
copy_data_dest_cb data_dest_cb; /* function for writing data */
@@ -138,6 +140,8 @@ static void CopyToCSVOneRow(CopyToState cstate, TupleTableSlot *slot);
static void CopyToTextLikeOneRow(CopyToState cstate, TupleTableSlot *slot,
bool is_csv);
static void CopyToTextLikeEnd(CopyToState cstate);
+static void CopyToJsonStart(CopyToState cstate, TupleDesc tupDesc);
+static void CopyToJsonOutFunc(CopyToState cstate, Oid atttypid, FmgrInfo *finfo);
static void CopyToJsonOneRow(CopyToState cstate, TupleTableSlot *slot);
static void CopyToJsonEnd(CopyToState cstate);
static void CopyToBinaryStart(CopyToState cstate, TupleDesc tupDesc);
@@ -160,8 +164,8 @@ static void CopySendInt16(CopyToState cstate, int16 val);
* COPY TO routines for built-in formats.
*
* Text and CSV formats share the same TextLike routines except for the
- * one-row callback. JSON shares the start and outfunc callbacks with
- * text/CSV, but has its own one-row and end callbacks.
+ * one-row callback. JSON has its own start, outfunc, one-row, and end
+ * callbacks.
*/
/* text format */
@@ -182,8 +186,8 @@ static const CopyToRoutine CopyToRoutineCSV = {
/* json format */
static const CopyToRoutine CopyToRoutineJson = {
- .CopyToStart = CopyToTextLikeStart,
- .CopyToOutFunc = CopyToTextLikeOutFunc,
+ .CopyToStart = CopyToJsonStart,
+ .CopyToOutFunc = CopyToJsonOutFunc,
.CopyToOneRow = CopyToJsonOneRow,
.CopyToEnd = CopyToJsonEnd,
};
@@ -211,7 +215,7 @@ CopyToGetRoutine(const CopyFormatOptions *opts)
return &CopyToRoutineText;
}
-/* Implementation of the start callback for text, CSV, and json formats */
+/* Implementation of the start callback for text and CSV formats */
static void
CopyToTextLikeStart(CopyToState cstate, TupleDesc tupDesc)
{
@@ -230,8 +234,6 @@ 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);
@@ -251,30 +253,10 @@ CopyToTextLikeStart(CopyToState cstate, TupleDesc tupDesc)
CopySendTextLikeEndOfRow(cstate);
}
-
- /* JSON-specific initialization */
- if (cstate->opts.format == COPY_FORMAT_JSON)
- {
- MemoryContext oldcxt;
-
- /* Allocate reusable JSON output buffer in long-lived context */
- oldcxt = MemoryContextSwitchTo(cstate->copycontext);
- initStringInfo(&cstate->json_buf);
- MemoryContextSwitchTo(oldcxt);
-
- /*
- * If FORCE_ARRAY has been specified, send the opening bracket.
- */
- if (cstate->opts.force_array)
- {
- CopySendChar(cstate, '[');
- CopySendTextLikeEndOfRow(cstate);
- }
- }
}
/*
- * Implementation of the outfunc callback for text, CSV, and json formats. Assign
+ * Implementation of the outfunc callback for text and CSV formats. Assign
* the output function data to the given *finfo.
*/
static void
@@ -354,34 +336,95 @@ CopyToTextLikeEnd(CopyToState cstate)
/* Nothing to do here */
}
+/*
+ * Implementation of the start callback for json format.
+ *
+ * Pre-compute the escaped JSON key strings ('"colname":') for each selected
+ * column so CopyToJsonOneRow only needs to copy them per row.
+ */
+static void
+CopyToJsonStart(CopyToState cstate, TupleDesc tupDesc)
+{
+ MemoryContext oldcxt;
+ StringInfoData keybuf;
+
+ oldcxt = MemoryContextSwitchTo(cstate->copycontext);
+
+ /* Allocate reusable JSON output buffer */
+ initStringInfo(&cstate->json_buf);
+
+ /* Pre-build escaped key strings: "\"colname\":" */
+ cstate->json_col_keys = palloc0(tupDesc->natts * sizeof(char *));
+ initStringInfo(&keybuf);
+ foreach_int(attnum, cstate->attnumlist)
+ {
+ char *colname;
+
+ colname = NameStr(TupleDescAttr(tupDesc, attnum - 1)->attname);
+
+ resetStringInfo(&keybuf);
+ escape_json(&keybuf, colname);
+ appendStringInfoChar(&keybuf, ':');
+
+ cstate->json_col_keys[attnum - 1] = pstrdup(keybuf.data);
+ }
+ pfree(keybuf.data);
+
+ MemoryContextSwitchTo(oldcxt);
+
+ /* If FORCE_ARRAY, send the opening bracket */
+ if (cstate->opts.force_array)
+ {
+ CopySendChar(cstate, '[');
+ CopySendTextLikeEndOfRow(cstate);
+ }
+}
+
+/*
+ * Implementation of the outfunc callback for json format.
+ *
+ * Instead of text output functions, we categorize each column's type for
+ * JSON serialization once so CopyToJsonOneRow can use datum_to_json_append
+ * directly.
+ */
+static void
+CopyToJsonOutFunc(CopyToState cstate, Oid atttypid, FmgrInfo *finfo)
+{
+ int attidx = finfo - cstate->out_functions;
+
+ json_categorize_type(atttypid, false,
+ &cstate->json_categories[attidx],
+ &cstate->json_outfuncoids[attidx]);
+}
+
/* Implementation of per-row callback for json format */
static void
CopyToJsonOneRow(CopyToState cstate, TupleTableSlot *slot)
{
- Datum rowdata;
+ bool needsep = false;
- /*
- * For query-based COPY, copy the query's TupleDesc attributes into the
- * slot's TupleDesc once. BlessTupleDesc registers the RECORDOID
- * descriptor so that lookup_rowtype_tupdesc inside composite_to_json can
- * find it.
- */
- if (!cstate->rel && !cstate->json_tupledesc_ready)
- {
- 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);
- cstate->json_tupledesc_ready = true;
- }
-
- rowdata = ExecFetchSlotHeapTupleDatum(slot);
resetStringInfo(&cstate->json_buf);
- composite_to_json(rowdata, &cstate->json_buf, false);
+ appendStringInfoChar(&cstate->json_buf, '{');
+
+ foreach_int(attnum, cstate->attnumlist)
+ {
+ Datum value = slot->tts_values[attnum - 1];
+ bool isnull = slot->tts_isnull[attnum - 1];
+
+ if (needsep)
+ appendStringInfoChar(&cstate->json_buf, ',');
+ needsep = true;
+
+ /* Append pre-escaped "key": */
+ appendStringInfoString(&cstate->json_buf,
+ cstate->json_col_keys[attnum - 1]);
+
+ datum_to_json_append(value, isnull, &cstate->json_buf,
+ cstate->json_categories[attnum - 1],
+ cstate->json_outfuncoids[attnum - 1]);
+ }
+
+ appendStringInfoChar(&cstate->json_buf, '}');
if (cstate->opts.force_array)
{
@@ -1004,12 +1047,6 @@ BeginCopyTo(ParseState *pstate,
/* Generate or convert list of attributes to process */
cstate->attnumlist = CopyGetAttnums(tupDesc, cstate->rel, attnamelist);
- /* JSON outputs whole rows; a column list doesn't make sense */
- if (cstate->opts.format == COPY_FORMAT_JSON && attnamelist != NIL)
- ereport(ERROR,
- (errcode(ERRCODE_FEATURE_NOT_SUPPORTED),
- errmsg("column selection is not supported in JSON mode")));
-
num_phys_attrs = tupDesc->natts;
/* Convert FORCE_QUOTE name list to per-column flags, check validity */
@@ -1204,6 +1241,12 @@ DoCopyTo(CopyToState cstate)
/* Get info about the columns we need to process. */
cstate->out_functions = (FmgrInfo *) palloc(num_phys_attrs * sizeof(FmgrInfo));
+ if (cstate->opts.format == COPY_FORMAT_JSON)
+ {
+ /* JSON outfunc callback stores per-column type categorization here */
+ cstate->json_categories = palloc0(num_phys_attrs * sizeof(JsonTypeCategory));
+ cstate->json_outfuncoids = palloc0(num_phys_attrs * sizeof(Oid));
+ }
foreach(cur, cstate->attnumlist)
{
int attnum = lfirst_int(cur);
diff --git a/src/backend/utils/adt/json.c b/src/backend/utils/adt/json.c
index f609d7b9417..de81160a831 100644
--- a/src/backend/utils/adt/json.c
+++ b/src/backend/utils/adt/json.c
@@ -771,6 +771,20 @@ datum_to_json(Datum val, JsonTypeCategory tcategory, Oid outfuncoid)
return PointerGetDatum(cstring_to_text_with_len(result.data, result.len));
}
+/*
+ * Append JSON representation of a Datum to a StringInfo.
+ *
+ * tcategory and outfuncoid are from a previous call to json_categorize_type.
+ * If is_null is true, appends "null" regardless of tcategory/outfuncoid.
+ */
+void
+datum_to_json_append(Datum val, bool is_null, StringInfo result,
+ JsonTypeCategory tcategory, Oid outfuncoid)
+{
+ datum_to_json_internal(val, is_null, result, tcategory, outfuncoid,
+ false);
+}
+
/*
* json_agg transition function
*
diff --git a/src/include/utils/jsonfuncs.h b/src/include/utils/jsonfuncs.h
index 636f0f55840..12a01451fbb 100644
--- a/src/include/utils/jsonfuncs.h
+++ b/src/include/utils/jsonfuncs.h
@@ -85,6 +85,8 @@ extern void json_categorize_type(Oid typoid, bool is_jsonb,
JsonTypeCategory *tcategory, Oid *outfuncoid);
extern Datum datum_to_json(Datum val, JsonTypeCategory tcategory,
Oid outfuncoid);
+extern void datum_to_json_append(Datum val, bool is_null, StringInfo result,
+ JsonTypeCategory tcategory, Oid outfuncoid);
extern Datum datum_to_jsonb(Datum val, JsonTypeCategory tcategory,
Oid outfuncoid);
extern Datum jsonb_from_text(text *js, bool unique_keys);
diff --git a/src/test/regress/expected/copy.out b/src/test/regress/expected/copy.out
index a7e88b711d7..d60e5a4d32a 100644
--- a/src/test/regress/expected/copy.out
+++ b/src/test/regress/expected/copy.out
@@ -111,8 +111,6 @@ 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
-copy copytest (style) to stdout (format json);
-ERROR: column selection is not supported in JSON mode
-- all of the above should yield error
-- should fail: force_array requires json format
copy copytest to stdout (format csv, force_array true);
@@ -137,6 +135,12 @@ copy copytest to stdout (format json, force_array false);
{"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}
+-- column list with json format
+copy copytest (style, filler) to stdout (format json);
+{"style":"DOS","filler":1}
+{"style":"Unix","filler":2}
+{"style":"Mac","filler":3}
+{"style":"esc\\ape","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 ae202fc5e8d..d64f4c66b93 100644
--- a/src/test/regress/sql/copy.sql
+++ b/src/test/regress/sql/copy.sql
@@ -99,7 +99,6 @@ 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);
-copy copytest (style) to stdout (format json);
-- all of the above should yield error
-- should fail: force_array requires json format
@@ -110,6 +109,9 @@ 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);
+-- column list with json format
+copy copytest (style, filler) to stdout (format json);
+
-- embedded escaped characters
create temp table copyjsontest (
id bigserial,
--
2.43.0
