PostgreSQL Error in Assignment (SQLSTATE 22005)

PostgreSQL raises ERROR_IN_ASSIGNMENT (SQLSTATE 22005) when an assignment operation fails because the value being assigned is incompatible with the target variable or column's type or constraints. You will typically see this in PL/pgSQL procedural code or in contexts where implicit coercion during assignment is rejected.

What This Error Means

SQLSTATE 22005 belongs to PostgreSQL's Class 22 — Data Exception. This class covers errors that arise from problems with the data values themselves, such as numeric overflow, invalid text representations, division by zero, and assignment mismatches. The specific condition ERROR_IN_ASSIGNMENT signals that an attempted assignment could not be completed because the source value could not be coerced or cast to match the destination.

In practice, PostgreSQL raises this condition when an assignment cast fails inside a PL/pgSQL block. Assignment casts in PostgreSQL are a distinct category from explicit (CAST(x AS y)) and implicit casts: they are permitted in assignment contexts (:=, DEFAULT, INSERT ... VALUES, etc.) but not in expression contexts. When even the assignment cast rules cannot bridge the gap between the source and target types, PostgreSQL raises 22005.

After this error occurs, the current transaction is placed in an aborted state. Any subsequent SQL commands within the same transaction will fail with ERROR: current transaction is aborted, commands ignored until end of transaction block until you issue a ROLLBACK or the block's exception handler catches the error.

Common Causes

1. Assigning a value of an incompatible type to a PL/pgSQL variable. For example, assigning a text value that cannot be coerced to integer to a variable declared as INTEGER.
2. Assigning a composite row value where a field's type does not match the target record type's field declaration.
3. A function return value being assigned to a OUT or INOUT parameter with an incompatible type, when no assignment cast exists between the two types.
4. Driver-level or ORM-level parameter binding that sends a value PostgreSQL cannot coerce to the target column type at assignment time. Some drivers send NULL typed as text, which can trigger this when assigning into a strictly-typed variable.
5. Using SELECT INTO inside PL/pgSQL where the selected column type does not match the declared variable type and no implicit or assignment cast is available.

How to Fix error_in_assignment

1. Declare the variable with the correct type. Use %TYPE anchoring to avoid type drift between the table column and the PL/pgSQL variable:

   DECLARE
     v_id employees.id%TYPE;  -- always matches the column type
   BEGIN
     SELECT id INTO v_id FROM employees WHERE name = 'Alice';
   END;
   

2. Add an explicit cast at the point of assignment if you know the conversion is safe:

   DECLARE
     v_count INTEGER;
   BEGIN
     v_count := some_text_variable::INTEGER;
   END;
   

3. Check for missing assignment casts between custom types. If you have user-defined types and the assignment fails, create the required cast with AS ASSIGNMENT:

   CREATE CAST (source_type AS target_type)
     WITH FUNCTION my_cast_function(source_type)
     AS ASSIGNMENT;
   

4. Use %ROWTYPE for row variables to keep composite variable types in sync with the underlying table:

   DECLARE
     rec employees%ROWTYPE;
   BEGIN
     SELECT * INTO rec FROM employees WHERE id = 1;
   END;
   

5. Handle the error in a PL/pgSQL exception block if the incompatibility is expected for some inputs, so the transaction is not left in an aborted state:

   BEGIN
     v_num := input_value::NUMERIC;
   EXCEPTION
     WHEN error_in_assignment OR invalid_text_representation THEN
       RAISE NOTICE 'Could not convert value: %', input_value;
       v_num := NULL;
   END;
   

Additional Information

• SQLSTATE 22005 is part of Class 22 (Data Exception). Related codes in the same class include 22001 (STRING_DATA_RIGHT_TRUNCATION), 22003 (NUMERIC_VALUE_OUT_OF_RANGE), 22007 (INVALID_DATETIME_FORMAT), and 22P02 (INVALID_TEXT_REPRESENTATION). If your error message references a type conversion problem, 22P02 or 22007 may be more applicable than 22005.
• In PostgreSQL's source code, ERROR_IN_ASSIGNMENT maps to ERRCODE_ASSIGN_ERROR. It is defined in the SQL standard and preserved in PostgreSQL for compatibility, but in many common type-mismatch scenarios PostgreSQL raises more specific error codes instead.
• Most PostgreSQL client drivers (libpq, psycopg2, asyncpg, node-postgres) surface this as a DatabaseError or ProgrammingError with the sqlstate attribute set to "22005". Check the pgcode or sqlstate property on the exception object to distinguish it from other data exceptions.
• ORMs such as SQLAlchemy or Django ORM rarely trigger 22005 directly on simple column writes because they validate types before sending to the database; it is more commonly encountered in raw SQL, stored procedures, or when using execute() with untyped parameters.
• There are no significant behavioral differences between PostgreSQL versions for this error code. The condition has been present since at least PostgreSQL 8.x.

Frequently Asked Questions

Why does PostgreSQL raise 22005 instead of a more specific cast error? PostgreSQL raises 22005 specifically in assignment contexts — variable assignments in PL/pgSQL, SELECT INTO, OUT parameter writes — where the failure happens during an assignment cast rather than an explicit or implicit cast. More specific errors like 22P02 or 22007 are raised when the type conversion itself is the problem in an expression context.

My query works in plain SQL but fails with 22005 inside a stored procedure. Why? Plain SQL uses implicit casts, which are generally more permissive. PL/pgSQL variable assignments use a slightly stricter set of assignment casts. A coercion that succeeds in a standalone SELECT may still fail when assigning the result to a strictly-typed variable if no assignment cast is registered between those two types.

Can I catch 22005 without rolling back the entire transaction? Yes. Use a PL/pgSQL EXCEPTION block with WHEN error_in_assignment THEN (or the numeric form WHEN SQLSTATE '22005' THEN). PostgreSQL will internally roll back to a savepoint set at the start of the block, allowing your code to recover and continue the outer transaction.

How do I reproduce this error to test my exception handler? You can force the error in a PL/pgSQL block like this:

DO $$
DECLARE
  v_num INTEGER;
BEGIN
  v_num := 'not_a_number'::INTEGER;
EXCEPTION
  WHEN error_in_assignment OR invalid_text_representation THEN
    RAISE NOTICE 'Caught expected assignment error';
END;
$$;