存储 json、jsonb、hstore、xml、enum、ipaddr 等失败，并显示 "column " x " is of type json but expression is of type

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对于文本和非文本数据类型之间的强制类型转换过于严格。它不允许从文本类型(如text或varchar (character varying))到类似文本的非文本类型(如json, xml等)进行隐式强制转换(SQL中没有CAST或::)。

PgJDBC驱动程序在调用setString分配参数时指定varchar的数据类型。如果列、函数参数等的数据库类型实际上不是varchar或text，而是另一种类型，则会得到类型错误。对于许多其他驱动程序和orm也是如此。

PgJDBC: stringtype=unspecified

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

All: 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实现自定义类型处理程序。当你使用本地Java类型很好地映射到PostgreSQL类型时，这是非常有用的，而不是使用String，尽管它也可以工作，如果你的ORM允许你使用注释等指定类型处理程序。

实现自定义类型处理程序的方法是特定于驱动程序、语言和orm的。下面是一个用于json的Java和Hibernate的示例。

PgJDBC: type handler using PGObject

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

PgJDBC: Type handler using 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