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.

Eevee · Accepted Answer · 2013-01-25T23:38:52.900

13

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. :)

edited Jan 25 '13 at 23:38

answered Jan 25 '13 at 00:10

Eevee

47,412
11
95
127

Without that annotation, the JPA provider would generate a table with column type varchar, which isn't what I want. `@Lob` generates the PostgreSQL `text` type, which is exactly what I want. If I wanted to switch databases, I would want to use the JPA provider to generate the DDL for me. `@Lob` is necessary in order to do that. Make sense? – Joe Jan 25 '13 at 00:15
are you sure `@Lob` is generating `text`? both the documentation and your posted output make me think it would generate `oid`—which is how Postgres stores large objects. if it were truly `text`, you would just see _text_, not a mystery number. – Eevee Jan 25 '13 at 00:19
I'm positive, here is the table it outputs with the `@Lob` annotation: [gist](https://gist.github.com/4630173) – Joe Jan 25 '13 at 00:22
this might be [bozotic behavior in Hibernate](http://www.shredzone.de/cilla/page/299/string-lobs-on-postgresql-with-hibernate-36.html). you're generating a `text` column but it's being nonsensically treated as a large-object column; the text _should_ appear in the table. (postgres absolutely does not store regular text as numbers.) that article links to [this SO question](http://stackoverflow.com/questions/3677380/proper-hibernate-annotation-for-byte) which is having a similar problem. – Eevee Jan 25 '13 at 00:29
good point about the bozotic behavior in Hibernate. I added the non-standard Hibernate annotation `@Type(type="org.hibernate.type.StringClobType")` and the text is stored human-readable again. I guess the original question was based on false assumptions about PostgreSQL, so thanks for setting me straight! :-) – Joe Jan 25 '13 at 15:56
not to impose, but i don't suppose you could accept this answer? :) – Eevee Aug 07 '13 at 18:30

score 8 · Answer 2 · answered Jun 25 '13 at 12:19

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.

score 2 · Answer 3 · edited May 23 '17 at 12:01

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

vp8106 · Answer 4 · 2014-09-30T11:24:11.250

If using spring you can create LocalSessionFactoryBean descendant to inject Hibernate type overrides. See example:

 public class CustomSessionFactoryBean extends LocalSessionFactoryBean {

    @Override
    protected SessionFactory buildSessionFactory(LocalSessionFactoryBuilder sfb) {
        // To store @Lob annotated Strings in TEXT fields.
        // By default for Postgres generated TEXT column, but stored OID of Postgres LOB object
        sfb.registerTypeOverride(new TextType() {
            @Override
            public String getName() {
                return StandardBasicTypes.MATERIALIZED_CLOB.getName();
            }
        });
        sfb.registerTypeOverride(new NTextType() {
            @Override
            public String getName() {
                return StandardBasicTypes.MATERIALIZED_NCLOB.getName();
            }
        });

        super.buildSessionFactory(sfb);
    }
 }

You don't need to use Hibernate specific annotation @Type, just annotate String property with @Lob and use CustomSessionFactoryBean

How do I migrate PostgreSQL strings to a text type?

4 Answers4

Linked