Horton

Re: Emitting JSON to file using COPY TO

jian he <jian.universality@gmail.com>

View thread View on postgr.es
From: jian he <jian.universality@gmail.com>
To: Andrew Dunstan <andrew@dunslane.net>
Cc: Joe Conway <mail@joeconway.com>, Junwang Zhao <zhjwpku@gmail.com>, Florents Tselai <florents.tselai@gmail.com>, "Andrey M. Borodin" <x4mmm@yandex-team.ru>, Dean Rasheed <dean.a.rasheed@gmail.com>, Daniel Verite <daniel@manitou-mail.org>, Davin Shearer <davin@apache.org>, PostgreSQL-development <pgsql-hackers@postgresql.org>
Date: 2026-03-06T09:38:30Z
Lists: pgsql-hackers, pgsql-general

Attachments

COPY (SELECT 1 UNION ALL SELECT 2) TO stdout WITH (format json);

still fails with v25-0002, json_tupledesc_ready is not helpful.
I think I figured it out. We need to use BlessTupleDesc in BeginCopyTo.
Then let slot->tts_tupleDescriptor point to cstate->queryDesc->tupDesc
in CopyToJsonOneRow

 * CSV, text and json formats share the same TextLike routines except for the
 * one-row callback.
This comment is not useful, I want to delete it.

CopyToTextLikeStart
+ /* 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);
+ }
We ca just add

cstate->json_buf = makeStringInfo();

in BeginCopyTo.

v25-0004-COPY-TO-JSON-build-JSON-per-column-support-colum.patch
added several fields to the CopyToStateData.
Actually, there is a simpler way (construct a new Tupdesc and let
composite_to_json do the job), please see my v26-0004.



--
jian
https://www.enterprisedb.com/

Commits

  1. Add option force_array for COPY JSON FORMAT

  2. json format for COPY TO

  3. introduce CopyFormat, refactor CopyFormatOptions

  4. Doc: add IDs to copy.sgml's <varlistentry> and <refsect1>

  5. Refactor COPY TO to use format callback functions.