Updating ResultSets with SQL Array types in JDBC / PostgreSQL
arrays, java, jdbc, postgresql
Solution
Change "CHARACTER VARYING" to "varchar". The command-line `psql` client accepts the type name "CHARACTER VARYING", but the JDBC driver does not.
The source for `org.postgresql.jdbc2.TypeInfoCache` contains a list of accepted type names.
Consider part of the ambiguously-worded contract for `createArrayOf()`:
The typeName is a database-specific name which may be the name of a built-in type, a user-defined type or a standard SQL type supported by this database.
I always assumed driver implementors interpret the phrases "database-specific name" and "supported by this database" to mean "accept whatever you want". But maybe you could file this as a bug against the Postgres JDBC driver.
Good luck.
Problem
I'm trying to use a SQL Array type with PostgreSQL 8.4 and the JDBC4 driver. My column is defined as follows: ``` nicknames CHARACTER VARYING(255)[] NOT NULL ``` and I'm trying to update it thusly: ``` row.updateArray("nicknames", connection.createArrayOf("CHARACTER VARYING", p.getNicknames().toArray())); ``` (`p.getNicknames()` returns a `List<String>`) but I'm seeing: org.postgresql.util.PSQLException: Unable to find server array type for provided name CHARACTER VARYING. at org.postgresql.jdbc4.AbstractJdbc4Connection.createArrayOf(AbstractJdbc4Connection.java:67) at org.postgresql.jdbc4.Jdbc4Connection.createArrayOf(Jdbc4Connection.java:21) Unfortunately, the Array types don't seem to be well documented - I've not found mention of exactly how to do this for PostgreSQL anywhere :( Any ideas?