Storing json, jsonb, hstore, xml, enum, ipaddr, etc fails with "column "x" is of type json but expression is of type character varying"

Question

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.

Answer 1

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/[email protected]
• https://github.com/pgjdbc/pgjdbc/issues/265
• http://www.pateldenish.com/2013/05/inserting-json-data-into-postgres-using-jdbc-driver.html