Updating ResultSets with SQL Array types in JDBC / PostgreSQL

Question

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?

Answer 1

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.