Re: Emitting JSON to file using COPY TO

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 to files using a PostgreSQL function directly. -Davin On Tue, Nov 28, 2023 at 2:36 AM Fil

Re: Emitting JSON to file using COPY TO

This would be a very special case for COPY. It applies only to a single column of JSON values. The original problem can be solved with psql --tuples-only as David wrote earlier. $ psql -tc 'select json_agg(row_to_json(t)) from (select * from public.tbl_json_test) t;' [{"id":1,"t

Re: Emitting JSON to file using COPY TO

On Mon, Nov 27, 2023 at 5:04 PM Adrian Klaver wrote: > On 11/27/23 01:44, Dominique Devienne wrote: > > On Mon, Nov 27, 2023 at 10:33 AM Dominique Devienne > > wrote: > > On second thought, I guess that's COPY in its text modes doing the > escaping? > > Interesting. T

Re: Emitting JSON to file using COPY TO

On 11/27/23 01:44, Dominique Devienne wrote: On Mon, Nov 27, 2023 at 10:33 AM Dominique Devienne > wrote: On second thought, I guess that's COPY in its text modes doing the escaping? Interesting. The text-based modes of COPY are configurable. There's even a JSON m

Re: Emitting JSON to file using COPY TO

On Mon, Nov 27, 2023 at 3:56 PM Tom Lane wrote: > "David G. Johnston" writes: > > I agree there should be a copy option for “not formatted” so if you dump > a > > single column result in that format you get the raw unescaped contents of > > the column. > > I'm not sure I even buy that. JSON dat

Re: Emitting JSON to file using COPY TO

"David G. Johnston" writes: > I agree there should be a copy option for “not formatted” so if you dump a > single column result in that format you get the raw unescaped contents of > the column. I'm not sure I even buy that. JSON data in particular is typically multi-line, so how will you know w

Re: Emitting JSON to file using COPY TO

On Monday, November 27, 2023, Pavel Stehule wrote: > Hi > > po 27. 11. 2023 v 14:27 odesílatel David G. Johnston < > david.g.johns...@gmail.com> napsal: > >> On Monday, November 27, 2023, Dominique Devienne >> wrote: >> >>> There's even a JSON mode. >>> By miracle, would the JSON output mode rec

Re: Emitting JSON to file using COPY TO

Hi po 27. 11. 2023 v 14:27 odesílatel David G. Johnston < david.g.johns...@gmail.com> napsal: > On Monday, November 27, 2023, Dominique Devienne > wrote: > >> There's even a JSON mode. >> By miracle, would the JSON output mode recognize JSON[B] values, and >> avoid the escaping? >> > > I agree t

Re: Emitting JSON to file using COPY TO

On Monday, November 27, 2023, Dominique Devienne wrote: > There's even a JSON mode. > By miracle, would the JSON output mode recognize JSON[B] values, and avoid > the escaping? > I agree there should be a copy option for “not formatted” so if you dump a single column result in that format you ge

Re: Emitting JSON to file using COPY TO

On Mon, Nov 27, 2023 at 10:33 AM Dominique Devienne wrote: > On Sat, Nov 25, 2023 at 10:00 PM Adrian Klaver > wrote: > >> On 11/25/23 11:21, Davin Shearer wrote: >> > Hello! >> > >> > I'm trying to emit a JSON aggregation of JSON rows to a file using COPY >> > TO, but I'm running into problems w

Re: Emitting JSON to file using COPY TO

On Sat, Nov 25, 2023 at 10:00 PM Adrian Klaver wrote: > On 11/25/23 11:21, Davin Shearer wrote: > > Hello! > > > > I'm trying to emit a JSON aggregation of JSON rows to a file using COPY > > TO, but I'm running into problems with COPY TO double quoting the > > output. Here is a minimal example

Re: Emitting JSON to file using COPY TO

On 11/25/23 11:21, Davin Shearer wrote: Hello! I'm trying to emit a JSON aggregation of JSON rows to a file using COPY TO, but I'm running into problems with COPY TO double quoting the output.   Here is a minimal example that demonstrates the problem I'm having: I have tried to get COPY T

Re: Emitting JSON to file using COPY TO

On Sat, Nov 25, 2023 at 12:22 PM Davin Shearer wrote: > > Is there a way to emit JSON results to file from within postgres? > Use psql to directly output query results to a file instead of using COPY to output structured output in a format you don't want. David J.

Emitting JSON to file using COPY TO

Hello! I'm trying to emit a JSON aggregation of JSON rows to a file using COPY TO, but I'm running into problems with COPY TO double quoting the output. Here is a minimal example that demonstrates the problem I'm having: create table public.tbl_json_test (id int, t_test text); -- insert text tha