Re: Emitting JSON to file using COPY TO

On Sat, Mar 9, 2024 at 9:13 AM jian he wrote: > > On Sat, Mar 9, 2024 at 2:03 AM Joe Conway wrote: > > > > On 3/8/24 12:28, Andrey M. Borodin wrote: > > > Hello everyone! > > > > > > Thanks for working on this, really nice feature! > > > > > >> On 9 Jan 2024, at 01:40, Joe Conway wrote: > > >>

Re: Emitting JSON to file using COPY TO

On Sat, Mar 9, 2024 at 2:03 AM Joe Conway wrote: > > On 3/8/24 12:28, Andrey M. Borodin wrote: > > Hello everyone! > > > > Thanks for working on this, really nice feature! > > > >> On 9 Jan 2024, at 01:40, Joe Conway wrote: > >> > >> Thanks -- will have a look > > > > Joe, recently folks

Re: Emitting JSON to file using COPY TO

On 3/8/24 12:28, Andrey M. Borodin wrote: Hello everyone! Thanks for working on this, really nice feature! On 9 Jan 2024, at 01:40, Joe Conway wrote: Thanks -- will have a look Joe, recently folks proposed a lot of patches in this thread that seem like diverted from original way of

Re: Emitting JSON to file using COPY TO

Hello everyone! Thanks for working on this, really nice feature! > On 9 Jan 2024, at 01:40, Joe Conway wrote: > > Thanks -- will have a look Joe, recently folks proposed a lot of patches in this thread that seem like diverted from original way of implementation. As an author of CF entry [0]

Re: Emitting JSON to file using COPY TO

On Fri, Jan 19, 2024 at 4:10 PM Masahiko Sawada wrote: > > if (opts_out->json_mode && is_from) > ereport(ERROR, ...); > > if (!opts_out->json_mode && opts_out->force_array) > ereport(ERROR, ...); > > Also these checks can be moved close to other checks at the end of > ProcessCopyOptions(). > >

Re: Emitting JSON to file using COPY TO

On Fri, Feb 2, 2024 at 5:48 PM Alvaro Herrera wrote: > > If you want the server to send this message when the JSON word is not in > quotes, I'm afraid that's not possible, due to the funny nature of the > FORMAT keyword when the JSON keyword appears after it. But why do you > care? If you use

Re: Emitting JSON to file using COPY TO

On 2024-Feb-02, jian he wrote: > copy (select 1) to stdout with (format json); > ERROR: syntax error at or near "format" > LINE 1: copy (select 1) to stdout with (format json); > ^ > > json is a keyword. Is it possible to escape it? > make `copy

Re: Emitting JSON to file using COPY TO

On Wed, Jan 31, 2024 at 9:26 PM Alvaro Herrera wrote: > > On 2024-Jan-23, jian he wrote: > > > > + | FORMAT_LA copy_generic_opt_arg > > > + { > > > + $$ = makeDefElem("format", $2, @1); > > > + } > > > ; > > > > > > I think it's not

Re: Emitting JSON to file using COPY TO

On 2024-Jan-23, jian he wrote: > > + | FORMAT_LA copy_generic_opt_arg > > + { > > + $$ = makeDefElem("format", $2, @1); > > + } > > ; > > > > I think it's not necessary. "format" option is already handled in > >

Re: Emitting JSON to file using COPY TO

Hi Vignesh, On Wed, Jan 31, 2024 at 5:50 PM vignesh C wrote: > > On Sat, 27 Jan 2024 at 11:25, Junwang Zhao wrote: > > > > Hi hackers, > > > > Kou-san(CCed) has been working on *Make COPY format extendable[1]*, so > > I think making *copy to json* based on that work might be the right > >

Re: Emitting JSON to file using COPY TO

On Sat, 27 Jan 2024 at 11:25, Junwang Zhao wrote: > > Hi hackers, > > Kou-san(CCed) has been working on *Make COPY format extendable[1]*, so > I think making *copy to json* based on that work might be the right direction. > > I write an extension for that purpose, and here is the patch set

Re: Emitting JSON to file using COPY TO

On Fri, Jan 19, 2024 at 4:10 PM Masahiko Sawada wrote: > > If I'm not missing, copyto_json.007.diff is the latest patch but it > needs to be rebased to the current HEAD. Here are random comments: > please check the latest version. > if (opts_out->json_mode) > + { > + if (is_from) > +

Re: Emitting JSON to file using COPY TO

On Thu, Dec 7, 2023 at 10:10 AM Joe Conway wrote: > > On 12/6/23 18:09, Joe Conway wrote: > > On 12/6/23 14:47, Joe Conway wrote: > >> On 12/6/23 13:59, Daniel Verite wrote: > >>> Andrew Dunstan wrote: > >>> > IMNSHO, we should produce either a single JSON > document (the ARRAY

Re: Emitting JSON to file using COPY TO

On Tue, Jan 16, 2024 at 11:46 AM jian he wrote: > > > I think the reason is maybe related to the function copy_dest_startup. I was wrong about this sentence. in the function CopyOneRowTo `if (!cstate->opts.json_mode)` else branch change to the following: else { Datum rowdata; StringInfo result;

Re: Emitting JSON to file using COPY TO

On Tue, Jan 9, 2024 at 4:40 AM Joe Conway wrote: > > On 1/8/24 14:36, Dean Rasheed wrote: > > On Thu, 7 Dec 2023 at 01:10, Joe Conway wrote: > >> > >> The attached should fix the CopyOut response to say one column. > >> > > > > Playing around with this, I found a couple of cases that generate an

Re: Emitting JSON to file using COPY TO

On 1/8/24 14:36, Dean Rasheed wrote: On Thu, 7 Dec 2023 at 01:10, Joe Conway wrote: The attached should fix the CopyOut response to say one column. Playing around with this, I found a couple of cases that generate an error: COPY (SELECT 1 UNION ALL SELECT 2) TO stdout WITH (format json);

Re: Emitting JSON to file using COPY TO

On Thu, 7 Dec 2023 at 01:10, Joe Conway wrote: > > The attached should fix the CopyOut response to say one column. > Playing around with this, I found a couple of cases that generate an error: COPY (SELECT 1 UNION ALL SELECT 2) TO stdout WITH (format json); COPY (VALUES (1), (2)) TO stdout

Re: Emitting JSON to file using COPY TO

> On Sat, Dec 2, 2023 at 4:11 PM Tom Lane wrote: > > Joe Conway writes: > >> I noticed that, with the PoC patch, "json" is the only format that must be > >> quoted. Without quotes, I see a syntax error. In longer term we should move any specific COPY flag names and values out of grammar and

Re: Emitting JSON to file using COPY TO

On 12/8/23 14:45, Daniel Verite wrote: Joe Conway wrote: copyto_json.007.diff When the source has json fields with non-significant line feeds, the COPY output has these line feeds too, which makes the output incompatible with rule #2 at https://jsonlines.org ("2. Each Line is a

Re: Emitting JSON to file using COPY TO

Dave Cramer wrote: > > This argument for leaving 3 as the column count makes sense to me. I > > agree this content is not meant to facilitate interpreting the contents at > > a protocol level. > > > > I'd disagree. From my POV if the data comes back as a JSON Array this is > one object

Re: Emitting JSON to file using COPY TO

Joe Conway wrote: > copyto_json.007.diff When the source has json fields with non-significant line feeds, the COPY output has these line feeds too, which makes the output incompatible with rule #2 at https://jsonlines.org ("2. Each Line is a Valid JSON Value"). create table j(f json);

Re: Emitting JSON to file using COPY TO

On Thu, 7 Dec 2023 at 08:47, David G. Johnston wrote: > On Thursday, December 7, 2023, Daniel Verite > wrote: > >> Joe Conway wrote: >> >> > The attached should fix the CopyOut response to say one column. I.e. it >> > ought to look something like: >> >> Spending more time with the doc I

Re: Emitting JSON to file using COPY TO

On 12/7/23 09:11, David G. Johnston wrote: Those are all the same breakage though - if truly interpreted as data rows the protocol is basically written such that the array format is not supportable and only the lines format can be used.  Hence my “format 0 doesn’t work” comment for array

Re: Emitting JSON to file using COPY TO

On Thursday, December 7, 2023, Joe Conway wrote: > On 12/7/23 08:35, Daniel Verite wrote: > >> Joe Conway wrote: >> >> The attached should fix the CopyOut response to say one column. I.e. it >>> ought to look something like: >>> >> >> Spending more time with the doc I came to the opinion

Re: Emitting JSON to file using COPY TO

On 12/7/23 08:52, Joe Conway wrote: Or maybe this is preferred? 8<-- [{"ss":{"f1":1,"f2":1}}, {"ss":{"f1":1,"f2":2}}, {"ss":{"f1":1,"f2":3}}] 8<-- I don't know why my mail client keeps adding extra spaces, but the intention here is a single space in front

Re: Emitting JSON to file using COPY TO

On 12/7/23 08:35, Daniel Verite wrote: Joe Conway wrote: The attached should fix the CopyOut response to say one column. I.e. it ought to look something like: Spending more time with the doc I came to the opinion that in this bit of the protocol, in CopyOutResponse (B) ... Int16 The

Re: Emitting JSON to file using COPY TO

On Thursday, December 7, 2023, Daniel Verite wrote: > Joe Conway wrote: > > > The attached should fix the CopyOut response to say one column. I.e. it > > ought to look something like: > > Spending more time with the doc I came to the opinion that in this bit > of the protocol, in

Re: Emitting JSON to file using COPY TO

Joe Conway wrote: > The attached should fix the CopyOut response to say one column. I.e. it > ought to look something like: Spending more time with the doc I came to the opinion that in this bit of the protocol, in CopyOutResponse (B) ... Int16 The number of columns in the data to be

Re: Emitting JSON to file using COPY TO

On 2023-12-06 We 17:56, David G. Johnston wrote: On Wed, Dec 6, 2023 at 3:38 PM Joe Conway wrote: So the questions are: 1. Do those two formats work for the initial implementation? Yes.  We provide a stream-oriented format and one atomic-import format. 2. Is the default correct

Re: Emitting JSON to file using COPY TO

On 12/6/23 21:56, Nathan Bossart wrote: On Wed, Dec 06, 2023 at 03:20:46PM -0500, Tom Lane wrote: If Nathan's perf results hold up elsewhere, it seems like some micro-optimization around the text-pushing (appendStringInfoString) might be more useful than caching. The 7% spent in cache lookups

Re: Emitting JSON to file using COPY TO

On Wed, Dec 06, 2023 at 03:20:46PM -0500, Tom Lane wrote: > If Nathan's perf results hold up elsewhere, it seems like some > micro-optimization around the text-pushing (appendStringInfoString) > might be more useful than caching. The 7% spent in cache lookups > could be worth going after later,

Re: Emitting JSON to file using COPY TO

On Wed, Dec 6, 2023, at 3:59 PM, Daniel Verite wrote: > The first Copy data message with contents "5b0a" does not qualify > as a row of data with 3 columns as advertised in the CopyOut > message. Isn't that a problem? > > At least the json non-ARRAY case ("json lines") doesn't have > this issue,

Re: Emitting JSON to file using COPY TO

On Wed, Dec 6, 2023 at 6:14 PM Joe Conway wrote: > > > But the point that we should introduce a 2 still stands. The new code > > would mean: use text output functions but that there is no inherent > > tabular structure in the underlying contents. Instead the copy format > > was JSON and the

Re: Emitting JSON to file using COPY TO

On 12/6/23 20:09, David G. Johnston wrote: On Wed, Dec 6, 2023 at 5:57 PM Joe Conway > wrote: On 12/6/23 19:39, David G. Johnston wrote: > On Wed, Dec 6, 2023 at 4:45 PM Joe Conway mailto:m...@joeconway.com> >

Re: Emitting JSON to file using COPY TO

On 12/6/23 18:09, Joe Conway wrote: On 12/6/23 14:47, Joe Conway wrote: On 12/6/23 13:59, Daniel Verite wrote: Andrew Dunstan wrote: IMNSHO, we should produce either a single JSON document (the ARRAY case) or a series of JSON documents, one per row (the LINES case). "COPY

Re: Emitting JSON to file using COPY TO

On Wed, Dec 6, 2023 at 5:57 PM Joe Conway wrote: > On 12/6/23 19:39, David G. Johnston wrote: > > On Wed, Dec 6, 2023 at 4:45 PM Joe Conway > > wrote: > > > But I still cannot shake the belief that using a format code of 1 - > > which really could be interpreted as

Re: Emitting JSON to file using COPY TO

On 12/6/23 19:39, David G. Johnston wrote: On Wed, Dec 6, 2023 at 4:45 PM Joe Conway > wrote: " The backend sends a CopyOutResponse message to the frontend, followed     by zero or more CopyData messages (always one per row), followed by     CopyDone"

Re: Emitting JSON to file using COPY TO

On Wed, Dec 6, 2023 at 4:45 PM Joe Conway wrote: > > " The backend sends a CopyOutResponse message to the frontend, followed > by zero or more CopyData messages (always one per row), followed by > CopyDone" > > probably "always one per row" would be changed to note that json array >

Re: Emitting JSON to file using COPY TO

On 12/6/23 18:38, David G. Johnston wrote: On Wed, Dec 6, 2023 at 4:28 PM David G. Johnston mailto:david.g.johns...@gmail.com>> wrote: On Wed, Dec 6, 2023 at 4:09 PM Joe Conway mailto:m...@joeconway.com>> wrote: On 12/6/23 14:47, Joe Conway wrote: > On 12/6/23 13:59,

Re: Emitting JSON to file using COPY TO

On 12/6/23 18:28, David G. Johnston wrote: On Wed, Dec 6, 2023 at 4:09 PM Joe Conway > wrote: On 12/6/23 14:47, Joe Conway wrote: > On 12/6/23 13:59, Daniel Verite wrote: >>      Andrew Dunstan wrote: >> >>> IMNSHO, we should produce either a

Re: Emitting JSON to file using COPY TO

On Wed, Dec 6, 2023 at 4:28 PM David G. Johnston wrote: > On Wed, Dec 6, 2023 at 4:09 PM Joe Conway wrote: > >> On 12/6/23 14:47, Joe Conway wrote: >> > On 12/6/23 13:59, Daniel Verite wrote: >> >> Andrew Dunstan wrote: >> >> >> >>> IMNSHO, we should produce either a single JSON >> >>>

Re: Emitting JSON to file using COPY TO

On Wed, Dec 6, 2023 at 4:09 PM Joe Conway wrote: > On 12/6/23 14:47, Joe Conway wrote: > > On 12/6/23 13:59, Daniel Verite wrote: > >> Andrew Dunstan wrote: > >> > >>> IMNSHO, we should produce either a single JSON > >>> document (the ARRAY case) or a series of JSON documents, one per row >

Re: Emitting JSON to file using COPY TO

On 12/6/23 14:47, Joe Conway wrote: On 12/6/23 13:59, Daniel Verite wrote: Andrew Dunstan wrote: IMNSHO, we should produce either a single JSON document (the ARRAY case) or a series of JSON documents, one per row (the LINES case). "COPY Operations" in the doc says: " The backend

Re: Emitting JSON to file using COPY TO

On Wed, Dec 6, 2023 at 3:38 PM Joe Conway wrote: > So the questions are: > 1. Do those two formats work for the initial implementation? > Yes. We provide a stream-oriented format and one atomic-import format. 2. Is the default correct or should it be switched > e.g. rather than specifying

Re: Emitting JSON to file using COPY TO

On 12/6/23 16:42, Sehrope Sarkuni wrote: On Wed, Dec 6, 2023 at 4:29 PM Joe Conway > wrote: > 1. Outputting a top level JSON object without the additional column > keys. IIUC, the top level keys are always the column names. A common use > case would

Re: Emitting JSON to file using COPY TO

On Wed, Dec 6, 2023 at 4:29 PM Joe Conway wrote: > > 1. Outputting a top level JSON object without the additional column > > keys. IIUC, the top level keys are always the column names. A common use > > case would be a single json/jsonb column that is already formatted > > exactly as the user

Re: Emitting JSON to file using COPY TO

On Wed, Dec 06, 2023 at 03:20:46PM -0500, Tom Lane wrote: > If Nathan's perf results hold up elsewhere, it seems like some > micro-optimization around the text-pushing (appendStringInfoString) > might be more useful than caching. The 7% spent in cache lookups > could be worth going after later,

Re: Emitting JSON to file using COPY TO

On Wed, Dec 6, 2023 at 4:03 PM Andrew Dunstan wrote: > > The output size difference does say that maybe we should pay some > > attention to the nearby request to not always label every field. > > Perhaps there should be an option for each row to transform to > > a JSON array rather than an

Re: Emitting JSON to file using COPY TO

On 12/6/23 11:28, Sehrope Sarkuni wrote: Big +1 to this overall feature. cool! Regarding the defaults for the output, I think JSON lines (rather than a JSON array of objects) would be preferred. It's more natural to combine them and generate that type of data on the fly rather than forcing

Re: Emitting JSON to file using COPY TO

On 2023-12-06 We 15:20, Tom Lane wrote: Joe Conway writes: I'll see if I can add some caching to composite_to_json(), but based on the relative data size it does not sound like there is much performance left on the table to go after, no? If Nathan's perf results hold up elsewhere, it seems

Re: Emitting JSON to file using COPY TO

Joe Conway writes: > I'll see if I can add some caching to composite_to_json(), but based on > the relative data size it does not sound like there is much performance > left on the table to go after, no? If Nathan's perf results hold up elsewhere, it seems like some micro-optimization around

Re: Emitting JSON to file using COPY TO

On 12/6/23 11:44, Nathan Bossart wrote: On Wed, Dec 06, 2023 at 10:33:49AM -0600, Nathan Bossart wrote: (format csv) Time: 12295.480 ms (00:12.295) Time: 12311.059 ms (00:12.311) Time: 12305.469 ms (00:12.305) (format json) Time: 24568.621 ms

Re: Emitting JSON to file using COPY TO

On 12/6/23 13:59, Daniel Verite wrote: Andrew Dunstan wrote: IMNSHO, we should produce either a single JSON document (the ARRAY case) or a series of JSON documents, one per row (the LINES case). "COPY Operations" in the doc says: " The backend sends a CopyOutResponse message to the

Re: Emitting JSON to file using COPY TO

Andrew Dunstan wrote: > IMNSHO, we should produce either a single JSON > document (the ARRAY case) or a series of JSON documents, one per row > (the LINES case). "COPY Operations" in the doc says: " The backend sends a CopyOutResponse message to the frontend, followed by zero or

Re: Emitting JSON to file using COPY TO

On Wed, Dec 06, 2023 at 10:33:49AM -0600, Nathan Bossart wrote: > (format csv) > Time: 12295.480 ms (00:12.295) > Time: 12311.059 ms (00:12.311) > Time: 12305.469 ms (00:12.305) > > (format json) > Time: 24568.621 ms (00:24.569) > Time: 23756.234 ms

Re: Emitting JSON to file using COPY TO

On Wed, Dec 06, 2023 at 11:28:59AM -0500, Tom Lane wrote: > It might be acceptable to plan on improving the performance later, > depending on just how bad it is now. On 10M rows with 11 integers each, I'm seeing the following: (format text) Time: 10056.311 ms (00:10.056)

Re: Emitting JSON to file using COPY TO

Andrew Dunstan writes: > On 2023-12-06 We 10:44, Tom Lane wrote: >> In particular, has anyone done any performance testing? >> I'm concerned about that because composite_to_json() has >> zero capability to cache any metadata across calls, meaning >> there is going to be a large amount of

Re: Emitting JSON to file using COPY TO

Big +1 to this overall feature. This is something I've wanted for a long time as well. While it's possible to use a COPY with text output for a trivial case, the double escaping falls apart quickly for arbitrary data. It's really only usable when you know exactly what you are querying and know it

Re: Emitting JSON to file using COPY TO

Joe Conway writes: > On 12/6/23 10:44, Tom Lane wrote: >> In particular, has anyone done any performance testing? > I will devise some kind of test and report back. I suppose something > with many rows and many narrow columns comparing time to COPY > text/csv/json modes would do the trick?

Re: Emitting JSON to file using COPY TO

On 2023-12-06 We 10:44, Tom Lane wrote: Joe Conway writes: I believe this is ready to commit unless there are further comments or objections. I thought we were still mostly at proof-of-concept stage? In particular, has anyone done any performance testing? I'm concerned about that because

Re: Emitting JSON to file using COPY TO

On 12/6/23 10:44, Tom Lane wrote: Joe Conway writes: I believe this is ready to commit unless there are further comments or objections. I thought we were still mostly at proof-of-concept stage? The concept is narrowly scoped enough that I think we are homing in on the final patch. In

Re: Emitting JSON to file using COPY TO

On 12/6/23 10:32, Andrew Dunstan wrote: On 2023-12-06 We 08:49, Joe Conway wrote: On 12/6/23 07:36, Andrew Dunstan wrote: On 2023-12-05 Tu 16:46, Joe Conway wrote: On 12/5/23 16:20, Andrew Dunstan wrote: On 2023-12-05 Tu 16:09, Joe Conway wrote: On 12/5/23 16:02, Joe Conway wrote: On

Re: Emitting JSON to file using COPY TO

Joe Conway writes: > I believe this is ready to commit unless there are further comments or > objections. I thought we were still mostly at proof-of-concept stage? In particular, has anyone done any performance testing? I'm concerned about that because composite_to_json() has zero capability

Re: Emitting JSON to file using COPY TO

On 2023-12-06 We 08:49, Joe Conway wrote: On 12/6/23 07:36, Andrew Dunstan wrote: On 2023-12-05 Tu 16:46, Joe Conway wrote: On 12/5/23 16:20, Andrew Dunstan wrote: On 2023-12-05 Tu 16:09, Joe Conway wrote: On 12/5/23 16:02, Joe Conway wrote: On 12/5/23 15:55, Andrew Dunstan wrote: and

Re: Emitting JSON to file using COPY TO

On 12/6/23 07:36, Andrew Dunstan wrote: On 2023-12-05 Tu 16:46, Joe Conway wrote: On 12/5/23 16:20, Andrew Dunstan wrote: On 2023-12-05 Tu 16:09, Joe Conway wrote: On 12/5/23 16:02, Joe Conway wrote: On 12/5/23 15:55, Andrew Dunstan wrote: and in any other case (e.g. LINES) I can't see why

Re: Emitting JSON to file using COPY TO

On 2023-12-05 Tu 16:46, Joe Conway wrote: On 12/5/23 16:20, Andrew Dunstan wrote: On 2023-12-05 Tu 16:09, Joe Conway wrote: On 12/5/23 16:02, Joe Conway wrote: On 12/5/23 15:55, Andrew Dunstan wrote: and in any other case (e.g. LINES) I can't see why you would have them. Oh I didn't

Re: Emitting JSON to file using COPY TO

> Am I understanding something incorrectly? No, you've got it. You already covered the concerns there. > That seems quite absurd, TBH. I know we've catered for some absurdity in > the CSV code (much of it down to me), so maybe we need to be liberal in > what we accept here too. IMNSHO, we

Re: Emitting JSON to file using COPY TO

On 12/5/23 16:20, Andrew Dunstan wrote: On 2023-12-05 Tu 16:09, Joe Conway wrote: On 12/5/23 16:02, Joe Conway wrote: On 12/5/23 15:55, Andrew Dunstan wrote: and in any other case (e.g. LINES) I can't see why you would have them. Oh I didn't address this -- I saw examples in the interwebs

Re: Emitting JSON to file using COPY TO

On 2023-12-05 Tu 16:09, Joe Conway wrote: On 12/5/23 16:02, Joe Conway wrote: On 12/5/23 15:55, Andrew Dunstan wrote: and in any other case (e.g. LINES) I can't see why you would have them. Oh I didn't address this -- I saw examples in the interwebs of MSSQL server I think [1] which had

Re: Emitting JSON to file using COPY TO

On 12/5/23 16:12, Andrew Dunstan wrote: On 2023-12-05 Tu 16:02, Joe Conway wrote: On 12/5/23 15:55, Andrew Dunstan wrote: On 2023-12-05 Tu 14:50, Davin Shearer wrote: Hi Joe, In reviewing the 005 patch, I think that when used with FORCE ARRAY, we should also _imply_ FORCE ROW DELIMITER. 

Re: Emitting JSON to file using COPY TO

On 2023-12-05 Tu 16:02, Joe Conway wrote: On 12/5/23 15:55, Andrew Dunstan wrote: On 2023-12-05 Tu 14:50, Davin Shearer wrote: Hi Joe, In reviewing the 005 patch, I think that when used with FORCE ARRAY, we should also _imply_ FORCE ROW DELIMITER.  I can't envision a use case where

Re: Emitting JSON to file using COPY TO

On 12/5/23 16:02, Joe Conway wrote: On 12/5/23 15:55, Andrew Dunstan wrote: and in any other case (e.g. LINES) I can't see why you would have them. Oh I didn't address this -- I saw examples in the interwebs of MSSQL server I think [1] which had the non-array with commas import and export

Re: Emitting JSON to file using COPY TO

On 12/5/23 15:55, Andrew Dunstan wrote: On 2023-12-05 Tu 14:50, Davin Shearer wrote: Hi Joe, In reviewing the 005 patch, I think that when used with FORCE ARRAY, we should also _imply_ FORCE ROW DELIMITER.  I can't envision a use case where someone would want to use FORCE ARRAY without also

Re: Emitting JSON to file using COPY TO

On 2023-12-05 Tu 14:50, Davin Shearer wrote: Hi Joe, In reviewing the 005 patch, I think that when used with FORCE ARRAY, we should also _imply_ FORCE ROW DELIMITER.  I can't envision a use case where someone would want to use FORCE ARRAY without also using FORCE ROW DELIMITER.  I can,

Re: Emitting JSON to file using COPY TO

Hi Joe, In reviewing the 005 patch, I think that when used with FORCE ARRAY, we should also _imply_ FORCE ROW DELIMITER. I can't envision a use case where someone would want to use FORCE ARRAY without also using FORCE ROW DELIMITER. I can, however, envision a use case where someone would want

Re: Emitting JSON to file using COPY TO

On 12/5/23 12:43, Davin Shearer wrote: Joe, those test cases look great and the outputs are the same as `jq`. Forward slash escaping is optional, so not escaping them in Postgres is okay. The important thing is that the software _reading_ JSON interprets both '\/' and '/' as '/'. Thanks

Re: Emitting JSON to file using COPY TO

Thanks for the wayback machine link Andrew. I read it, understood it, and will comply. Joe, those test cases look great and the outputs are the same as `jq`. As for forward slashes being escaped, I found this: https://stackoverflow.com/questions/1580647/json-why-are-forward-slashes-escaped .

Re: Emitting JSON to file using COPY TO

On 12/4/23 21:54, Joe Conway wrote: On 12/4/23 17:55, Davin Shearer wrote: There are however a few characters that need to be escaped 1. |"|(double quote) 2. |\|(backslash) 3. |/|(forward slash) 4. |\b|(backspace) 5. |\f|(form feed) 6. |\n|(new line) 7. |\r|(carriage return) 8.

Re: Emitting JSON to file using COPY TO

On 2023-12-04 Mo 17:55, Davin Shearer wrote: Sorry about the top posting / top quoting... the link you sent me gives me a 404.  I'm not exactly sure what top quoting / posting means and Googling those terms wasn't helpful for me, but I've removed the quoting that my mail client is

Re: Emitting JSON to file using COPY TO

On 12/4/23 17:55, Davin Shearer wrote: Sorry about the top posting / top quoting... the link you sent me gives me a 404.  I'm not exactly sure what top quoting / posting means and Googling those terms wasn't helpful for me, but I've removed the quoting that my mail client is automatically

Re: Emitting JSON to file using COPY TO

Sorry about the top posting / top quoting... the link you sent me gives me a 404. I'm not exactly sure what top quoting / posting means and Googling those terms wasn't helpful for me, but I've removed the quoting that my mail client is automatically "helpfully" adding to my emails. I mean no

Re: Emitting JSON to file using COPY TO

On 2023-12-04 Mo 13:37, Davin Shearer wrote: Looking great! For testing, in addition to the quotes, include DOS and Unix EOL, \ and /, Byte Order Markers, and mulitbyte characters like UTF-8. Essentially anything considered textural is fair game to be a value. Joe already asked you to

Re: Emitting JSON to file using COPY TO

Looking great! For testing, in addition to the quotes, include DOS and Unix EOL, \ and /, Byte Order Markers, and mulitbyte characters like UTF-8. Essentially anything considered textural is fair game to be a value. On Mon, Dec 4, 2023, 10:46 Joe Conway wrote: > On 12/4/23 09:25, Andrew

Re: Emitting JSON to file using COPY TO

On 12/4/23 09:25, Andrew Dunstan wrote: On 2023-12-04 Mo 08:37, Joe Conway wrote: On 12/4/23 07:41, Andrew Dunstan wrote: On 2023-12-03 Su 20:14, Joe Conway wrote: (please don't top quote on the Postgres lists) On 12/3/23 17:38, Davin Shearer wrote: " being quoted as \\" breaks the JSON.

Re: Emitting JSON to file using COPY TO

On 2023-12-04 Mo 08:37, Joe Conway wrote: On 12/4/23 07:41, Andrew Dunstan wrote: On 2023-12-03 Su 20:14, Joe Conway wrote: (please don't top quote on the Postgres lists) On 12/3/23 17:38, Davin Shearer wrote: " being quoted as \\" breaks the JSON. It needs to be \".  This has been my

Re: Emitting JSON to file using COPY TO

On 12/4/23 07:41, Andrew Dunstan wrote: On 2023-12-03 Su 20:14, Joe Conway wrote: (please don't top quote on the Postgres lists) On 12/3/23 17:38, Davin Shearer wrote: " being quoted as \\" breaks the JSON. It needs to be \".  This has been my whole problem with COPY TO for JSON. Please

Re: Emitting JSON to file using COPY TO

On 2023-12-03 Su 20:14, Joe Conway wrote: (please don't top quote on the Postgres lists) On 12/3/23 17:38, Davin Shearer wrote: " being quoted as \\" breaks the JSON. It needs to be \".  This has been my whole problem with COPY TO for JSON. Please validate that the output is in proper

Re: Emitting JSON to file using COPY TO

I worked around it by using select json_agg(t)... and redirecting it to file via psql on the command line. COPY TO was working until we ran into broken JSON and discovered the double quoting issue due to some values containing " in them.

Re: Emitting JSON to file using COPY TO

(please don't top quote on the Postgres lists) On 12/3/23 17:38, Davin Shearer wrote: " being quoted as \\" breaks the JSON. It needs to be \".  This has been my whole problem with COPY TO for JSON. Please validate that the output is in proper format with correct quoting for special

Re: Emitting JSON to file using COPY TO

" being quoted as \\" breaks the JSON. It needs to be \". This has been my whole problem with COPY TO for JSON. Please validate that the output is in proper format with correct quoting for special characters. I use `jq` on the command line to validate and format the output. On Sun, Dec 3, 2023,

Re: Emitting JSON to file using COPY TO

On 12/3/23 14:52, Andrew Dunstan wrote: On 2023-12-03 Su 14:24, Joe Conway wrote: On 12/3/23 11:03, Joe Conway wrote: On 12/3/23 10:10, Andrew Dunstan wrote: I  realize this is just a POC, but I'd prefer to see composite_to_json() not exposed. You could use the already public

Re: Emitting JSON to file using COPY TO

On 2023-12-03 Su 14:24, Joe Conway wrote: On 12/3/23 11:03, Joe Conway wrote: On 12/3/23 10:10, Andrew Dunstan wrote: I  realize this is just a POC, but I'd prefer to see composite_to_json() not exposed. You could use the already public datum_to_json() instead, passing JSONTYPE_COMPOSITE

Re: Emitting JSON to file using COPY TO

On 2023-12-03 Su 12:11, Joe Conway wrote: On 12/3/23 11:03, Joe Conway wrote:   From your earlier post, regarding constructing the aggregate -- not extensive testing but one data point: 8<-- test=# copy foo to '/tmp/buf' (format json, force_array); COPY 1000 Time:

Re: Emitting JSON to file using COPY TO

On 12/3/23 11:03, Joe Conway wrote: On 12/3/23 10:10, Andrew Dunstan wrote: I  realize this is just a POC, but I'd prefer to see composite_to_json() not exposed. You could use the already public datum_to_json() instead, passing JSONTYPE_COMPOSITE and F_RECORD_OUT as the second and third

Re: Emitting JSON to file using COPY TO

On 12/3/23 11:03, Joe Conway wrote: From your earlier post, regarding constructing the aggregate -- not extensive testing but one data point: 8<-- test=# copy foo to '/tmp/buf' (format json, force_array); COPY 1000 Time: 36353.153 ms (00:36.353) test=# copy (select

Re: Emitting JSON to file using COPY TO

On 12/3/23 10:10, Andrew Dunstan wrote: On 2023-12-01 Fr 14:28, Joe Conway wrote: On 11/29/23 10:32, Davin Shearer wrote: Thanks for the responses everyone. I worked around the issue using the `psql -tc` method as Filip described. I think it would be great to support writing JSON using

Re: Emitting JSON to file using COPY TO

On 12/3/23 10:31, Davin Shearer wrote: Please be sure to include single and double quotes in the test values since that was the original problem (double quoting in COPY TO breaking the JSON syntax). test=# copy (select * from foo limit 4) to stdout (format json); {"id":2456092,"f1":"line with

Re: Emitting JSON to file using COPY TO

Please be sure to include single and double quotes in the test values since that was the original problem (double quoting in COPY TO breaking the JSON syntax). On Sun, Dec 3, 2023, 10:11 Andrew Dunstan wrote: > > On 2023-12-01 Fr 14:28, Joe Conway wrote: > > On 11/29/23 10:32, Davin Shearer

Re: Emitting JSON to file using COPY TO

On 2023-12-01 Fr 14:28, Joe Conway wrote: On 11/29/23 10:32, Davin Shearer wrote: Thanks for the responses everyone. I worked around the issue using the `psql -tc` method as Filip described. I think it would be great to support writing JSON using COPY TO at some point so I can emit JSON

Re: Emitting JSON to file using COPY TO

On 12/2/23 17:37, Joe Conway wrote: On 12/2/23 16:53, Nathan Bossart wrote: On Sat, Dec 02, 2023 at 10:11:20AM -0500, Tom Lane wrote: So if you are writing a production that might need to match FORMAT followed by JSON, you need to match FORMAT_LA too. Thanks for the pointer. That does seem

  1   2   >