Expression index
 | This article has multiple issues. Please help improve it or discuss these issues on the talk page. (Learn how and when to remove these messages)
|
An expression index is a database index that is built on a generic expression, rather than one or more columns. This allows indexes to be defined for common query conditions that depend on data in a table, but are not actually stored in that table.
A common use for an expression index is to support case-insensitive searching or constraints. For example, if a web site wants to make user names case-insensitive, but still preserve the case as originally entered by the user, a unique index can be created on the upper- or lower-case representation of the user name:
CREATE INDEX users__last_name_lower ON users( lower( last_name ) );
That will create a unique index on "lower(last_name)". Any queries that search on "lower(last_name)" could then make use of that index:
SELECT user_id FROM users WHERE lower( last_name ) = 'smith';