Menu
Home
People
Places
Arts
History
Plants & Animals
Science
Life & Culture
Technology
Reference.org
Expression index
open-in-new
<p>Within <a href="/facts/Computing/Kghtm9fX">computing</a> and <a href="/facts/Computer_science/lN3pEyPz">computer science</a>, an expression index, also known as a function based index, is a <a href="/facts/Relational_database_management_system/pEKGmJLy">database</a> <a href="/facts/Index_(database)/TRqmk9It">index</a> that is built on a generic expression, rather than one or more <a href="/facts/Column_(database)/WB91xSVs">columns</a>. 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. </p><p>A common use for an expression index is to support <a href="/facts/Case_sensitivity/VS2eGDmY">case-insensitive</a> 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, an index can be created on the lower-case representation of the user name: </p> CREATE INDEX users__last_name_lower ON users( lower( last_name ) ); <p>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: </p> SELECT user_id FROM users WHERE lower( last_name ) = lower( 'Smith' );