DEV Community

umzzil nng
umzzil nng

Posted on • Originally published at oraerror.com

PostgreSQL 22023 Error: Causes and Solutions Complete Guide

#postgres #database #dba #sql

PostgreSQL Error 22023: Invalid Parameter Value

PostgreSQL error code 22023 (invalid_parameter_value) is raised when a value passed to a built-in function, operator, or configuration command falls outside the accepted range or uses an unsupported format. This error belongs to SQLSTATE class 22 (Data Exception) and occurs at runtime, meaning it can surface even when SQL syntax is perfectly valid. Understanding the exact context is key to resolving it quickly.

Top 3 Causes

1. Invalid Unit or Range in Date/Time Functions

Passing an unrecognized unit string to date_trunc or an out-of-range value to make_interval is the most common trigger.

-- Causes 22023
SELECT date_trunc('weekday', now());
-- ERROR:  22023: units "weekday" not recognized

-- Fix: use a valid unit
SELECT date_trunc('week', now());
SELECT date_trunc('month', now());

-- Causes 22023
SELECT make_interval(years => 999999999);
-- ERROR:  22023: interval out of range

-- Fix
SELECT make_interval(years => 100);
Enter fullscreen mode Exit fullscreen mode

Valid units for date_trunc: microseconds, milliseconds, second, minute, hour, day, week, month, quarter, year, decade, century, millennium

2. Invalid Value for a Configuration Parameter

Using SET or set_config() with a value that is out of range or not in the allowed enum triggers this error.

-- Causes 22023
SET work_mem = '-1kB';
-- ERROR:  22023: invalid value for parameter "work_mem": "-1kB"

SET client_min_messages = 'verbose_extra';
-- ERROR:  22023: invalid value for parameter "client_min_messages"

-- Fix
SET work_mem = '64MB';
SET client_min_messages = 'notice';

-- Check allowed values before setting
SELECT name, setting, unit, min_val, max_val, enumvals
FROM pg_settings
WHERE name IN ('work_mem', 'client_min_messages');
Enter fullscreen mode Exit fullscreen mode

3. Unsupported Flag or Format in String/Encoding Functions

Passing an invalid regex flag or an unsupported encoding name to encode/decode also raises 22023.

-- Causes 22023: invalid regex flag
SELECT regexp_match('hello', 'hel', 'z');
-- ERROR:  22023: invalid regular expression option: "z"

-- Fix: valid flags are i, g (regexp_replace only), m, s, x
SELECT regexp_match('Hello', 'hello', 'i');
SELECT regexp_replace('aabbcc', 'b', 'X', 'g');

-- Causes 22023: unsupported encoding
SELECT encode('data'::bytea, 'utf8');
-- ERROR:  22023: invalid encoding name "utf8"

-- Fix: only hex, base64, escape are supported
SELECT encode('data'::bytea, 'base64');
SELECT encode('data'::bytea, 'hex');
Enter fullscreen mode Exit fullscreen mode

Quick Fix Solutions

Wrap risky calls in exception-handling blocks to prevent application crashes:

-- Safe wrapper pattern in PL/pgSQL
CREATE OR REPLACE FUNCTION safe_date_trunc(unit TEXT, ts TIMESTAMPTZ)
RETURNS TIMESTAMPTZ AS $$
DECLARE
  valid_units TEXT[] := ARRAY['second','minute','hour','day','week',
                               'month','quarter','year','decade',
                               'century','millennium'];
BEGIN
  IF unit = ANY(valid_units) THEN
    RETURN date_trunc(unit, ts);
  ELSE
    RAISE EXCEPTION 'Invalid unit: "%". Must be one of: %',
      unit, array_to_string(valid_units, ', ')
      USING ERRCODE = '22023';
  END IF;
END;
$$ LANGUAGE plpgsql;

-- Catching 22023 in a block
DO $$
BEGIN
  PERFORM set_config('work_mem', '-999kB', false);
EXCEPTION
  WHEN invalid_parameter_value THEN
    RAISE WARNING 'Caught 22023 - falling back to default work_mem';
    PERFORM set_config('work_mem', '64MB', false);
END;
$$;
Enter fullscreen mode Exit fullscreen mode

Prevention Tips

Whitelist dynamic inputs before passing to functions.
Never pass user-supplied or dynamic strings directly to functions like date_trunc, encode, or regexp_replace. Validate against a known-good list first, or use a lookup table.

Consult pg_settings before dynamic SET operations.
Query pg_settings for min_val, max_val, and enumvals at application startup to build a validation map. Also review PostgreSQL release notes during version upgrades, as allowed parameter values can change between major versions.

Related Errors

Code Name Notes
22000 data_exception Parent class of 22023
22007 invalid_datetime_format Malformed date/time strings
22008 datetime_field_overflow Date/time value out of range
22P02 invalid_text_representation Type cast from invalid text
42704 undefined_object Unknown config parameter name in SET

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

Top comments (0)