如何将PostgreSQL字符串迁移到文本类型? [英] How do I migrate PostgreSQL strings to a text type?

问题描述

我们使用的JPA注释类型如下所示(常规代码):

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

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

第一次编写它时，我对JPA还是很陌生，首先编写了SQL，然后使带注释的类与SQL匹配.在PostgreSQL上阅读后，似乎下面是我想要的表:

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

我现在想做的就是将JPA实体看起来像这样:

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
}

通过添加@Lob批注，如果我们要换出数据库，JPA提供程序(在我的情况下为休眠)可以为我正确生成DDL.它还准确记录了我希望文本字段是什么.现在，当创建便笺时，我会看到类似这样的内容:

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

这对于新笔记来说很好，因为当我使用String getText()在代码中读取它时，它会返回真正的长文本.老实说，我不知道PostgreSQL如何实现text类型，理论上我也不需要.但是，我们的生产数据库中已经有许多使用旧代码存储的注释，而没有@Lob注释.在现有数据库上运行新代码会产生如下问题:

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

对于现有注释，SQL中是否有一种方法可以迁移旧注释以正确使用@Lob和text类型?预先感谢.

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

推荐答案

基于

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.

我对JPA一无所知，但是@Lob与DDL或切换数据库有什么关系?您已经完全更改了列的类型； Postgres的text类型出了什么问题?

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:

真正的问题是@Lob创建了Postgres text列，但是

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的解决方案是在@Type(type="org.hibernate.type.StringClobType")上打耳光，以强制Hibernate存储常规文本.

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

Postgres通常不将文本存储为五位整数. :)

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

这篇关于如何将PostgreSQL字符串迁移到文本类型?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！