O'Reilly logo

Rails, Angular, Postgres, and Bootstrap by David B. Copeland

Stay ahead with the world's most comprehensive technology and business learning platform.

With Safari, you learn the way you learn best. Get unlimited access to videos, live online training, learning paths, books, tutorials, and more.

Start Free Trial

No credit card required

Indexing Derived and Partial Values

Postgres allows you to create an index on transformed values of a column. This means we can create an index on the lowercased value for each of our three fields. Further, we can configure the index in a way that allows Postgres to optimize for the “starts with” search we are doing. Here’s the basic syntax:

 CREATE​ ​INDEX
  customers_lower_last_name
 ON
  customers (lower(last_name) varchar_pattern_ops);

If you’re familiar with creating indexes in general, the varchar_pattern_ops might look odd. This is a feature of Postgres called operator classes. Specifying an operator class isn’t required; however, the default operator class used by Postgres will only optimize the index for an exact match. Because ...

With Safari, you learn the way you learn best. Get unlimited access to videos, live online training, learning paths, books, interactive tutorials, and more.

Start Free Trial

No credit card required