EC
22030
PostgreSQLERRORNotableData ExceptionHIGH confidence

invalid argument for SQL/JSON datetime function

What this means

SQLSTATE 22030 is raised when a SQL/JSON datetime function receives an argument that is not a valid datetime value in the expected format.

Why it happens
  1. 1Passing a JSON string that is not a valid ISO 8601 datetime to a SQL/JSON datetime extraction function
How to reproduce

SQL/JSON datetime function with invalid input.

trigger — this will error
trigger — this will error
SELECT jsonb_path_query('{"d":"not-a-date"}'::jsonb, '$.d.datetime()');

expected output

ERROR:  datetime format is not recognized: "not-a-date"

Fix

Ensure JSON datetime strings use ISO 8601 format

WHEN When using SQL/JSON datetime functions.

Ensure JSON datetime strings use ISO 8601 format
SELECT jsonb_path_query('{"d":"2024-01-15T10:30:00Z"}'::jsonb, '$.d.datetime()');

Why this works

SQL/JSON datetime functions expect ISO 8601 formatted strings. Validate and normalise datetime strings before storing in JSON.

Version notes
Postgres 15+

SQL/JSON path functions with datetime support introduced in Postgres 15.

Sources
Official documentation ↗

Class 22 — Data Exception

Content generated with AI assistance and reviewed for accuracy. Found an error? hello@errcodes.dev

← All PostgreSQL errors