postgresql 存储 json、jsonb、hstore、xml、enum、ipaddr 等失败，“列“x”的类型为 json 但表达式的类型为不同的字符”
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/32238884/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Storing json, jsonb, hstore, xml, enum, ipaddr, etc fails with "column "x" is of type json but expression is of type character varying"
提问by Craig Ringer
When using PostgreSQL to store data in a field of a string-like validated type, like xml, json, jsonb, xml, ltree, etc, the INSERTor UPDATEfails with an error like:
当的场使用PostgreSQL存储数据绳状验证的类型，比如xml，json，jsonb，xml，ltree，等等，所述INSERT或UPDATE失败等错误：
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).
我正在使用 JDBC (PgJDBC)。
This happens via Hibernate, JPA, and all sorts of other abstraction layers.
这通过 Hibernate、JPA 和各种其他抽象层发生。
The "standard" advice from the PostgreSQL team is to use a CASTin 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 Stringin the application.
PostgreSQL 团队的“标准”建议是CAST在 SQL 中使用 a 。这对于使用查询生成器或 ORM 的人没有用，特别是如果这些系统没有明确支持像 那样的数据库类型json，因此它们String在应用程序中通过映射。
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.
一些 ORM 允许实现自定义类型处理程序，但我真的不想为每个 ORM 的每个数据类型编写自定义处理程序，例如 Hibernate 上的 json、EclipseLink 上的 json、OpenJPA 上的 json、Hibernate 上的 xml，...等等。没有用于编写通用自定义类型处理程序的 JPA2 SPI。我正在寻找一个通用的解决方案。
回答by Craig Ringer
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 CASTor ::in the SQL) from a text type like textor varchar(character varying) to a text-like non-text type like json, xml, etc.
问题是PostgreSQL 对文本和非文本数据类型之间的转换过于严格。它不会允许隐式转换（一个没有CAST或::从文本类型像SQL）text或varchar（character varying）的文本样非文本类型等json，xml等。
The PgJDBC driver specifies the data type of varcharwhen you call setStringto assign a parameter. If the database type of the column, function argument, etc, is not actually varcharor text, but instead another type, you get a type error. This is also true of quite a lot of other drivers and ORMs.
PgJDBC 驱动程序指定varchar调用setString分配参数时的数据类型。如果列、函数参数等的数据库类型实际上不是varcharor text，而是另一种类型，则会出现类型错误。许多其他驱动程序和 ORM 也是如此。
PgJDBC: stringtype=unspecified
PgJDBC： stringtype=unspecified
The best option when using PgJDBC is generally to pass the parameter stringtype=unspecified. This overrides the default behaviour of passing setStringvalues as varcharand 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.
使用 PgJDBC 时的最佳选择通常是传递参数stringtype=unspecified。这会覆盖传递setString值的默认行为，varchar而是让数据库“猜测”它们的数据类型。在几乎所有情况下，这完全符合您的要求，将字符串传递给您要存储的类型的输入验证器。
All: CREATE CAST ... WITH FUNCTION ...
全部： CREATE CAST ... WITH FUNCTION ...
You can instead CREATE CASTto 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 notuse WITHOUT FUNCTIONcasts, they will bypass type validation and result in errors. You must use the input/validation function for the data type. Using CREATE CASTis 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 CAST定义特定于数据类型的强制转换，以逐个类型地允许这样做，但这可能会在其他地方产生副作用。如果这样做，请不要使用WITHOUT FUNCTION强制转换，它们将绕过类型验证并导致错误。您必须对数据类型使用输入/验证功能。使用CREATE CAST适用于其他数据库驱动程序的用户，这些驱动程序无法停止指定字符串/文本参数类型的驱动程序。
e.g.
例如
CREATE OR REPLACE FUNCTION json_intext(text) RETURNS json AS $$
SELECT json_in(::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.
如果您的 ORM 允许，您可以为数据类型和特定的 ORM 实现自定义类型处理程序。当您使用很好地映射到 PostgreSQL 类型的本机 Java 类型而不是使用 时String，这非常有用，但如果您的 ORM 允许您使用注释等指定类型处理程序，它也可以工作。
Methods for implementing custom type handlers are driver-, language- and ORM-specific. Here's an example for Java and Hibernate for json.
实现自定义类型处理程序的方法是特定于驱动程序、语言和 ORM 的。这是 Java 和 Hibernate for 的示例json。
PgJDBC: type handler using PGObject
PgJDBC：类型处理程序使用 PGObject
If you're using a native Java type in Java, you can extend PGObjectto 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 toStringon types they don't recognise. This is the preferred way to map complex types between Java and PostgreSQL, but also the most complex.
如果您在 Java 中使用本机 Java 类型，则可以扩展PGObject为您的类型提供 PgJDBC 类型映射。您可能还需要实现一个特定于 ORM 的类型处理程序来使用您的PGObject，因为大多数 ORM 只会调用toString它们无法识别的类型。这是在 Java 和 PostgreSQL 之间映射复杂类型的首选方式，但也是最复杂的方式。
PgJDBC: Type handler using setObject(int, Object)
PgJDBC：使用类型处理程序 setObject(int, Object)
If you're using Stringto 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.
如果您使用StringJava 来保存值，而不是更具体的类型，则可以调用 JDBC 方法setObject(integer, Object)来存储没有指定特定数据类型的字符串。JDBC 驱动程序将发送字符串表示，数据库将从目标列类型或函数参数类型推断类型。
See also
也可以看看
Questions:
问题：
External:
外部的：
