22031

At a glance

A SQL/JSON datetime method received an invalid type, precision, or format. Fixed jsonpath execution reports an unrecognized format and hints to use a datetime template argument.

Messages

The datetime-method guards use these primary texts:

Meaning

The fixed executeDateTimeMethod path first requires a scalar string. .datetime(template) passes the explicit template to parse_datetime: when jspThrowErrors(cxt) is false, its ErrorSaveContext turns a parse failure into jperError; when throwing is enabled, the parser receives no save context and may throw its underlying error. The no-template .datetime(), .date(), .time(), .time_tz(), .timestamp(), and .timestamp_tz() paths try their enumerated ISO formats, saving each candidate failure in ErrorSaveContext even on a throwing execution; only after every candidate fails does the final 22031 RETURN_ERROR branch decide whether to throw or return jperError. Optional time precision is converted to an integer and checked before adjustment. A missing format, incompatible conversion, non-string item, or invalid precision uses 22031.

Diagnosis

Record the method name, input JSON item type, datetime text, template text if present, and precision argument. The .datetime() no-format match has the HINT to provide a template; the other methods use fixed ISO candidates and have no such template hint. Separate a scalar type mismatch from a format failure and from precision range/adjustment errors. lax controls structural auto-wrap/unwrap and structural-error handling; it does not generally suppress datetime parse or conversion errors. Check the executor’s throwErrors/RETURN_ERROR path and the jsonb_path_* function’s silent parameter or SQL/JSON ON ERROR clause to determine whether a saved parse error was returned or raised.

Response

Pass a string item to the method, use a template matching the datetime text for .datetime(), or choose the method whose ISO type matches the input. Keep precision within the integer and datetime typmod rules. If a non-ERROR ON ERROR behavior intentionally handles parse failure, repair or retain that behavior according to the application contract; otherwise correct the input or template. If an ERROR occurred in an explicit transaction, roll back or roll back to the existing savepoint before retrying; autocommit can retry the corrected expression.

Versions

The locked catalogue records this condition from 13.0; the fixed SQL/JSON datetime source path here is PostgreSQL 18.6. No natural runtime observation is claimed for this page.

Related

22007, 22018

Sources

The datetime method is src/backend/utils/adt/jsonpath_exec.c#L2326-2780, including string/type checks, explicit-template and ISO candidate parsing, ErrorSaveContext, conversions, and precision guards. The strict/lax/throw split is defined by #L235-249 and #L654-727. The structured evidence record retains the exact primary and hint roles; no natural runtime was run.