title: How do I migrate PostgreSQL strings to a text type?
body: <p>We're using a JPA annotated type that looks like this (groovy code):</p>
  
  <pre><code>@Entity
  @EqualsAndHashCode
  class TextNote extends Serializable {
      @Id Long id
      String text
  }
  </code></pre>
  
  <p>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:</p>
  
  <pre><code>CREATE TABLE textnote (
      id bigint NOT NULL,
      text text
  );
  </code></pre>
  
  <p>This worked, and we had tables that looked like this:</p>
  
  <pre><code> id  |          text
  -----+------------------------
   837 | really long text here
  </code></pre>
  
  <p>What I want to do now is correct the JPA Entity to look like this:</p>
  
  <pre><code>@Entity
  @EqualsAndHashCode
  class TextNote extends Serializable {
      @Id Long id
      @Lob String text
  }
  </code></pre>
  
  <p>By adding the <code>@Lob</code> 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:</p>
  
  <pre><code> id  |          text
  -----+------------------------
   837 | 33427
  </code></pre>
  
  <p>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 <code>text</code> type, nor should I need to in theory. However our production database already has many notes stored using old code without the <code>@Lob</code> annotation. Running the new code on an existing database generates issues like this: </p>
  
  <pre><code>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
  </code></pre>
  
  <p>For existing notes, is there a way in SQL to migrate the old notes to use <code>@Lob</code> and <code>text</code> type correctly? Thanks in advance.</p>