PostgreSQL CREATE INDEX Statement

Summary: In this tutorial, you’ll learn how to create an index for one or more columns of a table using the PostgreSQL CREATE INDEX statement.

Introduction to PostgreSQL CREATE INDEX statement #

The CREATE INDEX statement allows you to create an index on one or more columns of a table.

Here’s the basic syntax of the CREATE INDEX statement:

CREATE INDEX [IF NOT EXISTS] [index_name]
ON  tablename (column1, column2);Code language: PostgreSQL SQL dialect and PL/pgSQL (pgsql)

In this syntax:

• First, specify the index name after the CREATE INDEX keywords. The index_name is optional. If you don’t provide a name, PostgreSQL implicitly generates a name and assigns it to the index.
• Second, use the IF NOT EXISTS to instruct PostgreSQL not to throw an error if an index with the same name already exists. In this case, PostgreSQL will issue a notice instead.
• Third, provide the table’s name to create an index.
• Finally, specify one or more columns to index within the braces.

PostgreSQL uses the following naming convention to generate an index name:

tablename_column_idxCode language: PostgreSQL SQL dialect and PL/pgSQL (pgsql)

If an index consists of two or more columns, the generated index name would be:

tablename_column1_column2_idxCode language: PostgreSQL SQL dialect and PL/pgSQL (pgsql)

If you don’t create an index with an explicit name for the same set of columns, PostgreSQL will create redundant indexes with different names but for the same set of columns.

PostgreSQL CREATE INDEX statement example #

First, create an index on the transactions table for the type column:

CREATE INDEX ON transactions(type);Code language: PostgreSQL SQL dialect and PL/pgSQL (pgsql)

Try it

This statement creates a new index with the name transations_type_idx.

Second, show the index of the transactions table using the following query:

SELECT
  indexname,
  indexdef
FROM
  pg_indexes
WHERE
  tablename = 'transactions';Code language: PostgreSQL SQL dialect and PL/pgSQL (pgsql)

Try it

Output:

       indexname       |                                         indexdef
-----------------------+-------------------------------------------------------------------------------------------
 transactions_pkey     | CREATE UNIQUE INDEX transactions_pkey ON public.transactions USING btree (transaction_id)
 transactions_type_idx | CREATE INDEX transactions_type_idx ON public.transactions USING btree (type)Code language: PostgreSQL SQL dialect and PL/pgSQL (pgsql)

Creating an index with two columns #

The following statement creates an index on the inventories table, which includes two columns safety_stock and gross_weight:

CREATE INDEX ON products (safety_stock, gross_weight);Code language: PostgreSQL SQL dialect and PL/pgSQL (pgsql)

Try it

PostgreSQL generates the following index name:

products_safety_stock_gross_weight_idxCode language: PostgreSQL SQL dialect and PL/pgSQL (pgsql)

Summary #

• Use the CREATE INDEX statement to create a new index.

Quiz #