EC
22005
PostgreSQLERRORNotableData ExceptionHIGH confidence

error in assignment

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
  1. 1Assigning a value that cannot be implicitly cast to the target variable type in PL/pgSQL
  2. 2A domain constraint is violated during assignment
How to reproduce

PL/pgSQL variable assignment with incompatible type.

trigger — this will error
trigger — this will error
DO $
DECLARE v INT;
BEGIN
  v := 'hello'; -- cannot assign text to integer
END $;

expected output

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

Fix

Ensure the assigned value matches the target variable type

WHEN When a PL/pgSQL assignment raises 22005.

Ensure the assigned value matches the target variable type
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 documentation ↗

Class 22 — Data Exception

Content generated with AI assistance and reviewed for accuracy. Found an error? hello@errcodes.dev

← All PostgreSQL errors