3 ms·
Something really cool you can do is functional unique indexes: CREATE UNIQUE INDEX foo_fidx ON foo (lower(a)); Will reject values of "a" that have the sam
by jeffdavis 13y ago
Something really cool you can do is functional unique indexes:
CREATE UNIQUE INDEX foo_fidx ON foo (lower(a));
Will reject values of "a" that have the same lower-case value as something that already exists, even if the values of "a" are different. Exclusion Constraints[1] can make use of this, as well.
Also, you can do partial indexes, which have a WHERE clause. You can combine those with unique indexes, too:
CREATE UNIQUE INDEX foo_idx ON foo (lower(a))
WHERE b <> 0;
Exclusion constraints also accept a WHERE clause.
Additionally, if you do a partial and/or functional index, then postgres will collect stats specific to the function and predicate, which improves plans even if the index isn't used.
[1] http://www.postgresql.org/docs/current/static/sql-createtable.html#SQL-CREATETABLE-EXCLUDE http://www.postgresql.org/docs/current/static/sql-createtabl...