jgaskins/text_to_timestamp.sql

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