PostgreSQL Invalid Parameter Value (SQLSTATE 22023)

PostgreSQL raises ERROR: invalid parameter value with SQLSTATE 22023 and condition name invalid_parameter_value when a function or command receives an argument that is syntactically valid but falls outside the range or set of values that function accepts. The exact message varies by context — for instance, ERROR: invalid value for parameter "timezone": "Mars/Olympus" or ERROR: NUMERIC precision 1001 must be between 1 and 1000.

What This Error Means

SQLSTATE 22023 belongs to error class 22 — the data exception class — which covers a broad range of value-related errors including numeric overflows, invalid date/time values, and string data issues. Within this class, invalid_parameter_value specifically targets function and operator arguments that are logically out of bounds or unrecognized, as opposed to type mismatches (which produce 42804) or out-of-range numeric values (which produce 22003).

PostgreSQL raises this error from within built-in functions, type input/output routines, and GUC (Grand Unified Configuration) parameter validation. Because the check happens during expression evaluation rather than at parse time, you will often only see this error at execution time, not when the query is prepared. This distinction matters for applications using server-side prepared statements.

After 22023 is raised, the current statement is aborted and the transaction enters an error state. Any open transaction must be rolled back (or rolled back to a savepoint) before new commands can be issued. The error does not terminate the connection or the backend process.

Common Causes

1. Invalid timezone identifier. Passing an unrecognized timezone string to AT TIME ZONE, SET timezone, set_config('timezone', ...), or functions like timezone() and to_timestamp(). PostgreSQL validates timezone names against the IANA tz database bundled with the server.

2. Out-of-range precision or scale for numeric types. Using NUMERIC(precision, scale) or DECIMAL(precision, scale) with a precision outside the 1–1000 range, or a scale outside the 0–precision range.

3. Out-of-range precision for TIMESTAMP or TIME types. Both TIMESTAMP(p) and TIME(p) accept a fractional-seconds precision between 0 and 6. Values outside this range cause 22023.

4. Invalid interval fields specification. Providing an unrecognized fields qualifier (e.g., INTERVAL '5' MICROCENTURY) or an incompatible combination.

5. Invalid value for a GUC parameter via set_config() or SET. Calling set_config('work_mem', '-1kB', false) or setting an enum GUC to an unlisted value triggers 22023 rather than a syntax error.

6. Invalid regular expression flags. Passing an unrecognized flag character to regexp_match(), regexp_replace(), or similar functions.

7. Out-of-range arguments to math or string functions. For example, round(value, n) where n is outside the supported range, or repeat('x', -1) on some versions.

How to Fix invalid_parameter_value

1. Fix invalid timezone strings. Query pg_timezone_names to find the correct identifier:

-- Find available timezones
SELECT name FROM pg_timezone_names WHERE name ILIKE '%new_york%';

-- Correct usage
SELECT NOW() AT TIME ZONE 'America/New_York';

-- Set session timezone
SET timezone = 'America/New_York';

2. Use a supported numeric precision and scale. Keep precision in 1–1000 and scale in 0–precision:

-- This raises 22023 (precision too high)
CREATE TABLE t (val NUMERIC(1001, 2));

-- Correct
CREATE TABLE t (val NUMERIC(18, 6));

3. Use a supported timestamp/time precision. The fractional-seconds precision must be 0–6:

-- Raises 22023
CREATE TABLE t (ts TIMESTAMP(9));

-- Correct — microsecond precision (PostgreSQL maximum)
CREATE TABLE t (ts TIMESTAMP(6));

4. Validate GUC values before calling set_config(). If you are dynamically setting configuration parameters, guard against invalid values with application-level validation or a BEGIN/EXCEPTION block:

DO $$
BEGIN
  PERFORM set_config('timezone', 'America/Chicago', false);
EXCEPTION
  WHEN invalid_parameter_value THEN
    RAISE NOTICE 'Invalid timezone, using UTC';
    PERFORM set_config('timezone', 'UTC', false);
END;
$$;

5. Check regular expression flags. PostgreSQL supports g, i, m, n, s, w, x as regex flags. Remove or correct any unrecognized flag characters:

-- 'z' is not a valid flag — raises 22023
SELECT regexp_replace('Hello', 'h', 'J', 'iz');

-- Correct
SELECT regexp_replace('Hello', 'h', 'J', 'i');

6. Trap the error in PL/pgSQL when input is untrusted. Use the condition name directly:

BEGIN
  -- ... operation that might raise 22023
EXCEPTION
  WHEN invalid_parameter_value THEN
    -- handle gracefully
END;

Additional Information

• Error class 22 (data exception) contains many related conditions. Closely related codes include 22008 (datetime_field_overflow), 22003 (numeric_value_out_of_range), and 22007 (invalid_datetime_format). If you are working with date/time values, check these codes as well.
• SQLSTATE 22023 has been part of the SQL standard and PostgreSQL for many major versions; there is no specific version where it was introduced.
• JDBC drivers surface this as a PSQLException with getSQLState() returning "22023". Most ORMs (Django, SQLAlchemy, ActiveRecord) propagate it as a generic database error with the original PostgreSQL message in the exception string — search for 22023 or invalid_parameter_value in your ORM's exception hierarchy.
• When using connection poolers like PgBouncer in transaction mode, remember that a 22023 error aborts the current statement. If you are using set_config() to set session-level parameters, verify that the transaction is cleanly rolled back or committed before the connection is returned to the pool.
• In PostgreSQL 14+, the pg_timezone_names view is always available without any extension. On older versions it may require the tzdata package to be installed on the OS.

Frequently Asked Questions

Why does PostgreSQL accept the SQL but fail at runtime? Type input functions and GUC validation run at execution time, not parse time. The parser can only verify that a string literal is syntactically a string — it cannot know whether 'Atlantis/City' is a valid IANA timezone name until the query is actually executed and the timezone lookup is attempted.

My timezone name looks correct but PostgreSQL still raises 22023 — why? PostgreSQL bundles its own copy of the IANA tz database and may be one release behind the system's database. Use SELECT name FROM pg_timezone_names WHERE name = 'Your/Zone' to confirm the exact name recognized by your PostgreSQL instance. Case is not significant for timezone names, but spelling and slashes must match exactly.

Can I catch invalid_parameter_value in application code? Yes. In PL/pgSQL use WHEN invalid_parameter_value THEN. In application code, catch the database exception and inspect getSQLState() (JDBC), pgcode (psycopg2/psycopg3), or the equivalent in your driver for "22023".

Does this error roll back my entire transaction? The error aborts the current statement and marks the transaction as failed. In PostgreSQL, once a transaction has an error, all subsequent commands (except ROLLBACK or ROLLBACK TO SAVEPOINT) will return ERROR: current transaction is aborted. You must issue a ROLLBACK or use a SAVEPOINT with ROLLBACK TO SAVEPOINT to recover and continue.