How to Fix PostgreSQL Error: "column is of type jsonb but expression is of type character varying"

The Error

If you've spent much time with PostgreSQL, you’ve likely hit this frustrating roadblock. You try to save data to a jsonb column, but the database pushes back with a type mismatch error. It usually looks like this:

ERROR: column "metadata" is of type jsonb but expression is of type character varying
HINT: You will need to rewrite or cast the expression.

The root cause is simple: your application is sending a plain text string, but the database expects a binary JSON object. PostgreSQL is notoriously strict. Unlike more permissive databases, it won't automatically "guess" that your string should be converted into JSON during an assignment.

Why It Happens

PostgreSQL treats jsonb and character varying (varchar) as completely different animals. Even if your string looks like valid JSON—for example, '{"user_id": 101, "active": true}'—the engine sees it only as a sequence of characters. If your query doesn't explicitly tell the database to interpret that string as jsonb, the operation fails instantly.

This usually crops up in three specific situations:

• You are writing raw SQL and passing parameters as simple strings.
• Your ORM is misconfigured and defaults to string types for object properties.
• Your database driver doesn't handle the serialization of objects into the format PostgreSQL requires.

Solution 1: Explicit Casting in SQL

The fastest fix involves the ::jsonb cast operator. This tells PostgreSQL exactly how to handle the incoming data. It turns a varchar into a jsonb object on the fly.

Raw SQL Example

Avoid sending the string as-is:

INSERT INTO users (profile_data) VALUES ('{"theme": "dark", "notifications": true}');

Instead, add the cast operator to the end of your value:

INSERT INTO users (profile_data) VALUES ('{"theme": "dark", "notifications": true}'::jsonb);

If you are using parameterized queries to prevent SQL injection, apply the cast to the placeholder itself:

-- This works for Node.js (pg) or Python (psycopg2)
UPDATE users SET profile_data = $1::jsonb WHERE id = $2;

Solution 2: Handling Types in Application Code

Sometimes the problem isn't the SQL, but how your language driver prepares the data. You need to ensure the driver knows it's sending JSON, not just a string.

Node.js (pg driver)

The pg driver usually handles JavaScript objects correctly by stringifying them automatically. However, if you manually call JSON.stringify(data), you are sending a string. Pass the object directly instead:

// This causes the error
const payload = JSON.stringify({ role: 'admin', level: 5 });
await client.query('INSERT INTO logs (data) VALUES ($1)', [payload]);

// This works perfectly
const payload = { role: 'admin', level: 5 };
await client.query('INSERT INTO logs (data) VALUES ($1)', [payload]);

Python (psycopg2)

In Python, don't pass a raw dictionary or a string. Use the Json adapter from psycopg2.extras to wrap your data. This explicitly tells the driver to handle the type conversion for you.

import psycopg2
from psycopg2.extras import Json

user_settings = {"notifications": "enabled", "retry_count": 3}
cursor.execute("INSERT INTO config (settings) VALUES (%s)", [Json(user_settings)])

Solution 3: Updating ORM Model Definitions

ORMs like Sequelize or TypeORM rely on your model definitions to generate queries. If the ORM thinks a column is a string, it will send a string expression, triggering the error.

Sequelize Example

Ensure your attribute is defined as JSONB. Using STRING or TEXT here is a common mistake.

const User = sequelize.define('User', {
  preferences: {
    type: DataTypes.JSONB // Must be JSONB
  }
});

TypeORM Example

In TypeORM, be explicit with the column type decorator:

@Column({ type: 'jsonb' })
metadata: Record<string, any>;

Verification

Before you push a fix to production, verify the column type in your terminal using psql:

\d table_name

Check the "Type" column for your specific field. It must say jsonb. Once you've updated your code, run a test query to ensure you can actually parse the data:

-- This should return 'dark' if the data was stored as JSONB
SELECT profile_data->>'theme' FROM users WHERE id = 1;

Prevention and Pro-Tips

Type mismatches often hide a deeper problem: invalid syntax. Even with the ::jsonb cast, if your string has a missing quote or a trailing comma, PostgreSQL will throw an invalid input syntax for type json error.

When I'm debugging complex nested structures, I always use a JSON Formatter & Validator. It catches the small syntax errors that are impossible to spot in a 500-character string. Validating on the client side saves you from unnecessary database round-trips that end in failure.

If you are migrating data from YAML files, use a YAML to JSON Converter first. Converting formats manually is a recipe for typos, and those typos are exactly what trigger these Postgres type errors.