DEV Community

umzzil nng
umzzil nng

Posted on • Originally published at oraerror.com

PostgreSQL 22031 Error: Causes and Solutions Complete Guide

#postgres #database #dba #sql

PostgreSQL Error 22031: Invalid Argument for SQL JSON Datetime Function

PostgreSQL error code 22031 occurs when the datetime() function inside a SQL/JSON path expression receives an argument it cannot parse into a valid date or time value. This typically happens when JSON string fields contain date values in unexpected formats or when a mismatched template string is supplied. Understanding how PostgreSQL's JSON path datetime() function works is key to resolving and preventing this error.

Top 3 Causes

1. Non-ISO 8601 Date Strings in JSON Data

PostgreSQL's datetime() in JSON path expressions expects ISO 8601 format by default (YYYY-MM-DD, YYYY-MM-DDTHH:MI:SS, etc.). Any deviation triggers error 22031.

-- This FAILS: slash-separated date format
SELECT jsonb_path_query(
  '{"ts": "08/15/2024"}',
  '$.ts.datetime()'
);
-- ERROR: invalid argument for sql json datetime function

-- This WORKS: ISO 8601 format
SELECT jsonb_path_query(
  '{"ts": "2024-08-15"}',
  '$.ts.datetime()'
);
Enter fullscreen mode Exit fullscreen mode

2. Mismatched Template Format String (PostgreSQL 15+)

PostgreSQL 15 introduced template support for datetime(). If the template doesn't match the actual data structure, 22031 is raised.

-- FAILS: wrong template for the actual data
SELECT jsonb_path_query(
  '{"ts": "15-08-2024"}',
  '$.ts.datetime("YYYY-MM-DD")'
);
-- ERROR: 22031

-- WORKS: template matches data layout
SELECT jsonb_path_query(
  '{"ts": "15-08-2024"}',
  '$.ts.datetime("DD-MM-YYYY")'
);
Enter fullscreen mode Exit fullscreen mode

3. Non-String or Unexpected Type Values

If a JSON field holds a number, boolean, or nested object instead of a string, datetime() will fail. This is common with heterogeneous JSON datasets.

-- FAILS: numeric value passed to datetime()
SELECT jsonb_path_query(
  '{"ts": 1723680000}',
  '$.ts.datetime()'
);
-- ERROR: 22031

-- SAFE: filter by type before calling datetime()
SELECT jsonb_path_query(data, '$.ts.datetime()')
FROM logs
WHERE jsonb_typeof(data->'ts') = 'string'
  AND data->>'ts' ~ '^\d{4}-\d{2}-\d{2}';
Enter fullscreen mode Exit fullscreen mode

Quick Fix Solutions

Normalize dates before storing:

-- Fix non-ISO dates already in the table
UPDATE events
SET payload = jsonb_set(
  payload,
  '{ts}',
  to_jsonb(to_date(payload->>'ts', 'MM/DD/YYYY')::text)
)
WHERE payload->>'ts' ~ '^\d{2}/\d{2}/\d{4}$';
Enter fullscreen mode Exit fullscreen mode

Wrap calls in a safe helper function:

CREATE OR REPLACE FUNCTION safe_json_datetime(p_data jsonb, p_path text)
RETURNS timestamptz LANGUAGE plpgsql AS $$
DECLARE v_result timestamptz;
BEGIN
  SELECT jsonb_path_query_first(p_data, p_path::jsonpath)::text::timestamptz
  INTO v_result;
  RETURN v_result;
EXCEPTION WHEN SQLSTATE '22031' THEN RETURN NULL;
END;
$$;

-- Usage
SELECT id, safe_json_datetime(payload, '$.ts.datetime()') FROM events;
Enter fullscreen mode Exit fullscreen mode

Prevention Tips

Enforce format at insert time with a CHECK constraint:

ALTER TABLE events
ADD CONSTRAINT chk_ts_format
CHECK (
  payload->>'ts' IS NULL
  OR payload->>'ts' ~ '^\d{4}-\d{2}-\d{2}'
);
Enter fullscreen mode Exit fullscreen mode

Profile your data before running datetime() queries in production:

SELECT
  COUNT(*) FILTER (WHERE payload->>'ts' ~ '^\d{4}-\d{2}-\d{2}') AS valid,
  COUNT(*) FILTER (WHERE payload->>'ts' !~ '^\d{4}-\d{2}-\d{2}'
                     AND payload->>'ts' IS NOT NULL)             AS invalid
FROM events;
Enter fullscreen mode Exit fullscreen mode

Always validate data quality before deploying SQL/JSON path queries that rely on datetime(). Combining type guards (jsonb_typeof) with format regex checks will catch the vast majority of 22031 errors before they reach production.

Related Errors

  • 22007 – invalid_datetime_format: Similar error but raised in standard SQL casting contexts (::timestamp, to_timestamp()).
  • 22P02 – invalid_text_representation: Triggered when casting a JSON string value directly to a date/time type fails.

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