PostgreSQLエラー「column is of type jsonb but expression is of type character varying」の修正方法

エラーについて

PostgreSQLを長く使っていると、この厄介な壁にぶつかることがよくあります。jsonb型のカラムにデータを保存しようとした際、データベースから型不一致のエラーが返されることがあります。通常、以下のようなエラーが表示されます。

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

原因は単純です。アプリケーションはプレーンテキストの文字列を送信していますが、データベースはバイナリのJSONオブジェクトを期待しているからです。PostgreSQLは厳格なことで知られています。より寛容な他のデータベースとは異なり、代入時に文字列をJSONに変換すべきだと自動的に「推測」してくれることはありません。

なぜ発生するのか

PostgreSQLは、jsonbとcharacter varying（varchar）を全くの別物として扱います。たとえ文字列が '{"user_id": 101, "active": true}' のように有効なJSON形式に見えたとしても、エンジンはそれを単なる文字の並びとして認識します。クエリ内でその文字列を jsonb として解釈するように明示的に指示しない限り、操作は即座に失敗します。

これは通常、以下の3つの状況で発生します。

生（Raw）のSQLを記述し、パラメータを単純な文字列として渡している。
ORMの設定が誤っており、オブジェクトのプロパティがデフォルトで文字列型になっている。
データベースドライバが、PostgreSQLが要求する形式へのオブジェクトのシリアライズを処理していない。

解決策1：SQLでの明示的なキャスト

最も手っ取り早い修正方法は、::jsonb キャスト演算子を使用することです。これにより、入力データをどのように扱うべきかをPostgreSQLに正確に伝えます。実行時に varchar を jsonb オブジェクトに変換します。

生のSQLの例

文字列をそのまま送信するのは避けてください。

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

代わりに、値の末尾にキャスト演算子を追加します。

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

SQLインジェクションを防ぐためにパラメータ化クエリを使用している場合は、プレースホルダ自体にキャストを適用します。

-- これはNode.js (pg) や Python (psycopg2) で動作します
UPDATE users SET profile_data = $1::jsonb WHERE id = $2;

解決策2：アプリケーションコードでの型処理

問題はSQLではなく、言語のドライバがデータをどのように準備しているかにある場合もあります。ドライバが単なる文字列ではなく、JSONを送信していることを認識しているか確認する必要があります。

Node.js (pgドライバ)

pg ドライバは通常、JavaScriptオブジェクトを自動的に文字列化して適切に処理します。しかし、手作業で JSON.stringify(data) を呼び出すと、文字列を送信することになってしまいます。代わりにオブジェクトを直接渡してください。

// これはエラーの原因になります
const payload = JSON.stringify({ role: 'admin', level: 5 });
await client.query('INSERT INTO logs (data) VALUES ($1)', [payload]);

// これは正常に動作します
const payload = { role: 'admin', level: 5 };
await client.query('INSERT INTO logs (data) VALUES ($1)', [payload]);

Python (psycopg2)

Pythonでは、生の辞書（dict）や文字列を渡さないでください。psycopg2.extras の Json アダプタを使用してデータをラップします。これにより、ドライバに対して型変換を処理するよう明示的に指示できます。

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)])

解決策3：ORMモデル定義の更新

SequelizeやTypeORMなどのORMは、モデル定義に基づいてクエリを生成します。ORMがカラムを文字列だと認識していると、文字列式を送信してしまい、エラーが発生します。

Sequelizeの例

属性が JSONB として定義されていることを確認してください。ここで STRING や TEXT を使用するのはよくある間違いです。

const User = sequelize.define('User', {
  preferences: {
    type: DataTypes.JSONB // JSONBである必要があります
  }
});

TypeORMの例

TypeORMでは、カラム型のデコレータで明示的に指定します。

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

検証

修正を本番環境に反映する前に、ターミナルで psql を使用してカラム型を確認してください。

\d table_name

特定のフィールドの "Type" 列を確認します。そこが jsonb になっている必要があります。コードを更新したら、実際にデータをパースできるかテストクエリを実行して確認してください。

-- データがJSONBとして保存されていれば、'dark' が返されるはずです
SELECT profile_data->>'theme' FROM users WHERE id = 1;

予防策とプロのヒント

型の不一致は、構文の誤りというより深い問題を隠していることがよくあります。たとえ ::jsonb キャストを使用しても、文字列に引用符の欠落や末尾のカンマがある場合、PostgreSQLは invalid input syntax for type json エラーをスローします。

複雑なネスト構造をデバッグするときは、いつも JSON Formatter & Validator を使用しています。これにより、500文字の文字列の中では見つけるのが不可能な小さな構文エラーを特定できます。クライアント側で検証することで、失敗に終わる無駄なデータベースへのラウンドトリップを減らすことができます。

YAMLファイルからデータを移行する場合は、まず YAML to JSON Converter を使用してください。手動でフォーマットを変換するのはタイプミスの元であり、それらのタイプミスこそがPostgresの型エラーを引き起こす原因となります。