substring_error 22011 appears when SUBSTRING arguments point outside the source string or use a negative length.
PostgreSQL substring_error (SQLSTATE 22011) signals that SUBSTRING() received an invalid start index or length. Check CHAR_LENGTH() first or clamp the arguments so start ≥1 and length ≥0, then rerun the query to resolve it.
PostgreSQL substring_error 22011
PostgreSQL raises substring_error when the SUBSTRING function receives an invalid argument set. The SQLSTATE 22011 code belongs to the Data Exception class, alerting you that the requested slice cannot be extracted from the given text or bytea value.
Because SUBSTRING is widely used for parsing strings, the error often surfaces in data-cleanup scripts, ETL jobs, and application queries.
Fixing it quickly prevents runtime failures and keeps string manipulations reliable.
The database throws SQLSTATE 22011 immediately after evaluating SUBSTRING(start,length FROM source) or source[start:length] if the starting position is less than 1, larger than the source length, or the length argument is negative.
The error appears in PostgreSQL 8.0 through 16 without major variation, though messages differ slightly: “start position is out of range” or “negative substring length not allowed.”
Invalid start index - calling SUBSTRING with a position of 0 or a value that exceeds CHAR_LENGTH(source).
Negative or zero length - passing a negative length or combining a valid start with length 0.
Dynamic parameters - calculations that resolve to out-of-range numbers at runtime, often in functions, loops, or parameterized queries.
Data drift - unexpected shorter strings after upstream schema changes or trimming operations.
Validate inputs before calling SUBSTRING.
Use CASE expressions or GREATEST/LEAST to clamp values within legal bounds.
Guard length with NULLIF or COALESCE to avoid negatives. When unsure of string size, compute CHAR_LENGTH(source) first.
Refactor application code to stop sending invalid parameters. Use Galaxy’s AI copilot to rewrite brittle substring logic in seconds and test in-line.
Parsing IDs: SELECT SUBSTRING(id,1,3) FROM table may break when id is only 2 chars. Wrap with CASE WHEN CHAR_LENGTH(id) >= 3 THEN SUBSTRING(id,1,3) END.
Variable indexes: Dynamic start := pos-1.
Clamp with GREATEST(pos,1).
Bulk updates: UPDATE t SET col = SUBSTRING(col,0,5) fails. Change 0 to 1 or use RIGHT(col,5).
Always pre-check CHAR_LENGTH() or OCTET_LENGTH() in queries that slice text.
Enforce string length constraints with CHECK constraints so bad data never lands in tables.
Encapsulate substring logic in a SQL function that performs bounds checking.
Galaxy collections let you endorse and reuse that safe helper across teams.
SQLSTATE 22003 numeric_value_out_of_range - occurs when math overflows; validate calculations similarly.
SQLSTATE 22007 invalid_datetime_format - happens with malformed dates; sanitize inputs.
SQLSTATE 22000 data_exception - parent class for 22011; indicates general data validity issues.
.
Rarely. It usually means the query supplied incorrect parameters, not that the stored data is corrupt.
No native setting exists, but a wrapper function can catch invalid ranges and return NULL or ''.
The rule is identical across PostgreSQL versions, though wording in the message may vary.
Galaxy’s editor surfaces argument hints and its AI copilot autogenerates safe-substring patterns, reducing invalid queries before they hit production.
.avif)
.avif){kind=logo}
