ERROR: functions in index expression must be marked IMMUTABLE in Postgres
According to this thread in the hackers mailing list:
http://www.mail-archive.com/pgsql-hackers@postgresql.org/msg86725.html
this is intended behaviour as to_char
depends on the LC_MESSAGES setting
In your case this apparently doesn't make sense as the format you are using will never depend on the locale, so if you do need to use the text representation in the index, you can create your own to_char() function and mark it as immutable:
CREATE OR REPLACE FUNCTION my_to_char(some_time timestamp)
RETURNS text
AS
$BODY$
select to_char($1, 'yyyy-mm-dd');
$BODY$
LANGUAGE sql
IMMUTABLE;
If you have to use it as a text in the index (and cannot use the cast to a date as Sam suggested) you will need to create your own formatting function that you can mark as immutable. That can then be used in the index.
But to make Postgres use the index you will need to call my_to_char()
in your SQL statements as well. It will not recognize it when you use the built-in to_char()
But I do think Sam's suggestion using a straight date in the index is probably better
Error functions in index expression must be marked IMMUTABLE, not time stamp
You probably want
CREATE INDEX idx_concat_paostartno_paostartsuff
ON dmv_os_addbase_residential ((pao_start_number || pao_start_suffix));
This index can only be used for queries like
SELECT *
FROM dmv_os_addbase_residential
WHERE pao_start_number || pao_start_suffix <operator> <constant>;
where <operator>
is one of =
, <
, <=
, >
or >=
.
I'm trying to create the following index
time_stamp
must be a timestamp with time zone
.
You'll have to make the expression IMMUTABLE
by converting it to a timestamp without time zone
at the appropriate time zone:
CREATE INDEX request_response_partition_idx
ON public.request_response_partition (
userid,
extract('Month' FROM (time_stamp AT TIME ZONE 'UTC'))
);
Of course you also have to modify the expression in your queries to match the index.
How do I create an index in PostgreSQL on an expression that uses the date_trunc function?
only date_trunc(text,interval)
and date_trunc(text,timestamp)
are immutable
create index on test (date_trunc('month', foo::timestamp ));
the problem with foo at time zone 'GMT'
is that the expression foo at time zone 'GMT'
is not itself immutable. It's not immutable because it depends on the sessions time zone
setting.
confusingly at time zone 'GMT'
is the inverse of at time zone 'GMT'
so if the column being indexed is of type timestamptz
(timestamp with timezone) then that operation will fix the index expression.
How to create index on records for last 90 days in Postgres Making now() immutable
Instead of an immutable now()
function (which wouldn't work!) use a pseudo-immutable function returning a timestamp
constant, and base your partial index on it - as well as your queries that are supposed to use it.
Also, you don't have to update the index every day. The index can hold a couple of outdated rows, that's hardly relevant. You just add an exact condition to your queries additionally. Performance deteriorates slowly over time as more rows are added. It's enough to recreate function and index from time to time. Can be every week at times with the lowest DB load.
It just so happens that I posted a complete solution for the case 6 years ago:
- Optimize performance for queries on recent rows of a large table
Updated it a bit to reflect recent developments.
Aside: now()
returns timestamptz
, not timestamp
. LOCALTIMESTAMP
would be a better fit. But don't go there.
Related Topics
SQL Different Between Left Join On... and Left Join On..Where
Delimited Function in SQL to Split Data Between Semi-Colon
Sqllite Strftime Function to Get Grouped Data by Months
Generate a Unique Time-Based Id on a Table in SQL Server
How to Concatenate Text from Multiple Rows into a Single Text String in Oracle Server
How to Group by the Each Week Upto Last Six Week Sundays Dates in SQL
How to Add a Column to Large SQL Server Table
Access/SQL - Too Few Parameters
SQL Server - Can You Add Field Descriptions in Create Table
SQL Error: "Name Already Used by an Existing Constraint"
Informix 7.3 Isql Insert Statement - Text/Blob/Clob Field Insert Error
Dynamic SQL Column Value Duplicate and Difference Detection Merge Query
Using Isnull or Select Coalesce in Linq..
How to Return a Table from a Stored Procedure