Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. Join them; it only takes a minute:

Sign up
Here's how it works:
  1. Anybody can ask a question
  2. Anybody can answer
  3. The best answers are voted up and rise to the top

I've read that PostgreSQL can have "functional" indexes. For that you need the immutable keyword.

Seen that PostgreSQL is a CRUD DB I don't understand at all how it can possibly work in a functional way.

If there's an update or a delete to the DB, how can a past query guarantee that the result of the same query is always going to be the same?

share|improve this question
the inclusion of a functional feature does not mean that the language is purely functional, nore does the primary paradigm being something else preclude a functional feature. – xenoterracide Jan 11 '13 at 15:55
up vote 8 down vote accepted

I think you are getting confused with functional programming languages.

Anyway, a functional index is used to speed up access to data where queries that are accessing the data are using database functions.

By way of example, a query such as select blah from foo where sqrt(blah)=4 would have to perform the sqrt() operation for each value of blah. You could optimise this by pre-calculating all values of sqrt(blah) in a functional index: create index foo_func on foo (sqrt(blah));

This is where immutability comes in. A function is immutable if and only if the same input will always give the same output. Functional indexes can only use immutable functions. This guarantees that any queries for a given value and related functional index value will always return the same result.

share|improve this answer

Your Answer


By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.