PostgreSQL 22023 Error: Causes and Solutions Complete Guide

PostgreSQL Error 22023: Invalid Parameter Value — Causes, Fixes, and Prevention

PostgreSQL error code 22023 (invalid_parameter_value) is raised when a value passed to a built-in function, system command, or runtime parameter falls outside the accepted range or uses an unsupported format. This error belongs to SQL standard Class 22 (Data Exception) and can surface in a wide range of scenarios — from date functions to SET commands to regex operations. Understanding its root causes is key to writing robust, production-ready PostgreSQL code.

---

Top 3 Causes

1. Invalid Unit or Format in Date/Time Functions

Passing an unrecognized unit string (e.g., plural form 'minutes' instead of 'minute') to functions like date_trunc() is one of the most frequent triggers.

-- Causes ERROR 22023
SELECT date_trunc('minutes', now());
-- ERROR: unit "minutes" not recognized for type timestamp with time zone

-- Correct usage
SELECT date_trunc('minute', now());
SELECT date_trunc('hour', now());
SELECT date_trunc('month', now());

-- Mismatched format mask in to_timestamp
-- SELECT to_timestamp('15/01/2024', 'YYYY-MM-DD'); -- may raise error

-- Correct format mapping
SELECT to_timestamp('15/01/2024', 'DD/MM/YYYY');

2. Invalid Value for Runtime Parameters via SET

Setting a PostgreSQL GUC (Grand Unified Configuration) parameter to a value outside its allowed range or with an incorrect unit triggers 22023.

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

-- Correct usage
SET work_mem = '64MB';

-- Check allowed range before setting
SELECT name, setting, unit, min_val, max_val, context
FROM pg_settings
WHERE name = 'work_mem';

-- Invalid encoding name
-- SET client_encoding = 'LATIN99'; -- triggers 22023

-- Correct
SET client_encoding = 'UTF8';

3. Invalid Flags or Patterns in String/Regex Functions

Supplying unsupported flags to regexp_replace() or an unsupported encoding name to encode()/decode() also raises this error.

-- Causes ERROR 22023 — 'z' is not a valid regex flag
SELECT regexp_replace('Hello World', 'world', 'PG', 'z');
-- ERROR: invalid regular expression option: "z"

-- Valid flags: g (global), i (case-insensitive), m (multiline), s, w
SELECT regexp_replace('Hello World', 'world', 'PG', 'i');  -- case-insensitive
SELECT regexp_replace('aaa aaa', 'aaa', 'bbb', 'g');       -- global replace

-- Invalid encode format
-- SELECT encode('test'::bytea, 'base32'); -- 22023

-- Supported formats only
SELECT encode('Hello'::bytea, 'base64');
SELECT encode('Hello'::bytea, 'hex');

---

Quick Fix Solutions

1. Use singular unit names for all date/time functions ('minute' not 'minutes', 'second' not 'seconds').
2. Validate parameter values against pg_settings before executing SET commands dynamically.
3. Whitelist allowed values when injecting dynamic strings into function arguments.
4. Wrap risky calls in BEGIN...EXCEPTION blocks to catch and log 22023 gracefully.

-- Safe dynamic date_trunc with whitelist
DO $$
DECLARE
    v_unit TEXT := 'hour';
    v_valid TEXT[] := ARRAY['second','minute','hour','day',
                             'week','month','quarter','year'];
BEGIN
    IF v_unit = ANY(v_valid) THEN
        EXECUTE format('SELECT date_trunc(%L, now())', v_unit);
    ELSE
        RAISE EXCEPTION 'Invalid date unit: %', v_unit;
    END IF;
END $$;

-- Exception handling wrapper
DO $$
BEGIN
    SET work_mem = '32MB';
EXCEPTION
    WHEN invalid_parameter_value THEN
        RAISE WARNING 'Parameter setting failed: %, keeping default.', SQLERRM;
END $$;

---

Prevention Tips

• Always validate dynamic inputs before passing them to PostgreSQL functions. Build a whitelist for units, encoding names, and flags at the application layer or inside PL/pgSQL functions. Never trust raw user input.
• Review release notes on version upgrades. Accepted parameter values and function behavior can change between major PostgreSQL versions. Run your full query suite in a staging environment that mirrors production before upgrading.

---

Related Errors

Code	Name	Description
22007	invalid_datetime_format	Malformed date/time string format
22008	datetime_field_overflow	Date/time value out of valid range
22P02	invalid_text_representation	Invalid input syntax for a type cast
42704	undefined_object	Referenced object (encoding, cursor) does not exist

---

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