Copy from JSON FORMAT.
Hi hackers,
7dadd38cda introduced support for COPY TO in JSON format, but did
not include the corresponding COPY FROM functionality. The original
discussion [1]postgresql.org/message-id/flat/20231201230958.GA1786735%40nathanxps13 mentioned the possibility of supporting COPY FROM as
well, but that part of the patch never happened (If I don't miss).
For a data format like JSON, basic round-trip capability seems essential:
if data can be exported via COPY TO, it should also be possible to import
it back using COPY FROM. So I try to close that gap.
0001
A small, unrelated cleanup that replaces several usages with
TupleDescCompactAttr where appropriate. This was discovered
opportunistically while working on the JSON support.
0002
Adds support for COPY FROM with FORMAT json.
The core logic is outlined below.
Bytes are read into raw_buf and optionally transcoded into
input_buf. Instead of CopyReadLine, JSON mode uses CopyReadNextJson
to fetch the next row object via a small state machine:
- BEFORE_ARRAY: expect '[' or '{'
- BEFORE_OBJECT: in concat mode, expect next object or EOF
- IN_ARRAY: expect object, comma, or ']'
- IN_OBJECT: track brace depth to find the matching '}'
- IN_STRING / IN_STRING_ESC: skip braces inside strings
- ARRAY_END: after ']', no more rows
When a row object closes, copy_json_finalize_linebuf_for_row
reshapes line_buf to [row text][unparsed tail] and sets parse_pos
to the row length so jsonb_in sees only the current row.
NextCopyFromJsonRawFieldsInternal calls jsonb_in on that slice,
verifies a JSON object at the root, then looks up each target
column by name. Values are converted to C strings via
JsonbValueToCstring and stored in attribute_buf, following the
same pattern as text/CSV.
CopyFromJsonOneRow then runs the standard per-column input
functions, so type coercion matches ordinary textual input and
the existing COPY machinery for defaults and soft errors applies
unchanged.
[1]: postgresql.org/message-id/flat/20231201230958.GA1786735%40nathanxps13
--
Regards
Junwang Zhao
Attachments:
v1-0002-Support-COPY-FROM-with-FORMAT-JSON.patchapplication/octet-stream; name=v1-0002-Support-COPY-FROM-with-FORMAT-JSON.patchDownload+1031-16
v1-0001-use-TupleDescCompactAttr-where-possible.patchapplication/octet-stream; name=v1-0001-use-TupleDescCompactAttr-where-possible.patchDownload+4-5
On Sun, Mar 22, 2026 at 4:59 PM Junwang Zhao <zhjwpku@gmail.com> wrote:
Hi hackers,
7dadd38cda introduced support for COPY TO in JSON format, but did
not include the corresponding COPY FROM functionality. The original
discussion [1] mentioned the possibility of supporting COPY FROM as
well, but that part of the patch never happened (If I don't miss).For a data format like JSON, basic round-trip capability seems essential:
if data can be exported via COPY TO, it should also be possible to import
it back using COPY FROM. So I try to close that gap.
Hi.
repalloc can be replaced by repalloc_array.
maybe palloc, palloc0 can aslo be replaced by palloc_object/palloc0_object.
``foreach(cur, cstate->attnumlist)``
can be replaced by
``foreach_int(cur, cstate->attnumlist)``
+ ereport(ERROR,
+ (errcode(ERRCODE_BAD_COPY_FILE_FORMAT),
+ errmsg("invalid input format for COPY JSON"),
+ errdetail("Document must begin with \"[\" or \"{\".")));
+ pg_unreachable();
The parentheses in `` (errcode`` is not necessary, (and other palces)
See https://www.postgresql.org/message-id/202510100916.s2e6n3xiwvyc%40alvherre.pgsqlShould the JSON object match the table column?
create table copytest_from_json(style int, test text);
copy copytest_from_json (style, test) from stdin (format json);
The below 3 valid json should all fail?
{"style": "1", "test": 1, "x": 1}
{"style": "1"}
{"x": "1"}
I am not sure the handling of empty strings is correct.
src9=# copy copytest_from_json (style, test) from stdin (format json);
Enter data to be copied followed by a newline.
End with a backslash and a period on a line by itself, or an EOF signal.
\.
COPY 0
src9=# copy copytest_from_json (style, test) from stdin (format text);
Enter data to be copied followed by a newline.
End with a backslash and a period on a line by itself, or an EOF signal.
\.
ERROR: invalid input syntax for type integer: ""
CONTEXT: COPY copytest_from_json, line 1, column style: ""
Hi Jian,
On Sun, Mar 22, 2026 at 8:59 PM jian he <jian.universality@gmail.com> wrote:
On Sun, Mar 22, 2026 at 4:59 PM Junwang Zhao <zhjwpku@gmail.com> wrote:
Hi hackers,
7dadd38cda introduced support for COPY TO in JSON format, but did
not include the corresponding COPY FROM functionality. The original
discussion [1] mentioned the possibility of supporting COPY FROM as
well, but that part of the patch never happened (If I don't miss).For a data format like JSON, basic round-trip capability seems essential:
if data can be exported via COPY TO, it should also be possible to import
it back using COPY FROM. So I try to close that gap.Hi.
repalloc can be replaced by repalloc_array.
maybe palloc, palloc0 can aslo be replaced by palloc_object/palloc0_object.
I changed some of them, some are not changed because other similar patterns
use this pattern, I'd like to keep the consistency.
``foreach(cur, cstate->attnumlist)``
can be replaced by
``foreach_int(cur, cstate->attnumlist)``
Changed, since this can save a line of define, I also applied the same
change to other instances in this file.
+ ereport(ERROR, + (errcode(ERRCODE_BAD_COPY_FILE_FORMAT), + errmsg("invalid input format for COPY JSON"), + errdetail("Document must begin with \"[\" or \"{\"."))); + pg_unreachable(); The parentheses in `` (errcode`` is not necessary, (and other palces) See https://www.postgresql.org/message-id/202510100916.s2e6n3xiwvyc%40alvherre.pgsql
Changed. There are quite some `` (errcode`` usages in this file, I
don't touch them to introduce more code churn.
Should the JSON object match the table column?
create table copytest_from_json(style int, test text);
copy copytest_from_json (style, test) from stdin (format json);The below 3 valid json should all fail?
{"style": "1", "test": 1, "x": 1}
{"style": "1"}
{"x": "1"}
I think json format should be more flexible, as long as it is a valid json
string representation. Extra fields and missing fields should be accepted.
But I'd like to know what others' thought on this.
I am not sure the handling of empty strings is correct.
src9=# copy copytest_from_json (style, test) from stdin (format json);
Enter data to be copied followed by a newline.
End with a backslash and a period on a line by itself, or an EOF signal.\.
COPY 0
src9=# copy copytest_from_json (style, test) from stdin (format text);
Enter data to be copied followed by a newline.
End with a backslash and a period on a line by itself, or an EOF signal.\.
ERROR: invalid input syntax for type integer: ""
CONTEXT: COPY copytest_from_json, line 1, column style: ""
I'm not entirely sure about this either, it’s a corner case. I also noticed
that the current implementation accepts [] as valid input, and the JSON
output for an empty table is also []. From a round-trip perspective,
the current behavior seems reasonable.
Attach is the v2 resolved part of your comments, thanks for looking.
--
Regards
Junwang Zhao
Attachments:
t53559_3v2-0001-use-TupleDescCompactAttr-where-possible.patchapplication/octet-stream; name=v2-0001-use-TupleDescCompactAttr-where-possible.patchDownload+4-5
v2-0002-Support-COPY-FROM-with-FORMAT-JSON.patchapplication/octet-stream; name=v2-0002-Support-COPY-FROM-with-FORMAT-JSON.patchDownload+1030-25
On Sun, Mar 22, 2026 at 10:16 PM Junwang Zhao <zhjwpku@gmail.com> wrote:
Hi Jian,
On Sun, Mar 22, 2026 at 8:59 PM jian he <jian.universality@gmail.com> wrote:
On Sun, Mar 22, 2026 at 4:59 PM Junwang Zhao <zhjwpku@gmail.com> wrote:
Hi hackers,
7dadd38cda introduced support for COPY TO in JSON format, but did
not include the corresponding COPY FROM functionality. The original
discussion [1] mentioned the possibility of supporting COPY FROM as
well, but that part of the patch never happened (If I don't miss).For a data format like JSON, basic round-trip capability seems essential:
if data can be exported via COPY TO, it should also be possible to import
it back using COPY FROM. So I try to close that gap.Hi.
repalloc can be replaced by repalloc_array.
maybe palloc, palloc0 can aslo be replaced by palloc_object/palloc0_object.I changed some of them, some are not changed because other similar patterns
use this pattern, I'd like to keep the consistency.``foreach(cur, cstate->attnumlist)``
can be replaced by
``foreach_int(cur, cstate->attnumlist)``Changed, since this can save a line of define, I also applied the same
change to other instances in this file.+ ereport(ERROR, + (errcode(ERRCODE_BAD_COPY_FILE_FORMAT), + errmsg("invalid input format for COPY JSON"), + errdetail("Document must begin with \"[\" or \"{\"."))); + pg_unreachable(); The parentheses in `` (errcode`` is not necessary, (and other palces) See https://www.postgresql.org/message-id/202510100916.s2e6n3xiwvyc%40alvherre.pgsqlChanged. There are quite some `` (errcode`` usages in this file, I
don't touch them to introduce more code churn.Should the JSON object match the table column?
create table copytest_from_json(style int, test text);
copy copytest_from_json (style, test) from stdin (format json);The below 3 valid json should all fail?
{"style": "1", "test": 1, "x": 1}
{"style": "1"}
{"x": "1"}I think json format should be more flexible, as long as it is a valid json
string representation. Extra fields and missing fields should be accepted.But I'd like to know what others' thought on this.
I am not sure the handling of empty strings is correct.
src9=# copy copytest_from_json (style, test) from stdin (format json);
Enter data to be copied followed by a newline.
End with a backslash and a period on a line by itself, or an EOF signal.\.
COPY 0
src9=# copy copytest_from_json (style, test) from stdin (format text);
Enter data to be copied followed by a newline.
End with a backslash and a period on a line by itself, or an EOF signal.\.
ERROR: invalid input syntax for type integer: ""
CONTEXT: COPY copytest_from_json, line 1, column style: ""I'm not entirely sure about this either, it’s a corner case. I also noticed
that the current implementation accepts [] as valid input, and the JSON
output for an empty table is also []. From a round-trip perspective,
the current behavior seems reasonable.Attach is the v2 resolved part of your comments, thanks for looking.
--
Regards
Junwang Zhao
Here is v3, with some improvement:
1. Stricter JSON array scanning, adds an explicit array scan state
machine, so commas, array endings, missing separators, trailing
commas, and data after the top-level array are all treated as errors
now.
2. Better EOF handling, it now detects EOF while inside an unfinished
object or string and reports unexpected end of input in COPY JSON,
instead of potentially waiting indefinitely or producing unclear
behavior.
3. Correct json/jsonb scalar round-trip, for target columns of type
json or jsonb, scalar JSON values are preserved as valid JSON text,
e.g. "foo" stays "foo" and true stays true, instead of being converted
to SQL-style text such as foo or t.
4. More accurate error line numbers, which was pointed out by Jian off-list.
--
Regards
Junwang Zhao
On Sun, Jun 7, 2026 at 11:13 PM Junwang Zhao <zhjwpku@gmail.com> wrote:
On Sun, Mar 22, 2026 at 10:16 PM Junwang Zhao <zhjwpku@gmail.com> wrote:
Hi Jian,
On Sun, Mar 22, 2026 at 8:59 PM jian he <jian.universality@gmail.com> wrote:
On Sun, Mar 22, 2026 at 4:59 PM Junwang Zhao <zhjwpku@gmail.com> wrote:
Hi hackers,
7dadd38cda introduced support for COPY TO in JSON format, but did
not include the corresponding COPY FROM functionality. The original
discussion [1] mentioned the possibility of supporting COPY FROM as
well, but that part of the patch never happened (If I don't miss).For a data format like JSON, basic round-trip capability seems essential:
if data can be exported via COPY TO, it should also be possible to import
it back using COPY FROM. So I try to close that gap.Hi.
repalloc can be replaced by repalloc_array.
maybe palloc, palloc0 can aslo be replaced by palloc_object/palloc0_object.I changed some of them, some are not changed because other similar patterns
use this pattern, I'd like to keep the consistency.``foreach(cur, cstate->attnumlist)``
can be replaced by
``foreach_int(cur, cstate->attnumlist)``Changed, since this can save a line of define, I also applied the same
change to other instances in this file.+ ereport(ERROR, + (errcode(ERRCODE_BAD_COPY_FILE_FORMAT), + errmsg("invalid input format for COPY JSON"), + errdetail("Document must begin with \"[\" or \"{\"."))); + pg_unreachable(); The parentheses in `` (errcode`` is not necessary, (and other palces) See https://www.postgresql.org/message-id/202510100916.s2e6n3xiwvyc%40alvherre.pgsqlChanged. There are quite some `` (errcode`` usages in this file, I
don't touch them to introduce more code churn.Should the JSON object match the table column?
create table copytest_from_json(style int, test text);
copy copytest_from_json (style, test) from stdin (format json);The below 3 valid json should all fail?
{"style": "1", "test": 1, "x": 1}
{"style": "1"}
{"x": "1"}I think json format should be more flexible, as long as it is a valid json
string representation. Extra fields and missing fields should be accepted.But I'd like to know what others' thought on this.
I am not sure the handling of empty strings is correct.
src9=# copy copytest_from_json (style, test) from stdin (format json);
Enter data to be copied followed by a newline.
End with a backslash and a period on a line by itself, or an EOF signal.\.
COPY 0
src9=# copy copytest_from_json (style, test) from stdin (format text);
Enter data to be copied followed by a newline.
End with a backslash and a period on a line by itself, or an EOF signal.\.
ERROR: invalid input syntax for type integer: ""
CONTEXT: COPY copytest_from_json, line 1, column style: ""I'm not entirely sure about this either, it’s a corner case. I also noticed
that the current implementation accepts [] as valid input, and the JSON
output for an empty table is also []. From a round-trip perspective,
the current behavior seems reasonable.Attach is the v2 resolved part of your comments, thanks for looking.
--
Regards
Junwang ZhaoHere is v3, with some improvement:
1. Stricter JSON array scanning, adds an explicit array scan state
machine, so commas, array endings, missing separators, trailing
commas, and data after the top-level array are all treated as errors
now.
2. Better EOF handling, it now detects EOF while inside an unfinished
object or string and reports unexpected end of input in COPY JSON,
instead of potentially waiting indefinitely or producing unclear
behavior.
3. Correct json/jsonb scalar round-trip, for target columns of type
json or jsonb, scalar JSON values are preserved as valid JSON text,
e.g. "foo" stays "foo" and true stays true, instead of being converted
to SQL-style text such as foo or t.
4. More accurate error line numbers, which was pointed out by Jian off-list.--
Regards
Junwang Zhao
Attached is v4, rebased on master. Main changes since v3:
- replace whole-row jsonb_in() parsing with JSON parser callbacks, retaining
the original JSON text until column conversion. This preserves duplicate keys
and escapes in json column values and avoids rejecting valid JSON merely
because it cannot be represented as JSONB.
- Improve type conversion: handle domains over json/jsonb, and reuse
json_populate_type() for SQL arrays, composite types, and their domains.
Boolean scalars passed to other input functions now use true/false
rather than t/f.
- Parse completed rows in place using buffer offsets, and compact consumed
input only when refilling the buffer. This avoids repeatedly copying
the unread
suffix after every row. Column-name mappings and conversion metadata are
also cached per COPY operation.
CC'd Amit and Andrew, and would appreciate your thoughts on whether this
feature makes sense, thanks.
--
Regards
Junwang Zhao
On Sun, Oct 4, 2026 at 12:09 PM Junwang Zhao <zhjwpku@gmail.com> wrote:
On Sun, Jun 7, 2026 at 11:13 PM Junwang Zhao <zhjwpku@gmail.com> wrote:
On Sun, Mar 22, 2026 at 10:16 PM Junwang Zhao <zhjwpku@gmail.com> wrote:
Hi Jian,
On Sun, Mar 22, 2026 at 8:59 PM jian he <jian.universality@gmail.com> wrote:
On Sun, Mar 22, 2026 at 4:59 PM Junwang Zhao <zhjwpku@gmail.com> wrote:
Hi hackers,
7dadd38cda introduced support for COPY TO in JSON format, but did
not include the corresponding COPY FROM functionality. The original
discussion [1] mentioned the possibility of supporting COPY FROM as
well, but that part of the patch never happened (If I don't miss).For a data format like JSON, basic round-trip capability seems essential:
if data can be exported via COPY TO, it should also be possible to import
it back using COPY FROM. So I try to close that gap.Hi.
repalloc can be replaced by repalloc_array.
maybe palloc, palloc0 can aslo be replaced by palloc_object/palloc0_object.I changed some of them, some are not changed because other similar patterns
use this pattern, I'd like to keep the consistency.``foreach(cur, cstate->attnumlist)``
can be replaced by
``foreach_int(cur, cstate->attnumlist)``Changed, since this can save a line of define, I also applied the same
change to other instances in this file.+ ereport(ERROR, + (errcode(ERRCODE_BAD_COPY_FILE_FORMAT), + errmsg("invalid input format for COPY JSON"), + errdetail("Document must begin with \"[\" or \"{\"."))); + pg_unreachable(); The parentheses in `` (errcode`` is not necessary, (and other palces) See https://www.postgresql.org/message-id/202510100916.s2e6n3xiwvyc%40alvherre.pgsqlChanged. There are quite some `` (errcode`` usages in this file, I
don't touch them to introduce more code churn.Should the JSON object match the table column?
create table copytest_from_json(style int, test text);
copy copytest_from_json (style, test) from stdin (format json);The below 3 valid json should all fail?
{"style": "1", "test": 1, "x": 1}
{"style": "1"}
{"x": "1"}I think json format should be more flexible, as long as it is a valid json
string representation. Extra fields and missing fields should be accepted.But I'd like to know what others' thought on this.
I am not sure the handling of empty strings is correct.
src9=# copy copytest_from_json (style, test) from stdin (format json);
Enter data to be copied followed by a newline.
End with a backslash and a period on a line by itself, or an EOF signal.\.
COPY 0
src9=# copy copytest_from_json (style, test) from stdin (format text);
Enter data to be copied followed by a newline.
End with a backslash and a period on a line by itself, or an EOF signal.\.
ERROR: invalid input syntax for type integer: ""
CONTEXT: COPY copytest_from_json, line 1, column style: ""I'm not entirely sure about this either, it’s a corner case. I also noticed
that the current implementation accepts [] as valid input, and the JSON
output for an empty table is also []. From a round-trip perspective,
the current behavior seems reasonable.Attach is the v2 resolved part of your comments, thanks for looking.
--
Regards
Junwang ZhaoHere is v3, with some improvement:
1. Stricter JSON array scanning, adds an explicit array scan state
machine, so commas, array endings, missing separators, trailing
commas, and data after the top-level array are all treated as errors
now.
2. Better EOF handling, it now detects EOF while inside an unfinished
object or string and reports unexpected end of input in COPY JSON,
instead of potentially waiting indefinitely or producing unclear
behavior.
3. Correct json/jsonb scalar round-trip, for target columns of type
json or jsonb, scalar JSON values are preserved as valid JSON text,
e.g. "foo" stays "foo" and true stays true, instead of being converted
to SQL-style text such as foo or t.
4. More accurate error line numbers, which was pointed out by Jian off-list.--
Regards
Junwang ZhaoAttached is v4, rebased on master. Main changes since v3:
- replace whole-row jsonb_in() parsing with JSON parser callbacks, retaining
the original JSON text until column conversion. This preserves duplicate keys
and escapes in json column values and avoids rejecting valid JSON merely
because it cannot be represented as JSONB.- Improve type conversion: handle domains over json/jsonb, and reuse
json_populate_type() for SQL arrays, composite types, and their domains.
Boolean scalars passed to other input functions now use true/false
rather than t/f.- Parse completed rows in place using buffer offsets, and compact consumed
input only when refilling the buffer. This avoids repeatedly copying
the unread
suffix after every row. Column-name mappings and conversion metadata are
also cached per COPY operation.CC'd Amit and Andrew, and would appreciate your thoughts on whether this
feature makes sense, thanks.--
Regards
Junwang Zhao
I came across two links showing how to import JSON into Postgres
using COPY, which suggest this feature could be useful.
[1]: https://table.studio/blog/import-json-into-postgres-using-copy
[2]: https://stackoverflow.com/questions/39224382/how-can-i-import-a-json-file-into-postgresql/48396608#48396608
--
Regards
Junwang Zhao