JDBC insert real array

Question

I am attempting to insert a real array into a postgresql array:

the table definition is:

String sqlTable = "CREATE TABLE IF NOT EXISTS ccmBlock"
                + "   sampleId             INTEGER,"
                + "   block                REAL[])";

The insert is:

String sqlInsert = "INSERT INTO ccmBlock"
                 + "(sampleId, block) VALUES" 
                 + "(?,?)"; 
PreparedStatement preparedStatement = theConnection.prepareStatement(sqlInsert);

preparedStatement.setInt(1, 1); 

Object[] theArray = {.11f, .22f, .33f};
Array a = theConnection.createArrayOf("real", theArray);  
preparedStatement.setArray(2, a); 

I get a message: org.postgresql.util.PSQLException: Unable to find server array type for provided name real.

but on their documentation page: http://www.postgresql.org/docs/8.4/static/datatype-numeric.html

Table 8-2. Numeric Types

Name StorageSize Description Range

real 4 bytes variable-precision, inexact 6 decimal digits precision

Answer 1

The Postgresql JDBC driver has it's own idea about the naming of types. You can look them up in the TypeInfoCache class.

In your case, the correct name is float4, so the line would go:

Object[] theArray = {.11f, .22f, .33f};
Array a = theConnection.createArrayOf("float4", theArray); 

Props goes to @JBNizet for suggesting this registry in a similar question.