Index expressions are relatively expensive to maintain, because the derived expression(s) must be computed for each row insertion and non-HOT update. The parentheses can be omitted when the expression is just a function call, as in the first example. The syntax of the CREATE INDEX command normally requires writing parentheses around index expressions, as shown in the second example. Then it might be worth creating an index like this:ĬREATE INDEX people_names ON people ((first_name || ' ' || last_name))
SELECT * FROM people WHERE (first_name || ' ' || last_name) = 'John Smith' Thus, indexes on expressions can be used to enforce constraints that are not definable as simple unique constraints.Īs another example, if one often does queries like: If we were to declare this index UNIQUE, it would prevent creation of rows whose col1 values differ only in case, as well as rows whose col1 values are actually identical. This query can use an index if one has been defined on the result of the lower(col1) function:ĬREATE INDEX test1_lower_col1_idx ON test1 (lower(col1)) SELECT * FROM test1 WHERE lower(col1) = 'value' This feature is useful to obtain fast access to tables based on the results of computations.įor example, a common way to do case-insensitive comparisons is to use the lower function: An index column need not be just a column of the underlying table, but can be a function or scalar expression computed from one or more columns of the table.
