How do I migrate PostgreSQL strings to a text type?

Question

We're using a JPA annotated type that looks like this (groovy code):

@Entity
@EqualsAndHashCode
class TextNote extends Serializable {
    @Id Long id
    String text
}

When it was first written I was very new to JPA and wrote the SQL first, then made the annotated classes match the SQL. Reading up on PostgreSQL it seemed like the following was the table I wanted:

CREATE TABLE textnote (
    id bigint NOT NULL,
    text text
);

This worked, and we had tables that looked like this:

 id  |          text
-----+------------------------
 837 | really long text here

What I want to do now is correct the JPA Entity to look like this:

@Entity
@EqualsAndHashCode
class TextNote extends Serializable {
    @Id Long id
    @Lob String text
}

By adding the @Lob annotation the JPA provider (in my case, hibernate) could generate the DDL correctly for me in case we want to swap out databases. It also documents exactly what I want the text field to be. Now when a note gets created I see something like this:

 id  |          text
-----+------------------------
 837 | 33427

Which is fine for new notes, as when I read it in code using String getText() it returns the really long text. Honestly I don't know how PostgreSQL implements the text type, nor should I need to in theory. However our production database already has many notes stored using old code without the @Lob annotation. Running the new code on an existing database generates issues like this:

org.springframework.dao.DataIntegrityViolationException: Bad value for type long : not a number this time; SQL [n/a]; nested exception is org.hibernate.exception.DataException: Bad value for type long : not a number this time

For existing notes, is there a way in SQL to migrate the old notes to use @Lob and text type correctly? Thanks in advance.

Answer 1

Based on the Postgres large object docs it looks like you'd have to write each text chunk to a file and import it individually. Which isn't something you should be doing in SQL.

I don't know anything about JPA, but what does @Lob have to do with DDL or switching databases? You've completely changed the type of the column; what was wrong with Postgres's text type?

---

Closing the loop here so this isn't lost in comments:

The real problem was that @Lob creates a Postgres text column, but Hibernate treats it as a native Postgres "large object" which stores data elsewhere and leaves only an oid in the table (which was then being stored as text, as per the column type). This is not usually what you want for text.

OP's solution was to slap on @Type(type="org.hibernate.type.StringClobType") to force Hibernate to store regular text.

Postgres doesn't normally store text as a five-digit integer. :)

Answer 2

I am sure its late, but for anybody having the same problem in future.

I also faced a similar issue where I had old data in the columns of text directly in the columns not as OIDs. And when I was trying to use that data with upgraded application I too was getting

Bad value for type long

To resolve this I created this script. May be it can help somebody in future.

I got a big help from this post here

Answer 3

I use JPA to annotate my entities and hibernate to persist them. But I do not want to add hibernate specific annotations to my entities. Therefore I have a data jar containing the entities + annotations. Then in my implementation I specify the persistence.xml and add an 'CustomTypes.hbm.xml'. Which is auto-scanned or added through the mapping-file tag in the persistence.xml.

This mapping contains the types that I want to override from the basictyperegistry. In this case the materialized_clob type is used. Which I don't want because when I browse my database with a query tool I want to be able to view the actual contents directly. So I add:

<typedef name="materialized_clob" class="org.hibernate.type.TextType" />

To force the use of the specified type for all clobs without the need to add specific annotations in my data package.

You can review the mappings by logging org.hibernate.type.BasicTypeRegistry on DEBUG level.

It took me some time to figure this out and I hope this will help anyone facing the same issues. Because this will probably also solve your problem I thought it might be worth posting it here.