PostgreSQL 22022 Error: Causes and Solutions Complete Guide

PostgreSQL Error 22022: indicator overflow

PostgreSQL error code 22022 (indicator overflow) occurs when an indicator variable used in embedded SQL (ECPG) or a client library cannot accommodate the size or value of the data being retrieved from the database. Indicator variables are small companion variables that signal NULL status or data truncation, and when the returned data length exceeds what the indicator variable's data type can represent, this error is triggered. It is most commonly seen in C-based applications using ECPG, ODBC, or similar low-level database interfaces.

---

Top 3 Causes

1. Indicator Variable Declared as short — Overflows on Large Data

The most frequent cause is declaring indicator variables as short (max value: 32,767) while fetching large TEXT or VARCHAR values that exceed that length.

-- Problematic ECPG declaration
EXEC SQL BEGIN DECLARE SECTION;
    char big_content[200000];
    short ind;  /* BUG: short overflows for data > 32767 bytes */
EXEC SQL END DECLARE SECTION;

EXEC SQL SELECT content INTO :big_content :ind
FROM articles WHERE id = 1;

Fix: Use int or long for indicator variables.

-- Corrected ECPG declaration
EXEC SQL BEGIN DECLARE SECTION;
    char big_content[200000];
    int ind;  /* FIXED: int handles values up to ~2 billion */
EXEC SQL END DECLARE SECTION;

EXEC SQL SELECT content INTO :big_content :ind
FROM articles WHERE id = 1;

Alternatively, pre-trim large values at the SQL level:

-- Limit returned data size at the database level
SELECT LEFT(content, 30000) AS content
FROM articles
WHERE id = 1;

---

2. NUMERIC / DECIMAL Precision Exceeds Client Variable Range

When a NUMERIC(30, 10) column value is fetched into a host variable with a smaller declared precision, the indicator variable may overflow trying to represent the mismatch.

-- Risky: fetching high-precision numeric directly
SELECT large_amount FROM financial_ledger WHERE id = 42;

-- Safe: cast to a manageable precision before returning
SELECT large_amount::NUMERIC(15, 4) AS large_amount
FROM financial_ledger
WHERE id = 42;

-- Audit query: find high-precision NUMERIC columns that may cause issues
SELECT
    table_name,
    column_name,
    numeric_precision,
    numeric_scale
FROM information_schema.columns
WHERE table_schema = 'public'
  AND data_type IN ('numeric', 'decimal')
  AND numeric_precision > 15
ORDER BY numeric_precision DESC;

---

3. Mismatched Indicator Array Size in Array Fetch Operations

In bulk fetch scenarios, if the indicator array size is smaller than the number of rows being fetched, an overflow condition is triggered internally.

-- ECPG array fetch: mismatched indicator array (buggy)
EXEC SQL BEGIN DECLARE SECTION;
    int emp_ids[500];
    char emp_names[500][100];
    short indicators[50];   /* BUG: only 50 slots for 500 rows */
EXEC SQL END DECLARE SECTION;

-- ECPG array fetch: correctly sized indicator array (fixed)
EXEC SQL BEGIN DECLARE SECTION;
    int emp_ids[500];
    char emp_names[500][100];
    int indicators[500];    /* FIXED: matches host array size */
EXEC SQL END DECLARE SECTION;

EXEC SQL FETCH 500 FROM emp_cursor
INTO :emp_ids, :emp_names :indicators;

Pre-check row counts to size arrays appropriately:

-- Check row count before array fetch
SELECT COUNT(*) FROM employees WHERE department_id = 10;

-- Use LIMIT to stay within array bounds
SELECT id, name
FROM employees
WHERE department_id = 10
ORDER BY id
LIMIT 500;

---

Quick Fix Summary

Cause	Fix
short indicator variable	Change to int or long
High-precision NUMERIC	Cast with ::NUMERIC(p,s) or ::TEXT
Indicator array too small	Match indicator array size to host array

---

Prevention Tips

• Standardize on int indicator variables: Ban short indicator variables in your coding standards. For any column that is TEXT, VARCHAR without a length limit, or NUMERIC with precision > 15, int-sized indicators are non-negotiable.

• Monitor schema changes: When column types or precisions change in the database, always audit the corresponding client-side host and indicator variable declarations. Use the information_schema.columns view in your deployment pipeline to automatically flag columns with precision or length that could risk overflow in bound host variables.
  

-- Reusable monitoring query for overflow-risk columns
SELECT
    table_name,
    column_name,
    data_type,
    COALESCE(character_maximum_length::TEXT, 'unlimited') AS max_length,
    numeric_precision,
    numeric_scale
FROM information_schema.columns
WHERE table_schema = 'public'
  AND (
      (data_type = 'text')
      OR (data_type LIKE '%char%' AND character_maximum_length > 32767)
      OR (data_type IN ('numeric','decimal') AND numeric_precision > 15)
  )
ORDER BY table_name, column_name;

---

Related Errors

• 22001 string_data_right_truncation — Data truncated to fit target variable; often appears alongside 22022.
• 22003 numeric_value_out_of_range — Numeric value too large for the target type; closely related to precision overflow scenarios.
• 07002 count_field_overflow — More columns returned than INTO variables declared.

---

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