An index is a physical database object that is defined on a table column or a list of columns. In PostgreSQL, there are many types of indexes and several ways to use them. Indexes can be used, in general, to do the following:
- Optimize performance: an index allows the efficient retrieval of a small number of rows from the table. Whether or not a number of rows is considered small is determined by the total number of rows in the table and execution planner settings.
- Validate constraints: An index can be used to validate the constraints on several rows. For example, the
UNIQUEcheck constraint creates a unique index on the column behind the scenes.
The following example shows how to use GIST to forbid overlapping between date ranges. For more information, have a look at https://www.postgresql.org/docs/current/static/rangetypes.html
CREATE TABLE no_date_overlap (
date_range daterange,
EXCLUDE USING GIST (date_range WITH &&)
);To test date range overlapping:
car_portal=# INSERT...