基于时间戳年月创建PostgreSQL索引遇IMMUTABLE错误的解决问询

Let's break down why you're hitting this error and how to fix it—even though you've locked in the UTC timezone, there's a subtle reason PostgreSQL is still flagging your expression.

Why the Error Occurs #

Your index expression uses DATE(ts_field AT TIME zone 'utc'), but here's the key issue:

• If ts_field is a timestamptz (timestamp with time zone) type, the DATE() function acting on it is STABLE, not IMMUTABLE. PostgreSQL labels it as STABLE because its output could shift if the session's timezone setting changes—even if you've set UTC right now, the database can't guarantee that setting will never be modified later.
• The entire chain of functions (AT TIME ZONE → DATE() → to_char()) inherits this STABLE status, which violates the requirement for index expressions to be IMMUTABLE (index data is permanent and can't change based on session settings).

Solution 1: Wrap Logic in a Custom IMMUTABLE Function #

If you're certain you always want to use UTC for this conversion, package the entire logic into a custom function explicitly marked as IMMUTABLE. This tells PostgreSQL the output will never change for a given input.

First, create the function:

CREATE OR REPLACE FUNCTION get_utc_year_month(ts timestamptz)
RETURNS text AS $$
BEGIN
  RETURN to_char(ts AT TIME ZONE 'utc', 'YYYY-MM');
END;
$$ LANGUAGE plpgsql IMMUTABLE;

Then create the index using this function:

CREATE INDEX tmp_year_mo ON my_table (get_utc_year_month(ts_field));

Note: If ts_field is a timestamp without time zone type (and you're sure it's stored in UTC), simplify the function to skip timezone conversion:

CREATE OR REPLACE FUNCTION get_year_month(ts timestamp)
RETURNS text AS $$
BEGIN
  RETURN to_char(ts, 'YYYY-MM');
END;
$$ LANGUAGE plpgsql IMMUTABLE;

Solution 2: Precompute the Value with a Trigger (More Efficient) #

For better performance (especially on large tables), add a dedicated column to store the precomputed YYYY-MM value, then keep it updated automatically with a trigger. This avoids function calls during index lookups entirely.

1. Add the column and populate existing data:

ALTER TABLE my_table ADD COLUMN year_month text;
UPDATE my_table SET year_month = to_char(ts_field AT TIME ZONE 'utc', 'YYYY-MM');

2. Create a trigger function to update the column on insert/update:

CREATE OR REPLACE FUNCTION update_year_month()
RETURNS trigger AS $$
BEGIN
  NEW.year_month = to_char(NEW.ts_field AT TIME ZONE 'utc', 'YYYY-MM');
  RETURN NEW;
END;
$$ LANGUAGE plpgsql;

3. Attach the trigger to the table:

CREATE TRIGGER trigger_update_year_month
BEFORE INSERT OR UPDATE OF ts_field ON my_table
FOR EACH ROW EXECUTE FUNCTION update_year_month();

4. Create the index on the precomputed column:

CREATE INDEX tmp_year_mo ON my_table (year_month);

This approach is often faster for queries, as the database can directly use the indexed column without running conversion functions each time.

内容的提问来源于stack exchange，提问作者LittleBobbyTables