PostgreSQL Indicator Overflow (SQLSTATE 22022)

In embedded SQL programs using PostgreSQL's ECPG preprocessor, the error ERROR: indicator variable is too short for the length of the returned data with SQLSTATE 22022 and condition name indicator_overflow appears when the integer indicator variable you declared cannot represent the true length of the value fetched from the database. This is a data exception from SQLSTATE class 22 (Data Exception).

What This Error Means

SQLSTATE 22022 belongs to error class 22, the data exception class, which covers a wide range of errors involving invalid or out-of-range data values. The indicator_overflow condition is specific to embedded SQL (ECPG) — it does not occur in ordinary psql sessions or in server-side PL/pgSQL code.

In embedded SQL, indicator variables are short integer (short int) variables that accompany a host variable to signal null values and to communicate the actual length of variable-length data (such as VARCHAR or TEXT columns). When PostgreSQL retrieves a value whose true length exceeds the maximum value that a short int can hold (32767 on most platforms), it cannot store that length in the indicator variable and raises indicator_overflow.

The connection and transaction remain intact after this error occurs. The fetch that triggered the condition did not fully populate the host variable with valid data, but no rollback is forced. The application must handle the error and decide whether to continue or abort the current transaction.

Common Causes

1. Fetching a long text value with a short int indicator. The most common cause is retrieving a TEXT, VARCHAR, or BYTEA column that holds more than 32,767 characters/bytes while using a standard short int indicator variable. The indicator is meant to hold the actual data length, but short int saturates at 32,767.

2. Mismatched indicator variable type. ECPG requires the indicator variable to be declared as short int. If you accidentally use a plain int or long, the preprocessor may still generate code that stores the length into an incorrectly sized slot, potentially triggering overflow for large values.

3. Unbounded output buffers paired with long database content. When the host variable buffer is sized smaller than the column value, PostgreSQL truncates the data but still records the full original length in the indicator. If that full length exceeds SHRT_MAX, overflow is raised before the truncated value can be returned.

How to Fix indicator_overflow

1. Check column lengths at the database level. Before fetching, verify the maximum length of the column content your query might return:

   SELECT MAX(LENGTH(column_name)) FROM table_name;
   

   If the maximum length exceeds 32,767, you need to handle the fetch differently.

2. Increase the host variable buffer and accept truncation gracefully. If you only need the first N bytes of a long value, declare a large enough char host variable and check the indicator for truncation (a positive value means the original data was longer than the buffer):

   EXEC SQL BEGIN DECLARE SECTION;
       char   result[4096];
       short  ind;
   EXEC SQL END DECLARE SECTION;
   
   EXEC SQL FETCH cur INTO :result :ind;
   
   if (ind > 0) {
       /* Value was truncated — ind holds the original length */
   }
   

   For values longer than 32,767 bytes, ind itself will overflow, triggering SQLSTATE 22022. In that case, consider streaming the data with a server-side cursor and pg_read_binary_file or use the libpq C API directly.

3. Use the libpq C API instead of ECPG for large objects. When dealing with columns that routinely hold very large values (multi-kilobyte JSON, XML documents, binary blobs), the libpq PQgetvalue / PQgetlength functions return the length as an int (not a short), so overflow does not occur:

   int len = PQgetlength(res, row, col);
   char *val = PQgetvalue(res, row, col);
   

4. Limit returned data at the SQL level. If you only need a prefix of a long text column, use LEFT() or SUBSTRING() in the query to guarantee the returned length fits within your host variable and indicator:

   SELECT LEFT(long_text_column, 1000) FROM my_table WHERE id = $1;
   

Additional Information

• SQLSTATE class 22 (Data Exception) includes many sibling conditions such as 22001 (string_data_right_truncation), 22003 (numeric_value_out_of_range), and 22P02 (invalid_text_representation). All of these relate to data value problems rather than system or connection errors.
• indicator_overflow (22022) is defined by the SQL standard and is specifically applicable to embedded SQL environments. It does not appear in PL/pgSQL, PL/Python, or other server-side languages.
• ECPG has been part of the PostgreSQL distribution since very early versions. The behavior of indicator variables has remained consistent across all supported PostgreSQL versions (10 through 17).
• No transaction rollback is triggered by this error — the error is raised before data is written to the host variable, so no server-side state changes.
• Applications using JDBC, psycopg2, or other client drivers do not use ECPG indicator variables and will never encounter this specific SQLSTATE.

Frequently Asked Questions

Why does this error only happen in my C program and not in psql? SQLSTATE 22022 is exclusive to embedded SQL (ECPG). The psql client and most language drivers retrieve data lengths as full int or larger types, so they never hit the short int overflow that triggers this condition. ECPG uses short int indicators as mandated by the SQL/CLI standard.

Is my data corrupted when this error occurs? No. The error is raised during the fetch before any host variable is populated with invalid data. The database server-side state is unchanged. However, the specific row fetch failed, so your application must re-execute or skip that row.

Can I use a different type for the indicator variable to avoid overflow? No. The SQL embedded standard and ECPG require indicator variables to be short int. Using a different integer type will cause a preprocessor error or undefined behavior. The correct approach is to restructure how you retrieve large values — either by using LEFT()/SUBSTRING() in SQL, processing data in chunks with a cursor, or switching to libpq directly.

What does a positive indicator value mean versus a negative one? In ECPG, a negative indicator value (typically -1) means the column value was NULL. A positive indicator value means the column value was truncated to fit the host variable buffer, and the indicator holds the true original length. A value of 0 means the data was fetched without truncation or null. The overflow error fires when that positive length value cannot be stored in a short int.