存储json，jsonb，hstore，xml，enum，ipaddr等失败，并显示"column" 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或失败，并显示以下错误:

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

...或

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

为什么?我该怎么办?

我正在使用JDBC(PgJDBC).

I'm using JDBC (PgJDBC).

这是通过Hibernate，JPA和各种其他抽象层发生的.

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

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

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.

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

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.

推荐答案

为什么会发生

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

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.

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

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 .这将覆盖将setString值作为varchar传递的默认行为，而是将其留给数据库以猜测"其数据类型.在几乎所有情况下，它都能按照您想要的方式进行操作，将字符串传递给要存储的类型的输入验证器.

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.

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

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.

例如

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类型很好地映射的本机Java类型而不是使用String时，此功能最有用，尽管如果您的ORM允许您使用注释等指定类型处理程序，它也可以工作.

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.

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

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

如果您在Java中使用本机Java类型，则可以扩展

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.

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

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.

问题:

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

• 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

• 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等失败，并显示"column" x".的类型是json，但表达式的类型是可变字符“的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！