On Sun, Oct 4, 2026 at 12:09 PM Junwang Zhao <[email protected]> wrote: > > On Sun, Jun 7, 2026 at 11:13 PM Junwang Zhao <[email protected]> wrote: > > > > On Sun, Mar 22, 2026 at 10:16 PM Junwang Zhao <[email protected]> wrote: > > > > > > Hi Jian, > > > > > > On Sun, Mar 22, 2026 at 8:59 PM jian he <[email protected]> > > > wrote: > > > > > > > > On Sun, Mar 22, 2026 at 4:59 PM Junwang Zhao <[email protected]> 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. > > > > > > > > > > > > > > > -- > > > > jian > > > > https://www.enterprisedb.com/ > > > > > > > > > > > > -- > > > 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 > > 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
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
