How do I migrate PostgreSQL strings to a text type?
jpa, postgresql
Solution
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. :)
Problem
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.