1

I am working with a JSON document which I receive from an external source. In this document, there is a timestamp property which corresponds to the textual format of a timestamp PostgreSQL data type. So in other words, it can be casted to a timestamp type: (data->>'timestamp')::timestamp.

I want to use that timestamp property in an index. I tried the following:

create table t
(
    data    jsonb
);

create index on t(((data->>'timestamp')::timestamp));

I am getting:

ERROR:  functions in index expression must be marked IMMUTABLE

Why is this? I understood that casting to a timestamptz is not immutable, because of the localization configuration settings which are not static, but I can't apply that logic to a cast to timestamp.

Is there still a way to cast my timestamp property in the index here?

I am working with PostgreSQL 12, as released a few days ago.

1

Casts from text to timestamp without time zone are handled by calling the type input function timestamp_in, which is STABLE.

The reason is that timestamp_in supports some other formats too:

SELECT 'now'::timestamp;

         timestamp          
----------------------------
 2019-10-09 09:53:32.026673
(1 row)

If you know that your input is always an ISO timestamp and this cannot happen, define your own conversion function:

CREATE FUNCTION text2ts(text) RETURNS timestamp without time zone
   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$$;

You can use that in your queries and indexes.

If you lack paranoia and are sure of your data quality, you can omit the test. That will improve performance, but you risk index corruption if somebody sneaks something like now into the data.

  • Thank you for the explanation, I did not think about for support for now and such. Now I get it why such cast is not immutable, as the current timestamp is of course not a static. I'll make use of such custom function text2ts. – basicsuperuser Oct 9 '19 at 8:14
0

Try

create table t
(
    data    jsonb,
    data_timestamp TIMESTAMP GENERATED ALWAYS AS ((data->>'timestamp')::timestamp) STORED
);

create index on t(data_timestamp);

From comments:

I also tried that (I forgot to mention that in the question), that gives the same error: ERROR: generation expression is not immutable.

If so the only way which I see is to use static column which' value is generated by INSERT and UPDATE triggers. And index by it.

  • I also tried that (I forgot to mention that in the question), that gives the same error: ERROR: generation expression is not immutable. – basicsuperuser Oct 9 '19 at 7:45
  • @basicsuperuser If so the only way which I see is to use static column which' value is generated by INSERT and UPDATE triggers. And index by it. – Akina Oct 9 '19 at 7:50
  • That's a good idea, I'll apply it. But maybe one step back first, why is the cast not immutable? – basicsuperuser Oct 9 '19 at 7:56
  • why is the cast not immutable? It is dependent by server's timezone settings which are dynamic. – Akina Oct 9 '19 at 8:00
  • 1
    I understand that's the case for timestamptz. But I can't see why that is for timestamp. As far as I understand, there are no timezone settings in play here? – basicsuperuser Oct 9 '19 at 8:01

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.