json - 存储 json、jsonb、hstore、xml、enum、ipaddr 等失败，显示 "column "x“是 json 类型，但表达式是字符类型变化”

Question

当使用 PostgreSQL 将数据存储在类字符串验证类型的字段中时，如 xml、json、jsonb、xml、ltree 等，INSERT 或 UPDATE 失败并出现如下错误:

column "the_col" is of type json but expression is of type character varying

...或者

column "the_col" is of type json but expression is of type text

为什么？我该怎么办？

我正在使用 JDBC (PgJDBC)。

这是通过 Hibernate、JPA 和各种其他抽象层实现的。

PostgreSQL 团队的“标准”建议是在 SQL 中使用 CAST。这对使用查询生成器或 ORM 的人没有用，特别是如果这些系统没有明确支持数据库类型，如 json，因此它们通过 String 映射到应用程序。

有些 ORM 允许自定义类型处理程序的实现，但我真的不想为每个 ORM 的每个数据类型编写自定义处理程序，例如Hibernate 上的 json、EclipseLink 上的 json、OpenJPA 上的 json、Hibernate 上的 xml 等等。没有用于编写通用自定义类型处理程序的 JPA2 SPI。我正在寻找通用解决方案。

Answer 1

为什么会这样

问题是 PostgreSQL is overly strict about casts between text and non-text data types .它不允许从文本类型(如 text 或 )进行隐式转换(在 SQL 中没有 CAST 或 ::) varchar(character varying)到类似文本的非文本类型，如 json、xml 等

The PgJDBC driver specifies the data type of varchar when you call setString to assign a parameter .如果列、函数参数等的数据库类型实际上不是 varchar 或 text，而是另一种类型，则会出现类型错误。许多其他驱动程序和 ORM 也是如此。

PgJDBC: stringtype=unspecified

使用 PgJDBC 时的最佳选择通常是 pass the parameter stringtype=unspecified .这会覆盖将 setString 值作为 varchar 传递的默认行为，而是让数据库“猜测”它们的数据类型。在几乎所有情况下，这都完全符合您的要求，将字符串传递给您要存储的类型的输入验证器。

全部:CREATE CAST ... WITH FUNCTION ...

你可以改为 CREATE CAST定义特定于数据类型的强制转换以允许逐个类型地进行此操作，但这可能会在其他地方产生副作用。如果这样做，请不要使用WITHOUT FUNCTION 转换，它们会绕过类型验证并导致错误。您必须使用数据类型的输入/验证函数。使用 CREATE CAST 适用于其他数据库驱动程序的用户，这些用户无法通过任何方式停止指定字符串/文本参数类型的驱动程序。

例如

CREATE OR REPLACE FUNCTION json_intext(text) RETURNS json AS $$
SELECT json_in($1::cstring); 
$$ LANGUAGE SQL IMMUTABLE;

CREATE CAST (text AS json) 
WITH FUNCTION json_intext(text) AS IMPLICIT;

全部:自定义类型处理器

如果您的 ORM 允许，您可以为数据类型和特定的 ORM 实现自定义类型处理程序。这在您使用可以很好地映射到 PostgreSQL 类型的 native Java 类型而不是使用 String 时非常有用，尽管如果您的 ORM 允许您使用注释等指定类型处理程序，它也可以工作。

实现自定义类型处理程序的方法是特定于驱动程序、语言和 ORM 的。 Here's an example for Java and Hibernate for json .

PgJDBC:使用 PGObject 的类型处理程序

如果您在 Java 中使用 native Java 类型，则可以扩展 PGObject为您的类型提供 PgJDBC 类型映射。您可能还需要实现特定于 ORM 的类型处理程序才能使用您的 PGObject，因为大多数 ORM 只会对它们无法识别的类型调用 toString。这是在 Java 和 PostgreSQL 之间映射复杂类型的首选方式，但也是最复杂的方式。

PgJDBC:使用 setObject(int, Object) 的类型处理程序

如果您使用 String 来保存 Java 中的值，而不是更具体的类型，您可以调用 JDBC 方法 setObject(integer, Object) 来存储未指定特定数据类型的字符串。 JDBC 驱动程序将发送字符串表示，数据库将从目标列类型或函数参数类型推断类型。

另见

问题:

• Mapping postgreSQL JSON column to Hibernate value type
• Are JPA (EclipseLink) custom types possible?

外部:

• http://www.postgresql.org/message-id/54096082.1090009@2ndquadrant.com
• https://github.com/pgjdbc/pgjdbc/issues/265
• http://www.pateldenish.com/2013/05/inserting-json-data-into-postgres-using-jdbc-driver.html