column is of type jsonb but expression is of type text

ERROR: column "doc" is of type jsonb but expression is of type character varying (SQLSTATE 42804, datatype_mismatch), with HINT: You will need to rewrite or cast the expression., means an INSERT or UPDATE tries to store a value whose type PostgreSQL already knows into a column of another type, and there is no cast it is allowed to apply on its own. A quoted literal is fine, because it has no type yet; a value that is already text or character varying — a typed parameter, a text column, a CASE — is not. The character varying variant comes from Java: pgjdbc’s setString on a jsonb column produced exactly this message.

We measured every case below on PostgreSQL 17.11, with pgjdbc 42.7.13 on OpenJDK 21 and psycopg 3.3.6. With integer, numeric and json columns a text or varchar value gave the same error; a bigint into bytea did too.

Why does a literal work but a parameter not?

Because a literal is unknown until PostgreSQL sees where it goes, and a parameter declared text is text. On a table with doc jsonb:

insert into t(doc) values ('{"a":1}');          -- INSERT 0 1
insert into t(doc) values ('{"a":1}'::text);
-- ERROR:  42804: column "doc" is of type jsonb but expression is of type text
-- HINT:  You will need to rewrite or cast the expression.
insert into t(doc) values ('{"a":1}'::varchar);
-- ERROR:  42804: column "doc" is of type jsonb but expression is of type character varying

prepare p1(text) as insert into t(doc) values ($1);
-- ERROR:  42804: column "doc" is of type jsonb but expression is of type text
prepare p3(unknown) as insert into t(doc) values ($1);  -- PREPARE; execute: INSERT 0 1
prepare p4 as insert into t(doc) values ($1);           -- PREPARE; execute: INSERT 0 1
prepare p5(text) as insert into t(doc) values ($1::jsonb);  -- PREPARE; execute: INSERT 0 1

Note where it fails: at PREPARE, not at EXECUTE. The error is about types, not values, so it comes before any data is looked at. A function is checked at a different moment: a language sql function with a text parameter failed already at CREATE FUNCTION, a PL/pgSQL function only on the first call. In psql 17, insert into t(doc) values ($1) \bind '{"a":1}' \g worked, as did the same with an integer column and '42'.

It is a different error from operator does not exist: uuid = text (42883), which is about a comparison in a WHERE. This one is about assignment: the target of an INSERT or SET. The literal-versus-typed rule is the same.

Why does JDBC setString fail on a jsonb column?

pgjdbc’s setString bound the value as character varying, which is what the error names. That is the cause of the character varying variant. With a PreparedStatement on insert into t(doc) values (?):

stringtype=unspecified is a connection setting, so it fixed both the jsonb and the integer statement without touching the code; Types.OTHER and ?::jsonb fix one statement each. How a Spring Boot connection is set up for row-level security is in Spring Boot, Hibernate and row-level security.

Does psycopg 3 have the same problem?

Not with a plain %s: psycopg 3 sends a str untyped (OID 0), so the insert worked, also with prepare=True. The binary placeholder %b and a parameter inside coalesce(%s, '{}') made it text and gave 42804, the same mechanism as in operator does not exist: uuid = text. Three cases did not work:

Pass a dict wrapped in Jsonb() rather than serialising it yourself; that also tells PostgreSQL the type.

Why does integer get ‘expression is of type text’?

The same rule, with a twist: a literal that is not a number gives a different error, because PostgreSQL did try the conversion.

For numeric(10,2) it was identical: '12.50' worked, '12.50'::varchar gave 42804 with character varying. A 42804 is about the type of the expression; a 22P02 after the cast is about the value itself. For the second kind see invalid input syntax for type uuid; the same 22P02, for a different type.

Why does a CASE or INSERT … SELECT fail?

Because a CASE is resolved to a type before it is assigned, and with only literals in its branches that type is text:

update t set doc = case when id = 1 then '{"b":2}' else '{}' end where id = 1;
-- ERROR:  42804: column "doc" is of type jsonb but expression is of type text
update t set doc = coalesce(null, '{"c":3}') where id = 1;
-- ERROR:  42804: column "doc" is of type jsonb but expression is of type text
update t set doc = case when id = 1 then '{"b":2}'::jsonb else '{}' end where id = 1;
-- UPDATE 1
update t set doc = (case when id = 1 then '{"b":2}' else '{}' end)::jsonb where id = 1;
-- UPDATE 1

Each branch on its own would have worked; the CASE and the coalesce did not. One cast in one branch, or around the whole expression, was enough.

INSERT … SELECT from a staging table with text columns failed the same way: insert into t(doc) select payload from src gave 42804 for jsonb, and select num for integer. select payload::jsonb and select num::integer worked.

What about bytea and bigint?

insert into t(b) values (42::bigint) into a bytea column gave 42804: column "b" is of type bytea but expression is of type bigint, with the same hint. There is no cast between the two; you have to decide what the bytes should be. The other direction is more forgiving: a jsonb value and the integer 42 into a varchar(20) column both worked without a cast. PostgreSQL converts any type to a string type through its text representation, and the CREATE CAST documentation says: “The automatic casts to string types are treated as assignment casts, while the automatic casts from string types are explicit-only.” That asymmetry is this whole error. The column length still applies: the same jsonb into a varchar(5) gave 22001: value too long for type character varying(5). Into a json column, '{"a":1}'::varchar gave the same 42804 as jsonb.

Other SQLSTATEs from class 42, such as 42883 and 42703, and what caused them in our measurements, are in PostgreSQL error codes: measured causes.

What we did not measure

Hibernate and JPA mappings of jsonb columns (@JdbcTypeCode, custom types). Node (pg), Go (pgx) and .NET drivers. pgjdbc’s prepareThreshold and server-side prepared statements across versions. PostgreSQL versions other than 17. UPDATE … FROM and MERGE.

All measurements are our own, on PostgreSQL 17.11 in a container, with pgjdbc 42.7.13 on OpenJDK 21.0.12 and psycopg 3.3.6, on 22 October 2026. The SQLSTATE name datatype_mismatch is from PostgreSQL 17, Appendix A: Error Codes, consulted on 22 October 2026, and the quote on automatic casts from CREATE CAST, read on the same day.