7

Postgres JDBC driver seems to not handle UTF-8 clobs correctly. When you retrieve the clob the characters are not correct (you get ? marks for non ascii characters).

Supposedly this is a known issue:

Luckily for my application I generally avoid clobs so the fields that were clobs I have just made into really big varchars.

How are other people dealing with this limitation?

Adam Gent
  • 47,843
  • 23
  • 153
  • 203
  • I bet those characters didn't make it correctly into the database in the first place. I don't have any problems with UTF-8 characters and the JDBC driver. But it sure would be interesting what you think a "CLOB" in Postgres is. Especially as you converted that to varchar –  Feb 18 '11 at 17:19
  • @a_horse_with_no_name its not what I think a clob is in postgres its what hibernate thinks it should be :) – Adam Gent Feb 18 '11 at 17:28
  • ah that dreaded Hibernate thing again. –  Feb 18 '11 at 17:33
  • The fields look fine in the debugger before they go into the database then when they come back out nbsp (U+00A0) turn into >. – Adam Gent Feb 18 '11 at 17:34
  • the root cause is that [lob object seem to be stored in ascii with jdbc driver]. [lob object seem to be stored in ascii with jdbc driver]: http://stackoverflow.com/questions/9993701 – maxime Oct 03 '12 at 10:23

2 Answers2

7

Adding @Type(type="org.hibernate.type.StringClobType") to the clob description in hibernate solves it for me.

Zubzub
  • 782
  • 7
  • 18
  • I was trying to keep it to only JPA and not any Hibernate specifics +1 though. – Adam Gent Jul 06 '11 at 15:21
  • 3
    Normally I used the annotation @Type(type = "org.hibernate.type.TextType") but ran into problems deploying my application with Oracle as database. However, @Type(type="org.hibernate.type.StringClobType") works with Oracle and Postgres. Thank you. – liecno Jan 09 '12 at 11:26
0

clob? You mean text or varchar, PostgreSQL doesn't have clob datatype. The PostgreSQL JDBC driver doesn't have problems with that, it would be useless without UTF8 support. It works fine over here.

Frank Heikens
  • 117,544
  • 24
  • 142
  • 135
  • When I said clob I meant "text" as that is what Hibernate/JPA creates the column as for @Lob fields. Clob is what JDBC calls Postgres text fields. – Adam Gent Feb 18 '11 at 17:29
  • are you using Hibernate if so what are you annotating your fields as? – Adam Gent Feb 18 '11 at 17:36
  • We're using Hibernate and utf8. It's all pretty default, nothing special. The Java programmers did the original configuration, I can't help you with the annotations. Sorry – Frank Heikens Feb 18 '11 at 17:39