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?

Dan LaRocque · Accepted Answer · 2010-04-26 19:54:05Z

8

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.

answered Apr 26, 2010 at 19:54

Dan LaRocque

5,1932 gold badges24 silver badges16 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Steven Schlansker Over a year ago

Ach, you're right. I assumed "database-specific" meant "whatever the DB uses" which as you point out is wrong. Also worth noting that it seems to be case sensitive, unlike just about everything else. Thanks!

Collectives™ on Stack Overflow

Updating ResultSets with SQL Array types in JDBC / PostgreSQL

1 Answer 1

1 Comment

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Linked

Related