PostgreSQL: column is of type but expression is of type

What it means

In an INSERT or UPDATE, each value has to become the column’s type. PostgreSQL only does that on its own when an assignment cast exists, such as integer to bigint or numeric to integer. From text or varchar to integer, jsonb, boolean, uuid or an array there isn’t one, so it stops:

ERROR:  column "qty" is of type integer but expression is of type text
LINE 1: ...NSERT INTO seo_err_pg.events (id, qty) VALUES (1, '3'::text)...
                                                             ^
HINT:  You will need to rewrite or cast the expression.

The key word is typed. A bare literal like '3' has no type yet (“unknown”), and PostgreSQL reads it as an integer for an integer column, so VALUES (1, '3') works. It fails when the value already has a type: a column of a text table, a function result, or a parameter your driver declared as text.

Common causes

INSERT … SELECT from a staging table whose columns are all text, as CSV imports often are.
A driver that types every string parameter. JDBC’s setString() sends varchar by default, so a JSON string into a jsonb column fails with … but expression is of type character varying.
An integer into a boolean column (1 instead of true), common in code moved from MySQL.
A single value into an array column ('red' into text[]).
A CASE or coalesce that mixes types, so the whole expression comes out as text.

How to fix it

Cast the value in SQL

INSERT INTO events (id, happened_at, payload, qty, active)
SELECT id::integer, happened_at::timestamptz, payload::jsonb, qty::integer, active::boolean
FROM staging;

For parameters, cast the placeholder: VALUES ($1, $2::jsonb) or CAST(? AS jsonb). Any value that doesn’t convert then fails with invalid input syntax, which names it.

Send booleans and arrays as what they are

INSERT INTO events (id, active) VALUES (3, true);       -- or 1::boolean
UPDATE events SET tags = ARRAY['red'] WHERE id = 1;     -- or '{red}'

Let the driver send parameters untyped

With pgJDBC, the connection parameter stringtype=unspecified sends setString() values without a type, so the server infers it from the column, the same way it treats a bare literal. Casting the placeholder in the SQL works with any driver.

Fix the column type

If the staging table will be reused, give its columns their real types, or if the target column really holds text, change the target. Either way the casts disappear from your queries.

Reproduce it

On PostgreSQL 18.6, a typed table and a staging table of text:

CREATE TABLE seo_err_pg.events (id integer PRIMARY KEY, happened_at timestamptz, payload jsonb,
                                tags text[], qty integer, active boolean);
CREATE TABLE seo_err_pg.staging (id text, happened_at text, payload text, qty text, active text);
INSERT INTO seo_err_pg.staging VALUES ('1', '2026-10-01 09:00+00', '{"a":1}', '3', 'true');

INSERT INTO seo_err_pg.events (id, happened_at, payload, qty) SELECT id, happened_at, payload, qty FROM seo_err_pg.staging;

ERROR:  column "id" is of type integer but expression is of type text
LINE 1: ..._pg.events (id, happened_at, payload, qty) SELECT id, happen...
                                                             ^
HINT:  You will need to rewrite or cast the expression.

The first column that doesn’t fit is named. Other statements in the same session:

INSERT INTO seo_err_pg.events (id, active) VALUES (3, 1);
ERROR:  column "active" is of type boolean but expression is of type integer

UPDATE seo_err_pg.events SET tags = 'red'::text WHERE id = 1;
ERROR:  column "tags" is of type text[] but expression is of type text

PREPARE ins2(varchar) AS INSERT INTO seo_err_pg.events (id, payload) VALUES (6, $1);
ERROR:  column "payload" is of type jsonb but expression is of type character varying

The PREPARE with a varchar parameter is what a driver does when it types the parameter. Prepared without a type (PREPARE ins3 AS … VALUES (6, $1)), the same statement worked and stored {"b": 2}. VALUES (1, '3') worked, and the INSERT … SELECT with casts worked. With \set VERBOSITY verbose, psql shows the code: ERROR: 42804: column "payload" is of type jsonb but expression is of type text. PostgreSQL 14.24 gives the same messages.

In Inlet

When a statement fails, Inlet shows the error at the position the server reports, with PostgreSQL’s hint, and the structure editor shows each column’s type. With your own Anthropic API key, Ask Claude (Pro, ⌘L) can fix the failed statement; it sends the schema, the SQL and the error, never rows.

column is of type but expression is of type