[FIXED] Loading a non-materialised array from Postgres in Java

Issue

The setting: I am connecting to a Postgres database with Java, and trying to load the pg_stats.histogram_bounds statistic, which is an array in Postgres. I can extract the field as a sql.Array object with Array histogramBounds = rs.getArray("histogram_bounds");. This object can be printed with toString(), but I can't otherwise access any of the data (e.g. calls to histogramBounds.getArray(), getBaseType, etc.) all generate PSQLException: No results were returned by the query..

The documentation, as well as various tutorials and SE questions suggest this should be valid.

Apparently sql.Array objects do not hold the array data directly, but point to it on the server. From my experiments I have the following MWE which shows the difference between an array value stored in a table in Postgres, and one which is somehow ephemeral or non-materialised (as I suspect pg_stats may be, being a view onto other internal tables).

The MWE: I have two queries, one which selects data form a table and works, and one which selects data directly from the query, and does not work. I can't see any reason in the documentation they should not both work, and I would like to get the second version working to get the Java array [1, 2, 3]. Confusingly, toString still displays the data correctly in the second case, so it must be loaded successfully at some point.

import java.sql.Array;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;

public class PostgresStatsExperiments {
    public static void main(String[] args) throws SQLException {
        Connection conn = DriverManager.getConnection("jdbc:postgresql://localhost/tmp", "admin", "admin");
        Statement stmt = conn.createStatement();

        ResultSet rs = stmt.executeQuery("SELECT name, my_data from array_test;"); // This one works
        //ResultSet rs = stmt.executeQuery("SELECT '{1, 2, 3}' AS my_data;"); // This one does not work.

        while (rs.next()) {
            Array myData = rs.getArray("my_data");
            System.out.println(myData);
            System.out.println(myData.getArray()); // PSQLException here in the second case
        }

        stmt.close();
        rs.close();
        conn.close();
    }
}

The table array_test looks like this:

create table array_test ( name varchar, my_data integer ARRAY[3] );
insert into array_test values ('Alice', '{1, 2, 3}');
insert into array_test values ('Bob', '{4, 5, 6}');

In the second case, I get the following output:

{1, 2, 3}
Exception in thread "main" org.postgresql.util.PSQLException: No results were returned by the query.
    at org.postgresql.jdbc2.TypeInfoCache.getPGArrayElement(TypeInfoCache.java:425)
    at org.postgresql.jdbc2.AbstractJdbc2Array.buildArray(AbstractJdbc2Array.java:540)
    at org.postgresql.jdbc2.AbstractJdbc2Array.getArrayImpl(AbstractJdbc2Array.java:171)
    at org.postgresql.jdbc2.AbstractJdbc2Array.getArray(AbstractJdbc2Array.java:128)
    at tmp.PostgresStatsExperiments.main(PostgresStatsExperiments.java:21)

I am using postgresql-9.3-1102-jdbc41.jar from Maven, and the problem is also the same with the latest 42.2.4.jre7.

Solution

In the query SELECT '{1, 2, 3}' AS my_data the column my_data is not any array. It's a string that happens to look like an array - but the single quotes denote a text (or varchar) value.

And because the column's data type is not an array, calling getArray() throws an exception.

If you want an array, you need to cast the text value to an array:

SELECT '{1, 2, 3}'::int[] AS my_data;

or better: use an explicit array constructor:

SELECT array[1, 2, 3] AS my_data;

---

pg_stats.histogram_bounds is defined as anyarray - it's not a typed array as it contains different data types for each column (which means in each row, as that table contains one row per table column).

Apparently the JDBC driver can't work with anyarray properly.

To deal with the values from Java code, I think the easiest way is to convert it to a string which then can be converted back to a text array (as the string representation is correct).

So if you use the following query:

select schemaname, tablename, attname, histogram_bounds::text::text[] as histogram_bounds, ..
from pg_stats
where ...

You should be able to retrieve the content of the histogram_bounds column (but you get everything as strings, rather than a data type matching the column's data type).

Answered By - a_horse_with_no_name
Answer Checked By - Timothy Miller (JavaFixing Admin)