This page in other versions: Unsupported versions: 7.2 / 7.3

7.5. Functional Indexes

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 indexes 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 function:

SELECT * FROM test1 WHERE lower(col1) = 'value';

This query can use an index, if one has been 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 indexes are always single-column (namely, the function result) even if the function uses more than one input field; there cannot be multicolumn indexes that contain function calls.

Tip: The restrictions mentioned in the previous paragraph can easily be worked around by defining a custom function to use in the index definition that computes any desired result internally.


Oct. 8, 2002, 3:18 p.m.

This is a example of a functional index. I found this on the SQL mailinglist.
It makes sure that this:
- INSERT INTO test (c1,c2) VALUES('a','c');
- INSERT INTO test (c1,c2) VALUES('c','a');
Will fail.

CREATE FUNCTION ord_fn (text,text) RETURNS text AS '
WHEN $1 < $2
THEN $1 || $2
ELSE $2 || $1
END) as t;
' LANGUAGE SQL WITH (iscachable);

CREATE TABLE foo (a text, b text);
CREATE UNIQUE INDEX foo_both_uniq ON foo ( ord_fn(a,b) );

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