error in assignment

Category: Data ExceptionVersions: All Postgres versions

What this means

SQLSTATE 22005 is raised when a value cannot be assigned to a target because of a type or domain mismatch that is detected during the assignment phase. It often appears in PL/pgSQL when assigning a query result to a variable of an incompatible type.

Why it happens

1Assigning a value that cannot be implicitly cast to the target variable type in PL/pgSQL
2A domain constraint is violated during assignment

How to reproduce

PL/pgSQL variable assignment with incompatible type.

trigger — this will ERROR

DO $
DECLARE v INT;
BEGIN
  v := 'hello'; -- cannot assign text to integer
END $;

ERROR: invalid input syntax for type integer: "hello"

Fix 1: Ensure the assigned value matches the target variable type

When a PL/pgSQL assignment raises 22005.

fix

DECLARE v TEXT;
BEGIN
  v := 'hello'; -- correct type

Why this works

Use a variable type that matches the actual value, or cast the value explicitly before assignment.

Sources

📚 Official docs: https://www.postgresql.org/docs/current/errcodes-appendix.html

🔧 Source ref: Class 22 — Data Exception

Confidence assessment

✅ HIGH confidence

Standard SQLSTATE for assignment errors. Stable across versions.