Cách khắc phục lỗi PostgreSQL: "column is of type jsonb but expression is of type character varying"

Lỗi

Nếu bạn đã dành nhiều thời gian làm việc với PostgreSQL, có lẽ bạn đã từng gặp phải rào cản khó chịu này. Bạn cố gắng lưu dữ liệu vào cột jsonb, nhưng cơ sở dữ liệu từ chối với lỗi không khớp kiểu dữ liệu. Nó thường trông như thế này:

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

Nguyên nhân gốc rễ rất đơn giản: ứng dụng của bạn đang gửi một chuỗi văn bản thuần túy, nhưng cơ sở dữ liệu lại mong đợi một đối tượng JSON nhị phân. PostgreSQL nổi tiếng là khắt khe. Không giống như các cơ sở dữ liệu dễ dãi hơn, nó sẽ không tự động "đoán" rằng chuỗi của bạn nên được chuyển đổi thành JSON trong quá trình gán.

Tại sao lỗi này xảy ra

PostgreSQL coi jsonb và character varying (varchar) là hai thực thể hoàn toàn khác biệt. Ngay cả khi chuỗi của bạn trông giống như một JSON hợp lệ—ví dụ: '{"user_id": 101, "active": true}'—công cụ này vẫn chỉ xem nó như một chuỗi các ký tự. Nếu truy vấn của bạn không yêu cầu cơ sở dữ liệu diễn giải chuỗi đó là jsonb một cách rõ ràng, thao tác sẽ thất bại ngay lập tức.

Điều này thường xảy ra trong ba tình huống cụ thể:

• Bạn đang viết SQL thuần và truyền các tham số dưới dạng chuỗi đơn giản.
• ORM của bạn bị cấu hình sai và mặc định các thuộc tính đối tượng là kiểu chuỗi.
• Trình điều khiển cơ sở dữ liệu của bạn không xử lý việc tuần tự hóa các đối tượng sang định dạng mà PostgreSQL yêu cầu.

Giải pháp 1: Ép kiểu rõ ràng trong SQL

Cách khắc phục nhanh nhất liên quan đến toán tử ép kiểu ::jsonb. Điều này cho PostgreSQL biết chính xác cách xử lý dữ liệu đầu vào. Nó sẽ chuyển đổi một varchar thành một đối tượng jsonb ngay lập tức.

Ví dụ SQL thuần

Tránh gửi chuỗi nguyên bản:

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

Thay vào đó, hãy thêm toán tử ép kiểu vào cuối giá trị của bạn:

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

Nếu bạn đang sử dụng các truy vấn có tham số để ngăn chặn SQL injection, hãy áp dụng ép kiểu cho chính trình giữ chỗ (placeholder):

-- Cách này hoạt động với Node.js (pg) hoặc Python (psycopg2)
UPDATE users SET profile_data = $1::jsonb WHERE id = $2;

Giải pháp 2: Xử lý kiểu dữ liệu trong mã ứng dụng

Đôi khi vấn đề không nằm ở SQL, mà ở cách trình điều khiển ngôn ngữ của bạn chuẩn bị dữ liệu. Bạn cần đảm bảo trình điều khiển biết rằng nó đang gửi JSON, chứ không chỉ là một chuỗi.

Node.js (trình điều khiển pg)

Trình điều khiển pg thường xử lý các đối tượng JavaScript một cách chính xác bằng cách tự động chuyển chúng thành chuỗi. Tuy nhiên, nếu bạn gọi JSON.stringify(data) theo cách thủ công, bạn đang gửi một chuỗi. Thay vào đó, hãy truyền trực tiếp đối tượng:

// Điều này gây ra lỗi
const payload = JSON.stringify({ role: 'admin', level: 5 });
await client.query('INSERT INTO logs (data) VALUES ($1)', [payload]);

// Cách này hoạt động hoàn hảo
const payload = { role: 'admin', level: 5 };
await client.query('INSERT INTO logs (data) VALUES ($1)', [payload]);

Python (psycopg2)

Trong Python, đừng truyền một dictionary thuần hoặc một chuỗi. Hãy sử dụng adapter Json từ psycopg2.extras để bao bọc dữ liệu của bạn. Điều này yêu cầu trình điều khiển xử lý việc chuyển đổi kiểu dữ liệu cho bạn một cách rõ ràng.

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

Giải pháp 3: Cập nhật định nghĩa mô hình ORM

Các ORM như Sequelize hoặc TypeORM dựa vào định nghĩa mô hình của bạn để tạo truy vấn. Nếu ORM nghĩ rằng một cột là một chuỗi, nó sẽ gửi một biểu thức chuỗi, gây ra lỗi.

Ví dụ Sequelize

Đảm bảo thuộc tính của bạn được định nghĩa là JSONB. Sử dụng STRING hoặc TEXT ở đây là một sai lầm phổ biến.

const User = sequelize.define('User', {
  preferences: {
    type: DataTypes.JSONB // Phải là JSONB
  }
});

Ví dụ TypeORM

Trong TypeORM, hãy xác định rõ ràng với trình trang trí kiểu cột:

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

Xác minh

Trước khi đẩy bản sửa lỗi lên môi trường production, hãy xác minh kiểu cột trong terminal của bạn bằng psql:

\d table_name

Kiểm tra cột "Type" cho trường cụ thể của bạn. Nó phải ghi là jsonb. Sau khi bạn đã cập nhật mã của mình, hãy chạy một truy vấn kiểm tra để đảm bảo bạn thực sự có thể phân tích cú pháp dữ liệu:

-- Kết quả sẽ trả về 'dark' nếu dữ liệu được lưu dưới dạng JSONB
SELECT profile_data->>'theme' FROM users WHERE id = 1;

Phòng ngừa và mẹo chuyên nghiệp

Sự không khớp kiểu dữ liệu thường che giấu một vấn đề sâu xa hơn: cú pháp không hợp lệ. Ngay cả với phép ép kiểu ::jsonb, nếu chuỗi của bạn thiếu dấu ngoặc kép hoặc có dấu phẩy thừa, PostgreSQL sẽ báo lỗi invalid input syntax for type json.

Khi tôi gỡ lỗi các cấu trúc phân cấp phức tạp, tôi luôn sử dụng JSON Formatter & Validator. Nó giúp phát hiện các lỗi cú pháp nhỏ mà không thể nhận ra trong một chuỗi dài 500 ký tự. Việc xác thực ở phía client giúp bạn tránh được các vòng lặp gửi dữ liệu đến cơ sở dữ liệu không cần thiết dẫn đến thất bại.

Nếu bạn đang di chuyển dữ liệu từ các tệp YAML, trước tiên hãy sử dụng YAML to JSON Converter. Việc chuyển đổi định dạng theo cách thủ công rất dễ gây ra lỗi đánh máy, và những lỗi đó chính là nguyên nhân gây ra các lỗi kiểu dữ liệu Postgres này.