This page in other versions: Unsupported versions: 7.1

7.5. Functional Indices

For a functional index, an index is defined on the result of a function applied to one or more columns of a single table. Functional indices can be used to obtain fast access to data based on the result of function calls.

For example, a common way to do case-insensitive comparisons is to use the lower:

SELECT * FROM test1 WHERE lower(col1) = 'value';
In order for that query to be able to use an index, it has to be defined on the result of the lower(column) operation:
CREATE INDEX test1_lower_col1_idx ON test1 (lower(col1));

The function in the index definition can take more than one argument, but they must be table columns, not constants. Functional indices are always single-column (namely, the function result) even if the function uses more than one input field; there cannot be multi-column indices that contain function calls.

Tip: The restrictions mentioned in the previous paragraph can easily be worked around by defining custom functions to use in the index definition that call the desired function(s) internally.

Submit correction

If you see anything in the documentation that is not correct, does not match your experience with the particular feature or requires further clarification, please use this form to report a documentation issue.

Privacy Policy | About PostgreSQL
Copyright © 1996-2016 The PostgreSQL Global Development Group