3

I want to create an index on my table for start_time, a timestamptz (timestamp with time zone) field in my json column called match.

Following this question and this article I understand that you can't create an index on a timestamptz field because of different timezones and localisation. Both of these indicate that you can create an index on a timestamp (converted to text), so I tried the following function:

CREATE OR REPLACE FUNCTION to_text(timestamptz) 
 RETURNS text AS $$
  SELECT to_char($1 at time zone 'UTC', 'YYYY-MM-DD"T"HH24:MI:SS.US') 
  $$
LANGUAGE sql IMMUTABLE;

Which I believe has no issues with timezones and localisation.

CREATE INDEX i_match_start_time ON matches (to_text(((match->>'start_time')::timestamptz)));

This returns the following:

ERROR: functions in index expression must be marked IMMUTABLE

I have also tried functions that return a timestamp:

SELECT ($1 at time zone 'UTC')

And functions that return unix time (tried double and casted into decimal):

SELECT EXTRACT(EPOCH FROM $1)

Each of these returns the same error.

I need to index on start_time because virtually all select queries to this table will be ordered by start_time.

Improve this question
2
  • to_text() might be immutable, but to_text(((match->>'start_time')::timestamptz)) is not Commented Oct 24, 2015 at 7:01
  • 1
    @a_horse_with_no_name can you explain why? And how I can fix it? Commented Oct 24, 2015 at 7:15

1 Answer 1

Reset to default
5

Nesting timestamptz in JSON

a timestamptz (timestamp with time zone) field in my json column

There is no such thing as a "timestamptz field in a json". JSON only understands: object, array, string, number, boolean, null.

I'll assume this is handled properly with ISO 8601 timestamptz strings in the field exclusively. Else, there are more issues to solve.

Related blog:

Fake IMMUTABLE

The cast from text to timestamptz itself isn't IMMUTABLE. It depends on volatile settings like datestyle.

In your particular case we might move the cast into the function as a workaround:

CREATE OR REPLACE FUNCTION to_text(text) 
  RETURNS text
  LANGUAGE sql IMMUTABLE AS
$func$
SELECT to_char($1::timestamptz AT TIME ZONE 'UTC', 'YYYY-MM-DD"T"HH24:MI:SS.US') 
$func$;

With only ISO 8601 dates, and no template patterns depending on localized settings, this is de-facto immutable. And "fake"-declaring it as such, CREATE INDEX won't barf.

Better index

However, there is now no point in casting to text and back. Index the returned timestamptz value directly! (Your initial assumption that this wouldn't be possible doesn't hold!)

CREATE OR REPLACE FUNCTION to_tstz_immutable(text) 
  RETURNS timestamptz
  LANGUAGE sql IMMUTABLE STRICT PARALLEL SAFE
RETURN $1::timestamptz;

-- then simply:
CREATE INDEX matches_start_time_idx ON matches (to_tstz_immutable(match->>'start_time'));

Using an "SQL standard" function (added with Postgres 14). See:

Add PARALLEL SAFE in Postgres 9.6 or later. See:

Better yet

virtually all select queries to this table will be ordered by start_time.

Add a generated column (added with Postgres 12) based on above function, and a plain index on that:

ALTER TABLE matches
ADD COLUMN start_time timestamptz GENERATED ALWAYS AS (to_tstz_immutable(match->>'start_time')) STORED

CREATE INDEX matches_start_time_idx ON matches (start_time);

Even simpler in day-to-day handling, and more efficient. The added 8 bytes pay when used a lot. See:

Or move the timestamp out of the JSON document to its own column to begin with. That would be best.

Improve this answer
Sign up to request clarification or add additional context in comments.

1 Comment

Would it work (and make sense) to SET the datestyle and other relevant configuration parameters in the definition of the FUNCTION?

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.