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 being assigned to a column or variable is incompatible with the target data type, and PostgreSQL cannot perform an implicit cast. This error commonly appears in INSERT, UPDATE, SET statements, or inside PL/pgSQL function blocks when variable assignments fail due to type mismatches.

---

Top 3 Causes

1. Implicit Cast Failure Due to Type Mismatch

The most common cause is trying to insert or update a column with a value of an incompatible type that PostgreSQL cannot automatically convert.

-- Error: assigning a string to an INTEGER column
CREATE TABLE products (
    product_id  SERIAL PRIMARY KEY,
    quantity    INTEGER,
    price       NUMERIC(10, 2)
);

-- This will raise 22005
INSERT INTO products (quantity, price)
VALUES ('twenty', 'free');

-- Fix: use correct types or explicit casting
INSERT INTO products (quantity, price)
VALUES (20, 9.99);

-- Or use explicit CAST / :: operator
INSERT INTO products (quantity, price)
VALUES ('20'::INTEGER, '9.99'::NUMERIC);

2. PL/pgSQL Variable Assignment Type Mismatch

Inside PL/pgSQL functions or procedures, declaring a variable with a type that doesn't match the value being assigned will trigger this error. Using SELECT INTO to fetch data into a wrongly-typed variable is a frequent culprit.

-- Problematic function: variable type mismatch
CREATE OR REPLACE FUNCTION get_product_price(p_id INTEGER)
RETURNS TEXT AS $$
DECLARE
    v_price INTEGER;  -- Wrong: should be NUMERIC
BEGIN
    SELECT price INTO v_price  -- price is NUMERIC(10,2)
    FROM products
    WHERE product_id = p_id;

    RETURN 'Price: ' || v_price::TEXT;
END;
$$ LANGUAGE plpgsql;

-- Fix: use %TYPE to automatically match the column type
CREATE OR REPLACE FUNCTION get_product_price_fixed(p_id INTEGER)
RETURNS TEXT AS $$
DECLARE
    v_price products.price%TYPE;  -- Automatically matches column type
BEGIN
    SELECT price INTO v_price
    FROM products
    WHERE product_id = p_id;

    IF NOT FOUND THEN
        RETURN 'Product not found';
    END IF;

    RETURN 'Price: $' || v_price::TEXT;
END;
$$ LANGUAGE plpgsql;

SELECT get_product_price_fixed(1);

3. Invalid Value for ENUM or Domain Types

Assigning a value that doesn't belong to a defined ENUM or violates a Domain constraint will also raise this error.

-- Define ENUM and Domain types
CREATE TYPE order_status AS ENUM ('pending', 'shipped', 'delivered', 'cancelled');

CREATE DOMAIN positive_int AS INTEGER CHECK (VALUE > 0);

CREATE TABLE orders (
    order_id    SERIAL PRIMARY KEY,
    status      order_status,
    quantity    positive_int
);

-- Error: 'returned' is not a valid ENUM value
INSERT INTO orders (status, quantity)
VALUES ('returned', 5);  -- 22005 error

-- Error: negative value violates domain constraint
INSERT INTO orders (status, quantity)
VALUES ('pending', -3);  -- error in assignment

-- Fix: use valid ENUM values and domain-compliant values
INSERT INTO orders (status, quantity)
VALUES ('pending', 5);

-- To add a new ENUM value:
ALTER TYPE order_status ADD VALUE 'returned';

-- Now this works:
INSERT INTO orders (status, quantity)
VALUES ('returned', 2);

---

Quick Fix Solutions

• Always use explicit casting with ::type or CAST(value AS type) when inserting values from external sources.
• Use %TYPE and %ROWTYPE in PL/pgSQL variable declarations to automatically match column types and avoid drift after schema changes.
• Validate input data at the application layer before it reaches the database.
• Check ENUM values and Domain definitions before performing inserts or updates.

-- Quick diagnostic: check column types before inserting
SELECT column_name, data_type, udt_name
FROM information_schema.columns
WHERE table_name = 'orders';

---

Prevention Tips

1. Use %TYPE in all PL/pgSQL variable declarations to keep variables in sync with table column types automatically. This prevents breakage when schemas evolve.

2. Add domain types and CHECK constraints at the schema level to catch bad data early, and run integration tests against your schema using tools like pgTAP to catch type issues before they reach production.
   

-- Example: domain-driven schema design for safer assignments
CREATE DOMAIN email_address AS TEXT
    CHECK (VALUE ~* '^[A-Za-z0-9._%+-]+@[A-Za-z0-9.-]+\.[A-Za-z]{2,}$');

CREATE TABLE customers (
    customer_id SERIAL PRIMARY KEY,
    email       email_address NOT NULL,
    age         INTEGER CHECK (age BETWEEN 0 AND 120)
);

---

Related Errors

Error Code	Name	Description
42804	datatype_mismatch	Type mismatch in expressions or function return types
22P02	invalid_text_representation	Invalid format when casting text to a target type
23514	check_violation	CHECK constraint violation on domain or table column
42883	undefined_function	No matching function found due to wrong argument types

---

📖 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.