PostgreSQL 22022 Error: Causes and Solutions Complete Guide

PostgreSQL Error 22022: indicator overflow

PostgreSQL error code 22022 (indicator overflow) occurs primarily in ECPG (Embedded C for PostgreSQL) environments or when client libraries use indicator variables to track NULL values and data truncation status. The error is triggered when the value being stored into an indicator variable exceeds the storage capacity of its declared data type, most commonly when a short int indicator is used to represent data lengths that surpass 32,767 bytes.

---

Top 3 Causes

1. Undersized Indicator Variable Declaration

Declaring indicator variables as short instead of int is the most common root cause. When fetching large TEXT or BYTEA columns, the actual byte length easily exceeds the short maximum of 32,767.

-- Check actual column sizes to validate against your indicator variable type
SELECT
    MAX(octet_length(your_large_column)) AS max_bytes,
    AVG(octet_length(your_large_column)) AS avg_bytes
FROM your_table_name;

-- If max_bytes > 32767, a 'short' indicator will overflow

2. Fetching Oversized Column Data in a Single Fetch

Retrieving large TEXT, BYTEA, or VARCHAR columns in one fetch without proper buffer management causes the indicator to overflow when trying to record the full data length.

-- Use a cursor to fetch large data in manageable chunks
BEGIN;

DECLARE chunk_cursor CURSOR FOR
    SELECT id, large_text_column
    FROM your_table_name
    ORDER BY id;

FETCH 500 FROM chunk_cursor;

CLOSE chunk_cursor;

COMMIT;

-- Alternatively, split large columns using substring
SELECT
    id,
    substring(large_text_column FROM 1 FOR 8192) AS part_1,
    octet_length(large_text_column) AS total_size
FROM your_table_name
WHERE id = 42;

3. Uninitialized or Mismatched Indicator Arrays

When using array host variables with corresponding indicator arrays in ECPG, mismatched array sizes or uninitialized indicator arrays can trigger this error unpredictably.

-- Pre-check NULL distribution to minimize reliance on indicator variables
SELECT
    COUNT(*) AS total,
    COUNT(your_column) AS non_null,
    COUNT(*) - COUNT(your_column) AS null_count
FROM your_table_name;

-- Use COALESCE to eliminate NULLs server-side, reducing indicator variable usage
SELECT
    id,
    COALESCE(nullable_col, '') AS safe_col
FROM your_table_name
LIMIT 1000;

---

Quick Fix Solutions

• Change indicator variable type: Replace short ind; with int ind; or use PostgreSQL's sqlind type in ECPG code.
• Add server-side size constraints to prevent oversized data from reaching the client:

-- Add a CHECK constraint to cap column size
ALTER TABLE your_table_name
    ADD CONSTRAINT chk_max_col_size
    CHECK (octet_length(large_text_column) <= 65536);

-- Find rows that would violate the constraint before applying
SELECT id, octet_length(large_text_column) AS byte_size
FROM your_table_name
WHERE octet_length(large_text_column) > 65536
ORDER BY byte_size DESC;

• Use COALESCE server-side to avoid NULL indicator variable handling altogether for columns that can safely have a default value.

---

Prevention Tips

1. Standardize indicator variable types across all ECPG codebases to int or long. Add this rule to your code review checklist and enforce it with static analysis tools in your CI pipeline. Never use short or char as indicator variable types, regardless of the expected data size.

2. Monitor column size growth regularly using pg_stats to catch potential overflow risks before they hit production:
   

-- Monitor columns with large average widths
SELECT
    schemaname,
    tablename,
    attname AS column_name,
    avg_width AS avg_byte_width,
    null_frac
FROM pg_stats
WHERE avg_width > 1000
    AND schemaname = 'public'
ORDER BY avg_width DESC
LIMIT 10;

By keeping indicator variables properly sized and monitoring data growth proactively, 22022 errors can be entirely eliminated from production environments.

---

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