On Mon, 2024-01-08 at 18:43 +0000, Dean Rasheed wrote: > I can see the appeal in this feature. However, as it stands, this > isn't compatible with copy format json, and I think it would need to > duplicate quite a lot of the JSON output code in client-side code to > make it compatible. > > Consider, for example: > > CREATE TABLE foo(col json); > INSERT INTO foo VALUES ('"str_value"'); > > copy foo to stdout with (format json) produces this: > > {"col":"str_value"} > > which is as expected. However, psql -Jc "select * from foo" produces > > [ > { "col": "\"str_value\"" } > ] > > The problem is, various datatypes such as boolean, number types, json, > and jsonb must not be quoted and escaped, since that would change them > to strings or double-encode them in the result.
I agree that such data types should not be double quoted. > And then there are > domain types built on top of those types, and arrays, etc. See, for > example, the logic in json_categorize_type(). I think that trying to > duplicate that client-side is doomed to failure. Perhaps. But maybe "printTableContent" could be extended to contain a boolean array "quote_for_json" that is set in "printTableAddHeader" based on the underlying data type, similar to how "aligns" is set now. Detecting array types might be a challenge. Domains might not be a problem, since "PQftype()" seems to return the base data type for domain values. Yours, Laurenz Albe