如何将PostgreSQL字符串迁移到文本类型?

编程入门行业动态更新时间:2024-10-23 11:19:56

本文介绍了如何将PostgreSQL字符串迁移到文本类型?的处理方法，对大家解决问题具有一定的参考价值，需要的朋友们下面随着小编来一起学习吧！问题描述

我们使用的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.

推荐答案

基于 Postgres大对象文档，看来您必须将每个文本块写入文件并分别导入.在SQL中这不是您应该做的事情.

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列，但是 Hibernate将其视为本地Postgres大对象"，该对象将数据存储在其他位置，并且仅在表中保留一个oid(然后将其存储为文本，例如每个列类型).这通常不是您想要的文本.

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.