存储 json、jsonb、hstore、xml、enum、ipaddr 等失败并显示“列"x"是 json 类型，但表达式的类型是不同的字符"； [英] Storing json, jsonb, hstore, xml, enum, ipaddr, etc fails with "column "x" is of type json but expression is of type character varying"

问题描述

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

列the_col"的类型是 json 但表达式的类型是不同的

... 或

列the_col"的类型是 json 但表达式的类型是文本

为什么?我该怎么办?

我使用的是 JDBC (PgJDBC).

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

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

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

解决方案

为什么会这样

问题在于 PostgreSQL 对文本和非文本之间的转换过于严格数据类型.它不允许从 text 或 等文本类型进行隐式转换(SQL 中没有 CAST 或 ::)varchar(character变化)到类似文本的非文本类型，如json、xml等

当您调用 varchar 时，PgJDBC 驱动程序指定了 varchar 的数据类型code>setString 分配一个参数.如果列的数据库类型、函数参数等实际上不是 varchar 或 text，而是另一种类型，则会出现类型错误.许多其他驱动程序和 ORM 也是如此.

PgJDBC:stringtype=unspecified

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

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

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

例如

CREATE OR REPLACE FUNCTION json_intext(text) 返回 json AS $$选择 json_in($1::cstring);$$ 语言 SQL 不可变；CREATE CAST(文本为 json)WITH FUNCTION json_intext(text) 作为隐式；

全部:自定义类型处理程序

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

实现自定义类型处理程序的方法是特定于驱动程序、语言和 ORM 的.以下是 json 的 Java 和 Hibernate 示例.

PgJDBC:使用 PGObject

的类型处理程序

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

PgJDBC:使用 setObject(int, Object)

的类型处理程序

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

另见

问题:

• 将 postgreSQL JSON 列映射到 Hibernate 值类型
• 是否可以使用 JPA (EclipseLink) 自定义类型?

外部:

• http://www.postgresql.org/message-id/[e-mail]
• https://github.com/pgjdbc/pgjdbc/issues/265
• http://www.pateldenish.com/2013/05/inserting-json-data-into-postgres-using-jdbc-driver.html

When using PostgreSQL to store data in a field of a string-like validated type, like xml, json, jsonb, xml, ltree, etc, the INSERT or UPDATE fails with an error like:

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

... or

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

Why? What can I do about it?

I'm using JDBC (PgJDBC).

This happens via Hibernate, JPA, and all sorts of other abstraction layers.

The "standard" advice from the PostgreSQL team is to use a CAST in the SQL. This is not useful for people using query generators or ORMs, especially if those systems don't have explicit support for database types like json, so they're mapped via String in the application.

Some ORMs permit the implementation of custom type handlers, but I don't really want to write a custom handler for each data type for each ORM, e.g. json on Hibernate, json on EclipseLink, json on OpenJPA, xml on Hibernate, ... etc. There's no JPA2 SPI for writing a generic custom type handler. I'm looking for a general solution.

解决方案

Why it happens

The problem is that PostgreSQL is overly strict about casts between text and non-text data types. It will not allow an implicit cast (one without a CAST or :: in the SQL) from a text type like text or varchar (character varying) to a text-like non-text type like json, xml, etc.

The PgJDBC driver specifies the data type of varchar when you call setString to assign a parameter. If the database type of the column, function argument, etc, is not actually varchar or text, but instead another type, you get a type error. This is also true of quite a lot of other drivers and ORMs.

PgJDBC: stringtype=unspecified

The best option when using PgJDBC is generally to pass the parameter stringtype=unspecified. This overrides the default behaviour of passing setString values as varchar and instead leaves it up to the database to "guess" their data type. In almost all cases this does exactly what you want, passing the string to the input validator for the type you want to store.

All: CREATE CAST ... WITH FUNCTION ...

You can instead CREATE CAST to define a data-type specific cast to permit this on a type-by-type basis, but this can have side effects elsewhere. If you do this, do not use WITHOUT FUNCTION casts, they will bypass type validation and result in errors. You must use the input/validation function for the data type. Using CREATE CAST is suitable for users of other database drivers that don't have any way to stop the driver specifying the type for string/text parameters.

e.g.

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;

All: Custom type handler

If your ORM permits, you can implement a custom type handler for the data type and that specific ORM. This mostly useful when you're using native Java type that maps well to the PostgreSQL type, rather than using String, though it can also work if your ORM lets you specify type handlers using annotations etc.

Methods for implementing custom type handlers are driver-, language- and ORM-specific. Here's an example for Java and Hibernate for json.

PgJDBC: type handler using PGObject

If you're using a native Java type in Java, you can extend PGObject to provide a PgJDBC type mapping for your type. You will probably also need to implement an ORM-specific type handler to use your PGObject, since most ORMs will just call toString on types they don't recognise. This is the preferred way to map complex types between Java and PostgreSQL, but also the most complex.

PgJDBC: Type handler using setObject(int, Object)

If you're using String to hold the value in Java, rather than a more specific type, you can invoke the JDBC method setObject(integer, Object) to store the string with no particular data type specified. The JDBC driver will send the string representation, and the database will infer the type from the destination column type or function argument type.

See also

Questions:

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

External:

• http://www.postgresql.org/message-id/[e-mail]
• https://github.com/pgjdbc/pgjdbc/issues/265
• http://www.pateldenish.com/2013/05/inserting-json-data-into-postgres-using-jdbc-driver.html

这篇关于存储 json、jsonb、hstore、xml、enum、ipaddr 等失败并显示“列"x"是 json 类型，但表达式的类型是不同的字符"；的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！