Bug in JDBC.driver CopyManager method when copying json output - Mailing list pgsql-jdbc

Dear JDBC-Devs,

i think i found an escaping bug that causes duplicate escaping with backslash when using jdbc drivers CopyManager with JSON results.

Sample Query:

select to_json( 'my name is "rabbit"'::text);

Executing this query as Statement

<java>

Statement statement = connection.createStatement()

ResultSet resultSet = statement.executeQuery("select to_json('my name is \"rabbit\"'::text)");

String result=resultSet.next().getString(1);// => "my name is \"rabbit\""

</java>

Execution of this query using CopyManager (see result file attachment)

This Execution does the duplicate escaping for JSON output causing invalid json text.

<java>

BaseConnection bc=(BaseConnection) connection;                                     

final CopyManager cm=new CopyManager(bc);

File someFile=new File("test.json");

try(FileWriter fw=new FileWriter(someFile)){

                cm.copyOut("COPY (select to_json('my name is \"rabbit\"'::text)) TO STDOUT WITH NULL AS ''", fw);

                // => my name is \\"rabbit\\" instead

}

</java>

My Driver:

<dependency>

           <groupId>org.postgresql</groupId>

           <artifactId>postgresql</artifactId>

           <version>42.2.5</version>

       </dependency>

My pg Server (Docker):

10.4 (Debian 10.4-2.pgdg90+1)

If that is required behavior I suggest to introduce another format “json” in addition to “text” and “csv”.

Kind regards

Gerson Stadlmann

Manager Software Development International

IFCO Systems Austria GmbH

Unterthalham Straße 2  |  4694 Ohlsdorf |  Austria
T: +43 (0)7612-787 782   |  M: +43 664 3855 154
gerson.stadlmann@ifco.com  |  www.ifco.com

A better supply chain serves us all. Let’s eat.