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

Java "floating points" literals are of type `double`, so 8 byte. Have you tried `{.11f, .22f, .33f}` for your array? — mabi, May 22 '14 at 17:24
The message is complaining that it can't find a real type for the server, not that the format of the array is wrong. I tried your suggestion and the result is the same. When I first tried this part of the application I used floats and I got lazy when I was trying to make it work. — Sting, May 22 '14 at 17:31
Ah, you're right, I missed that. However, the type `"real"` isn't known to the postgresql jdbc driver, apparently it's called [float4](http://grepcode.com/file/repo1.maven.org/maven2/postgresql/postgresql/9.0-801.jdbc4/org/postgresql/jdbc2/TypeInfoCache.java#TypeInfoCache.0types) — mabi, May 22 '14 at 17:39
That is correct, I googled everything I could think of, where did you find that? Especially when it creates the table correctly. — Sting, May 22 '14 at 17:42

score 7 · Answer 1 · edited May 23 '17 at 10:29

7

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.

edited May 23 '17 at 10:29

Community

1
1

answered May 22 '14 at 17:45

mabi

5,279
2
43
78

using `namedParameterJdbcTemplate..getJdbcOperations().execute((ConnectionCallback) con -> con.createArrayOf("float4", arr));` I still get `Unable to find server array type for provided name REAL.` - `org.postgres:postgresql:42.2.19` – Tobi Akinyemi Apr 18 '21 at 22:15

JDBC insert real array

1 Answers1

Linked