Tuesday, April 27, 2010

PostgreSQL Arrays and Java JDBC (always quote on insert)

I ran into a problem recently using PostgreSQL arrays with Java. I had a table where each row represented a sentence in a web page. One column contained the entire sentence. Another column contained an array of tokens that make up that sentence (as parsed by GATE).

While parsing a Wikipedia page, it contained the character: '†' (\u2020). This inserted fine as a sentence, but became {"?"} in the array. I used the class PostgreSQLTextArray from Valentine's tech log (thanks Google). The contents would not display in pgAdmin3. How in the world could the same character work to insert into a character varying field but not a character varying[] field?!

What I found out is that this character and many others need to be quoted to properly insert into a PostgreSQL array. I changed the Array class I was using to always quote characters.

No comments: