Anthony Sotolongo León: Limitations in Postgres Index definition

Introduction Indexes are a core RDBMS component and Postgres ain’t an exception. Furthermore, Postgres indexing capabilities aren’t tied to the available native-supported mechanisms. Aside from the available 6 types of native indexes (B-tree, Hash, GiST, SP-GiST, GIN, BRIN) it is even possible to extend index capabilities using the Access Method Extensions Interface for customizing and building your own. There is a subcategory of index types that are classified according to their expressions and definitions, and they can use custom operators to extend the usage of more advanced data types. Those subcategories can be Multicolumn, Unique, Expressional, Partial, and Covering Indexes. Note: It is possible to implement other types of indexes and add them to PostgreSQL as extensions for example: https://www.postgresql.org/docs/current/bloom.html or https://github.com/postgrespro/rum Nevertheless, under certain circumstances, there are certain limitations on index definitions. This is precisely the objective of the current blog: to show under what circumstances you can not create indexes. Index functions must be IMMUTABLE The following error is very likely to be the most common one of this type of restriction, and it is in response to the restriction that when you use a function in an index expression, this function must be IMMUTABLE, this is one of “Function Volatility Categories”, please read the documentation to understand the differences between categories. mydatabase=>CREATE INDEX CONCURRENTLY idx_test on table_test (test_id, to_char(create_time, 'YYYY-MM-DD') ); ERROR: functions in index expression must be marked IMMUTABLE If it describes the to_char function is possible to see that this function is marked as STABLE mydatabase=>\df+ to_char Schema | pg_catalog Name | to_char Result data type | text Argument data types | timestamp with time zone, text Type | func Volatility | stable Parallel | safe Owner [...]