ErrorNotes

Fix lỗi PostgreSQL 'invalid input syntax for type integer: "abc"'

beginner🐘 PostgreSQL2026-03-21| PostgreSQL 12–16, mọi hệ điều hành (Linux, macOS, Windows), psql / application drivers

Error Message

ERROR: invalid input syntax for type integer: "abc"
#postgresql#kiểu-dữ-liệu#insert#cast#sql

Tình huống xảy ra lỗi

2 giờ sáng. Một job import hàng loạt vừa chết giữa chừng, để lại bảng dữ liệu ở trạng thái tải dở. Log hiển thị:

ERROR: invalid input syntax for type integer: "abc"
LINE 1: INSERT INTO orders (user_id, amount) VALUES ('abc', 100);
                                                      ^

Hoặc lỗi xuất hiện qua driver ứng dụng của bạn:

psycopg2.errors.InvalidTextRepresentation: invalid input syntax for type integer: "abc"

PostgreSQL rất nghiêm ngặt về kiểu dữ liệu. Không như MySQL, nó sẽ không âm thầm chuyển đổi "abc" thành 0 — mà sẽ ném ra lỗi và rollback toàn bộ transaction. Khó chịu lúc 2 giờ sáng, nhưng thực sự là hành vi hữu ích khi bạn đã tìm ra nguyên nhân gốc rễ.

Debug: tìm giá trị thực sự đang được insert

Thông báo lỗi cung cấp cho bạn hai manh mối: kiểu dữ liệu đích (integer, numeric, date, uuid) và giá trị gây lỗi chính xác. Bắt đầu từ đó.

-- Tái hiện lỗi thủ công
SELECT 'abc'::integer;
-- ERROR: invalid input syntax for type integer: "abc"

-- Kiểm tra các trường hợp biên từ dữ liệu thực tế của bạn
SELECT '42'::integer;   -- OK: trả về 42
SELECT ' 42 '::integer; -- OK: tự động xóa khoảng trắng
SELECT '42.5'::integer; -- ERROR: không phải số nguyên (dùng numeric hoặc làm tròn trước)
SELECT ''::integer;     -- ERROR: chuỗi rỗng không phải số không
SELECT NULL::integer;   -- OK: NULL luôn hợp lệ

Đây là những nguyên nhân thường gặp trong thực tế:

  • File CSV xuất ra mà dòng tiêu đề bị lẫn vào các dòng dữ liệu
  • Chuỗi rỗng ("") ở nơi cần giá trị NULL
  • Số thập phân ("12.50") được insert vào cột kiểu integer
  • ID trông có vẻ là số nhưng chứa chữ cái ("USR-042")
  • Số được định dạng theo locale ("1,234" thay vì 1234)

Tìm các dòng lỗi trước khi insert

Khi load dữ liệu từ bảng staging hoặc CSV, hãy kiểm tra trước khi đụng vào bảng thật. Một regex là đủ để phát hiện hầu hết các vấn đề:

-- Giả sử bạn đã load CSV vào bảng staging với tất cả cột kiểu TEXT
SELECT user_id, amount
FROM staging_orders
WHERE user_id !~ '^-?[0-9]+$'
   OR user_id = '';


-- Phiên bản tổng quát hơn, xử lý cả khoảng trắng
SELECT user_id
FROM staging_orders
WHERE user_id IS NOT NULL
  AND user_id !~ '^\s*-?[0-9]+\s*$';

PostgreSQL 14+ cho phép bạn viết hàm try_cast tùy chỉnh để code gọn hơn, nhưng regex trên hoạt động được trên mọi phiên bản.

Cách xử lý 1: Làm sạch dữ liệu trước khi insert

Sửa dữ liệu nguồn tốt hơn là tìm cách lách trong SQL. Ít "tinh vi" hơn nhưng đáng tin cậy hơn.

-- Chuỗi rỗng chuyển thành NULL thay vì gây lỗi
INSERT INTO orders (user_id, amount)
SELECT
  NULLIF(user_id, '')::integer,
  amount::numeric
FROM staging_orders
WHERE user_id ~ '^-?[0-9]+$' OR user_id IS NULL;


-- Xóa dấu phẩy trong số được định dạng theo locale như "1,234"
INSERT INTO orders (user_id, amount)
SELECT
  user_id::integer,
  REPLACE(amount, ',', '')::numeric
FROM staging_orders;

Cách xử lý 2: Validate trong code ứng dụng trước khi chạy query

Dữ liệu xấu đến từ input người dùng hoặc API bên ngoài? Hãy bắt lỗi ở tầng ứng dụng — trước khi nó đến được Postgres.

Python (psycopg2 / psycopg3):

def safe_insert_order(conn, user_id_raw, amount_raw):
    try:
        user_id = int(user_id_raw)
    except (ValueError, TypeError):
        raise ValueError(f"Invalid user_id: {user_id_raw!r}")

    with conn.cursor() as cur:
        cur.execute(
            "INSERT INTO orders (user_id, amount) VALUES (%s, %s)",
            (user_id, amount_raw)
        )
    conn.commit()

Node.js (pg):

async function insertOrder(client, userIdRaw, amount) {
  const userId = parseInt(userIdRaw, 10);
  if (isNaN(userId)) {
    throw new Error(`Invalid user_id: ${userIdRaw}`);
  }
  await client.query(
    'INSERT INTO orders (user_id, amount) VALUES ($1, $2)',
    [userId, amount]
  );
}

Cách xử lý 3: Thêm CHECK constraint hoặc domain type

Nếu dữ liệu xấu cứ lọt vào ở tầng database, hãy khóa chặt bằng constraint. Cách này đảm bảo ngay cả script migration có lỗi cũng không thể phá vỡ mọi thứ một cách thầm lặng.

-- Check constraint đơn giản trên một cột
ALTER TABLE orders
  ADD CONSTRAINT chk_user_id_positive CHECK (user_id > 0);

-- Hoặc định nghĩa domain type có thể tái sử dụng trên nhiều bảng
CREATE DOMAIN positive_int AS integer CHECK (VALUE > 0);

ALTER TABLE orders ALTER COLUMN user_id TYPE positive_int;

Cách xử lý 4: Load hàng loạt với COPY và bảng staging

Với các lần import CSV lớn, hãy bỏ qua INSERT hoàn toàn. Load vào bảng staging toàn TEXT trước, validate, rồi mới cast kiểu. Khôi phục sau một lần load staging lỗi rất đơn giản. Khôi phục giữa chừng transaction trên bảng production thì không.

-- Bước 1: bảng staging với tất cả cột TEXT
CREATE TEMP TABLE staging_orders (
  user_id    TEXT,
  amount     TEXT,
  created_at TEXT
);

-- Bước 2: load CSV thô
COPY staging_orders FROM '/tmp/orders.csv' CSV HEADER;

-- Bước 3: validate — kết quả phải trả về 0
SELECT COUNT(*) FROM staging_orders
WHERE user_id !~ '^[0-9]+$';

-- Bước 4: cast và insert chỉ khi validation thành công
INSERT INTO orders (user_id, amount, created_at)
SELECT
  user_id::integer,
  amount::numeric,
  created_at::timestamptz
FROM staging_orders;

Kiểm tra lại sau khi sửa

-- Số dòng phải khớp với file nguồn
SELECT COUNT(*) FROM orders;

-- Đảm bảo không có NULL bất ngờ lọt vào
SELECT COUNT(*) FROM orders WHERE user_id IS NULL;

-- Kiểm tra nhanh các dòng được insert gần nhất
SELECT user_id, amount FROM orders ORDER BY id DESC LIMIT 10;

Nếu bạn bọc quá trình import trong một transaction tường minh, hãy xác nhận nó đã được commit:

-- Trong psql
\echo :AUTOCOMMIT
-- hoặc
SELECT txid_current();

Bài học rút ra

  • Dữ liệu bên ngoài không bao giờ đúng kiểu như bạn nghĩ. Hãy validate hoặc làm sạch mọi thứ trước khi insert vào cột có kiểu cụ thể — CSV, webhook, và các hệ thống legacy đều có thể "nói dối".
  • Bảng staging rất đáng bỏ công thêm bước. Load dạng TEXT, validate, rồi mới cast. Bạn sẽ tự cảm ơn bản thân khi lần đầu tiên một lần import 500k dòng có 3 dòng lỗi ở dòng 450.000.
  • Chuỗi rỗng ≠ NULL trong PostgreSQL. NULLIF(val, '') là người bạn đồng hành của bạn. MySQL âm thầm chấp nhận "" trong cột số; PostgreSQL thì không.
  • Parameterized query tự động chuyển đổi kiểu cho bạn. Dùng placeholder %s hoặc $1 với các giá trị Python hoặc Node đã được đặt đúng kiểu. Nối chuỗi trực tiếp sẽ bỏ qua hoàn toàn lớp bảo vệ đó.
  • Thông báo lỗi đã cho bạn biết câu trả lời. PostgreSQL luôn trích dẫn giá trị gây lỗi — hãy dùng nó để truy ngược về đúng dòng dữ liệu nguồn thay vì lục tung cả dataset.

Related Error Notes

Sửa lỗi PostgreSQL 'row is too big: size exceeds maximum' khi INSERT

ERROR: row is too big: size 8208, maximum size 8160

Sửa lỗi PostgreSQL ERROR: aggregate functions are not allowed in WHERE

ERROR: aggregate functions are not allowed in WHERE

Sửa lỗi PostgreSQL 'cannot drop table because other objects depend on it' Khi Xóa Bảng Có Ràng Buộc Khóa Ngoại

ERROR: cannot drop table users because other objects depend on it DETAIL: constraint orders_user_id_fkey on table orders depends on table users HINT: Use DROP ... CASCADE to drop the dependent objects too.