On Tue, 9 Jan 2024 at 09:43, Christoph Berg <m...@debian.org> wrote: > > I can see we probably wouldn't want two different output formats named > json, but the general idea of "allow psql to format results as json of > strings" makes a lot of sense, so we should try to make it work. Does > it even have to be compatible? >
I would say that they should be compatible, in the sense that an external tool parsing the outputs should regard them as equal, but maybe there are different expectations for the two features. > I'll note that the current code uses PG's string representation of > strings which is meant to be round-trip safe when fed back into the > server. So quoted numeric values aren't a problem at all. (And that > part is fixable.) > I'm not sure that being round-trip safe is a necessary goal here, but again, it's about the expectations for the feature. I was imagining that the goal was to produce something that an external tool would parse, rather than something Postgres would read back in. So not quoting numeric values seems desirable to produce output that better reflects the semantic content of the data (though it doesn't affect it being round-trip safe). > The real problem here is that COPY json violates that by pulling json > values up one syntax level. "Normal" cases will be fixable by just > looking for json(b) and printing that unquoted. And composite types > with jsonb members... are these really only half-quoted?! > What to do with composites is an interesting point in question. COPY format json will turn a composite into a JSON object whose keys are the field names. That's useful if you want to use an external tool to parse the result and get at the individual fields, but it's not round-trip safe. OTOH, this patch outputs the Postgres string representation of the object, which might be round-trip safe, but is not very convenient for any other tool to read. Regards, Dean
