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

Edit
Report