22031 — invalid_argument_for_sql_json_datetime_function

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.

English manual coverage begins with PostgreSQL 8.1. Source history and runtime observations have their own recorded version boundaries.

Reading PG 18·Select a recorded version

13 14 15 16 17 18 19 20

aliases: Unknown
class name: Data Exception
condition name: invalid_argument_for_sql_json_datetime_function
lang: en
sqlstate: 22031

Definition PG 18

22031

At a glance {#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 {#messages}

The datetime-method guards use these primary texts:

| Guard | Primary and hint | | --- | --- | | non-string item | jsonpath item method .%s() can only be applied to a string | | .datetime() format not recognized | %s format is not recognized: "%s"; HINT: Use a datetime template argument to specify the input data format. | | precision outside integer range | time precision of jsonpath item method .%s() is out of range for type integer | | invalid adjusted precision | time precision of jsonpath item method .%s() is invalid |

Meaning {#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 {#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 {#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 {#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 {#related}

22007, 22018

Sources {#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.

Compare versions

SQLSTATE evidence and recorded runtime versions

claims · identity

{
  "claim_id": "identity",
  "limits": "",
  "method": "Read fixed definition.",
  "position": 0,
  "runtime": [],
  "sources": [
    "src.errcodes.18.6"
  ],
  "statement": "22031 is invalid_argument_for_sql_json_datetime_function in SQLSTATE Class 22."
}

claims · resolved-path

{
  "claim_id": "resolved-path",
  "limits": "Source confirmation is not a natural runtime observation.",
  "method": "Read executeDateTimeMethod together with the jsonpath context flags and RETURN_ERROR macro.",
  "position": 1,
  "runtime": [],
  "sources": [
    "src.path.0",
    "src.jsonpath.datetime",
    "src.jsonpath.context-flags",
    "src.jsonpath.execute-context"
  ],
  "statement": "The fixed jsonpath datetime-method implementation first requires a string item. An explicit .datetime(template) call passes the template to parse_datetime with NULL ErrorSaveContext only when jspThrowErrors(cxt) is true; otherwise it saves the parse error and returns jperError. The no-template ISO candidate loop saves each candidate failure even on throwing execution, then the final RETURN_ERROR branch decides whether an unrecognized format throws 22031 or returns a path error. Lax controls structural auto-wrap/unwrap and structural errors; it does not generally suppress datetime parse/conversion errors. Precision, type conversion, and format guards remain 22031 paths."
}

claims · history

{
  "claim_id": "history",
  "limits": "",
  "method": "Use catalogue boundary.",
  "position": 2,
  "runtime": [],
  "sources": [
    "src.errcodes.18.6"
  ],
  "statement": "The locked catalogue records 22031 from 13.0 without proving exact implementation introduction."
}

messages · message.0

{
  "limits": "",
  "message_id": "message.0",
  "path": "",
  "position": 0,
  "raw": {
    "id": "message.0",
    "primary_template": "%s format is not recognized: \"%s\"",
    "severity_source": "ERROR",
    "sources": [
      "src.path.0"
    ],
    "sqlstate": "22031"
  },
  "severity": "ERROR",
  "sources": [
    "src.path.0"
  ],
  "templates": [
    {
      "kind": "primary",
      "literal": "format is not recognized: \" \"",
      "position": 0,
      "role": "",
      "template": "%s format is not recognized: \"%s\""
    }
  ]
}

messages · message.string-only

{
  "limits": "",
  "message_id": "message.string-only",
  "path": "",
  "position": 1,
  "raw": {
    "id": "message.string-only",
    "primary_template": "jsonpath item method .%s() can only be applied to a string",
    "severity_source": "ERROR",
    "sources": [
      "src.jsonpath.datetime"
    ],
    "sqlstate": "22031"
  },
  "severity": "ERROR",
  "sources": [
    "src.jsonpath.datetime"
  ],
  "templates": [
    {
      "kind": "primary",
      "literal": "jsonpath item method . () can only be applied to a string",
      "position": 0,
      "role": "",
      "template": "jsonpath item method .%s() can only be applied to a string"
    }
  ]
}

messages · message.format-unrecognized

{
  "limits": "",
  "message_id": "message.format-unrecognized",
  "path": "",
  "position": 2,
  "raw": {
    "hint_template": "Use a datetime template argument to specify the input data format.",
    "id": "message.format-unrecognized",
    "primary_template": "%s format is not recognized: \"%s\"",
    "severity_source": "ERROR",
    "sources": [
      "src.jsonpath.datetime"
    ],
    "sqlstate": "22031"
  },
  "severity": "ERROR",
  "sources": [
    "src.jsonpath.datetime"
  ],
  "templates": [
    {
      "kind": "primary",
      "literal": "format is not recognized: \" \"",
      "position": 0,
      "role": "",
      "template": "%s format is not recognized: \"%s\""
    },
    {
      "kind": "hint",
      "literal": "Use a datetime template argument to specify the input data format.",
      "position": 1,
      "role": "",
      "template": "Use a datetime template argument to specify the input data format."
    }
  ]
}

messages · message.precision-range

{
  "limits": "",
  "message_id": "message.precision-range",
  "path": "",
  "position": 3,
  "raw": {
    "id": "message.precision-range",
    "primary_template": "time precision of jsonpath item method .%s() is out of range for type integer",
    "severity_source": "ERROR",
    "sources": [
      "src.jsonpath.datetime"
    ],
    "sqlstate": "22031"
  },
  "severity": "ERROR",
  "sources": [
    "src.jsonpath.datetime"
  ],
  "templates": [
    {
      "kind": "primary",
      "literal": "time precision of jsonpath item method . () is out of range for type integer",
      "position": 0,
      "role": "",
      "template": "time precision of jsonpath item method .%s() is out of range for type integer"
    }
  ]
}

messages · message.precision-invalid

{
  "limits": "",
  "message_id": "message.precision-invalid",
  "path": "",
  "position": 4,
  "raw": {
    "id": "message.precision-invalid",
    "primary_template": "time precision of jsonpath item method .%s() is invalid",
    "severity_source": "ERROR",
    "sources": [
      "src.jsonpath.datetime"
    ],
    "sqlstate": "22031"
  },
  "severity": "ERROR",
  "sources": [
    "src.jsonpath.datetime"
  ],
  "templates": [
    {
      "kind": "primary",
      "literal": "time precision of jsonpath item method . () is invalid",
      "position": 0,
      "role": "",
      "template": "time precision of jsonpath item method .%s() is invalid"
    }
  ]
}

sources · src.errcodes.18.6

{
  "commit": "724edf9bde9d356724ad384a2e196edc3c9f80f7",
  "docs_url": "",
  "kind": "upstream_source",
  "location": "",
  "path": "src/backend/utils/errcodes.txt",
  "position": 0,
  "sha256": "6e8de346643ba84aa3c9c6a73360acfc7b2dfb89162c06c08ce9bf5bcd5bbcba",
  "source_id": "src.errcodes.18.6",
  "tag": "REL_18_6",
  "url": "https://github.com/postgres/postgres/blob/724edf9bde9d356724ad384a2e196edc3c9f80f7/src/backend/utils/errcodes.txt#L216"
}

sources · src.path.0

{
  "commit": "724edf9bde9d356724ad384a2e196edc3c9f80f7",
  "docs_url": "",
  "kind": "upstream_source",
  "location": "",
  "path": "src/backend/utils/adt/jsonpath_exec.c",
  "position": 1,
  "sha256": "678cb8947f0b5e6ad14524f1863987ab99641cfb5f81121fc5c5006af0319d02",
  "source_id": "src.path.0",
  "tag": "REL_18_6",
  "url": "https://github.com/postgres/postgres/blob/724edf9bde9d356724ad384a2e196edc3c9f80f7/src/backend/utils/adt/jsonpath_exec.c#L2469"
}

sources · src.jsonpath.datetime

{
  "commit": "724edf9bde9d356724ad384a2e196edc3c9f80f7",
  "docs_url": "",
  "kind": "upstream_source",
  "location": "",
  "path": "src/backend/utils/adt/jsonpath_exec.c",
  "position": 2,
  "sha256": "678cb8947f0b5e6ad14524f1863987ab99641cfb5f81121fc5c5006af0319d02",
  "source_id": "src.jsonpath.datetime",
  "tag": "REL_18_6",
  "url": "https://github.com/postgres/postgres/blob/724edf9bde9d356724ad384a2e196edc3c9f80f7/src/backend/utils/adt/jsonpath_exec.c#L2326"
}

sources · src.jsonpath.context-flags

{
  "commit": "724edf9bde9d356724ad384a2e196edc3c9f80f7",
  "docs_url": "",
  "kind": "upstream_source",
  "location": "",
  "path": "src/backend/utils/adt/jsonpath_exec.c",
  "position": 3,
  "sha256": "678cb8947f0b5e6ad14524f1863987ab99641cfb5f81121fc5c5006af0319d02",
  "source_id": "src.jsonpath.context-flags",
  "tag": "REL_18_6",
  "url": "https://github.com/postgres/postgres/blob/724edf9bde9d356724ad384a2e196edc3c9f80f7/src/backend/utils/adt/jsonpath_exec.c#L235"
}

sources · src.jsonpath.execute-context

{
  "commit": "724edf9bde9d356724ad384a2e196edc3c9f80f7",
  "docs_url": "",
  "kind": "upstream_source",
  "location": "",
  "path": "src/backend/utils/adt/jsonpath_exec.c",
  "position": 4,
  "sha256": "678cb8947f0b5e6ad14524f1863987ab99641cfb5f81121fc5c5006af0319d02",
  "source_id": "src.jsonpath.execute-context",
  "tag": "REL_18_6",
  "url": "https://github.com/postgres/postgres/blob/724edf9bde9d356724ad384a2e196edc3c9f80f7/src/backend/utils/adt/jsonpath_exec.c#L654"
}

Source references

Original manual reference

Full definition and evidence JSON

Definition sources

center · PostgreSQL 18 · english-manuals:4e78c7f7e0b9ead98b7d55bf3db693d6f2993242e08b2438ca0a510f54a52de7

Text language: en · english-manuals:4e78c7f7e0b9ead98b7d55bf3db693d6f2993242e08b2438ca0a510f54a52de7