Parse Postgres TIMESTAMPs from JSON blobs

text_to_timestamp.sql

CREATE FUNCTION text_to_timestamp(text) RETURNS TIMESTAMP
LANGUAGE sql IMMUTABLE AS
$$
  SELECT CASE
  WHEN $1 ~ '^\d{4}-\d{2}-\d{2} \d{2}:\d{2}:\d{2}(\.\d+)?$' THEN
    CAST($1 AS timestamp without time zone)
  END
$$;

CREATE TABLE t (
 id SERIAL NOT NULL, 
 title VARCHAR(255) generated always as (item ->> 'title') stored, 
 start_date TIMESTAMP generated always as (text_to_timestamp(item ->> 'startDate')) stored,
 item JSONB NOT NULL,
 PRIMARY KEY(id)
)

Could i inline the CAST? Or is the magic happening on line 2 with the IMMUTABLE?

IMMUTABLE is the key. CAST(... AS timestamp) does the same thing as ::timestamp. I think Postgres even transforms one into the other — I've seen that in query plans in the past, at least.

Within the function, our CASE expression provides the guarantee that it returns a timestamp value, or NULL if it can't parse. If you declare the custom function as IMMUTABLE without providing an appropriate guarantee, some weird things can happen. Let's say you omit the format validation and just wrap the simplest expression in an IMMUTABLE function:

CREATE OR REPLACE FUNCTION text_to_timestamp(text) RETURNS TIMESTAMP
LANGUAGE sql
IMMUTABLE
AS $$
  SELECT $1::timestamp
$$;

Let's then imagine someone hits your API with the following JSON payload:

{
  "title": "title goes here",
  "startDate": "now"
}

Note that the startDate is just the literal string "now". Postgres will put an actual timestamp into your start_date column. On the surface, this seems awesome, but there are other consequences of relying on this behavior. The most frustrating is that updating the row will regenerate start_date:

postgres=# select * from t;
 id | title |         start_date         |                  item
----+-------+----------------------------+-----------------------------------------
  1 | foo   | 2024-11-15 17:03:00.992857 | {"title": "foo", "startDate": "now"}
(2 rows)

postgres=# update t set item = jsonb_set(item, '{title}', '"bar"');
UPDATE 1
postgres=# select * from t;
 id | title |         start_date         |                  item
----+-------+----------------------------+-----------------------------------------
  1 | bar   | 2024-11-15 17:24:41.117856 | {"title": "bar", "startDate": "now"}

Note that start_date changed even though I only modified title. This is almost certainly not what you want. I'm not even sure I'd use it as a poor-man's automatic updated_at column because I'm not sure what the consequences of indexing on this might be.

thanks a lot for these insights! i will add a thankyou in my talk at Symfony Con