PostgreSQL 22005 Error: Causes and Solutions Complete Guide

PostgreSQL Error 22005: error in assignment

PostgreSQL error code 22005, error in assignment, occurs when a value cannot be assigned to a column or variable due to an incompatible data type conversion. It falls under the SQL standard class 22 (Data Exception) and is commonly triggered during INSERT, UPDATE, or within PL/pgSQL functions when implicit type casting fails.

---

Top 3 Causes and Fixes

1. Incompatible Type Assignment in DML Statements

Attempting to store a value whose type cannot be implicitly converted to the target column type is the most common trigger.

-- Problem: Inserting a non-numeric string into an INTEGER column
CREATE TABLE products (
    product_id INTEGER,
    price      NUMERIC,
    created_at DATE
);

-- This will raise error 22005
INSERT INTO products (product_id, price, created_at)
VALUES ('XYZ', 'expensive', '13/2024/01');

-- Fix: Use explicit casting
INSERT INTO products (product_id, price, created_at)
VALUES (
    '101'::INTEGER,
    '29.99'::NUMERIC,
    TO_DATE('2024-01-13', 'YYYY-MM-DD')
);

2. Variable Type Mismatch in PL/pgSQL Functions

When a PL/pgSQL variable is declared with a hardcoded type that doesn't match the actual value being assigned, error 22005 is raised.

-- Problem: Wrong variable type declared
CREATE OR REPLACE FUNCTION get_price(p_id INTEGER)
RETURNS NUMERIC AS $$
DECLARE
    v_price VARCHAR(10);  -- Wrong! Column is NUMERIC
BEGIN
    SELECT price INTO v_price
    FROM products
    WHERE product_id = p_id;
    RETURN v_price;       -- 22005 can occur here
END;
$$ LANGUAGE plpgsql;

-- Fix: Use %TYPE to anchor variable type to the column
CREATE OR REPLACE FUNCTION get_price(p_id INTEGER)
RETURNS NUMERIC AS $$
DECLARE
    v_price products.price%TYPE;  -- Automatically matches column type
BEGIN
    SELECT price INTO v_price
    FROM products
    WHERE product_id = p_id;
    RETURN v_price;
END;
$$ LANGUAGE plpgsql;

3. Domain Constraint Violation on Assignment

PostgreSQL domains add extra constraints on top of base types. Assigning a value that violates a domain's CHECK constraint can raise error 22005.

-- Define a domain with constraints
CREATE DOMAIN positive_price AS NUMERIC
    CHECK (VALUE > 0);

CREATE TABLE items (
    item_id INTEGER PRIMARY KEY,
    price   positive_price
);

-- Problem: Negative value violates domain constraint
INSERT INTO items (item_id, price) VALUES (1, -9.99);  -- Error 22005

-- Fix: Provide a valid value
INSERT INTO items (item_id, price) VALUES (1, 9.99);

-- Check domain definitions
SELECT domain_name, data_type, check_clause
FROM information_schema.domains d
LEFT JOIN information_schema.domain_constraints dc USING (domain_name)
LEFT JOIN information_schema.check_constraints cc USING (constraint_name)
WHERE domain_schema = 'public';

---

Quick Fix Solutions

-- 1. Verify supported casts between types
SELECT
    pg_catalog.format_type(castsource, NULL) AS from_type,
    pg_catalog.format_type(casttarget, NULL) AS to_type,
    castcontext  -- 'a'=assignment, 'i'=implicit, 'e'=explicit only
FROM pg_cast
WHERE pg_catalog.format_type(castsource, NULL) = 'text';

-- 2. Pre-validate input before inserting (PostgreSQL 16+)
SELECT pg_input_is_valid('abc', 'integer');   -- returns false
SELECT pg_input_is_valid('123', 'integer');   -- returns true

-- 3. Safe insert with exception handling
CREATE OR REPLACE FUNCTION safe_insert(p_id TEXT, p_price TEXT)
RETURNS BOOLEAN AS $$
BEGIN
    INSERT INTO products (product_id, price)
    VALUES (p_id::INTEGER, p_price::NUMERIC);
    RETURN TRUE;
EXCEPTION
    WHEN invalid_parameter_value OR numeric_value_out_of_range THEN
        RAISE WARNING 'Invalid input: id=%, price=%', p_id, p_price;
        RETURN FALSE;
END;
$$ LANGUAGE plpgsql;

---

Prevention Tips

1. Always use %TYPE and %ROWTYPE in PL/pgSQL.
This ensures your function variables automatically adapt when table schemas change, eliminating an entire class of type mismatch errors without any manual updates.

2. Validate data at the boundary.
Use pg_input_is_valid() (PostgreSQL 16+) or application-level type checks before sending data to the database. Implement EXCEPTION blocks in critical PL/pgSQL functions to catch and log 22005 errors gracefully rather than letting them propagate uncaught.

---

Related Errors

Code	Name	Relation
22P02	invalid_text_representation	Often confused with 22005; raised when casting invalid text (e.g., 'abc'::integer)
23514	check_violation	Raised on CHECK constraint failures, overlaps with domain-related 22005 cases
42804	datatype_mismatch	Raised at function definition time when return types don't match
22000	data_exception	Parent class of 22005; generic data processing error

---

📖 Want a more detailed guide?
Check out the full in-depth version (Korean) on oraerror.com — includes detailed analysis, additional SQL examples, and prevention tips.