How to Create PostgreSQL Index and Postgres Create Index examples | How to drop index in Postgresql

 

PostgreSQL Index | Postgres Index Types | Can we CREATE INDEX in Postgresql?

PostgreSQL has several index types: B-tree, Hash, GiST, SP-GiST, GIN and BRIN. Each Index type uses a different algorithm that is best suited to different types of queries. By default, the postgres CREATE INDEX command creates B-tree indexes, which fit the most common situations. 

Postgres CREATE INDEX | Postgresql Index

When you create index PostgreSQL keep in mind that the postgresql index name should be the meaningful name and it should be identifiable to which table this index is created. So the PostgreSQL index naming should be easy to remember and meaningful. Specify any of the index type like B-tree, Hash, GiST, SP-GiST, GIN and BRIN, if not specified any index type by default it will create the post gre sql index with the type B-tree. The basic syntax of postgres CREATE INDEX statement is as follows.

CREATE INDEX index_name ON table_name;

Singlecolumn Index

An index created on a single column in a table is known as singlecolumn index. The basic syntax for singlecloumn index in postgres is as follows

CREATE INDEX IndexName ON TableName (ColumnName);

An example for singlecolumn index in postgres is as follows

CREATE INDEX index_employee_name ON employee(name);

Can we CREATE INDEX on multiple columns in Postgresql?

Multicolumn Index | Create Index postgres multiple columns

An index created on more than one column is known as a multicolumn index.  The basic syntax for multiplecloumn index in postgres is as follows 

CREATE INDEX IndexName ON TableName (ColumnName1,ColumnName2);

An example for the multicolumn index in postgres is as follows

CREATE INDEX index_employee_code_name ON employee (code,name);

Postgres add Unique Constraint - Unique Index

Unique indexes are used to prevent duplicate data.

CREATE UNIQUE INDEX index_employee_code ON employee (code);

Postgres DROP INDEX

DROP INDEX in postgres drops an existing index from the database. You must be the index owner in order to run this command.

DROP INDEX index_employee_name;

Postgres show indexes

PostgreSQL list indexes created in a table using the pg_indexes view. The details about index created in a PostgreSQL database can be retrieve from the pg_indexes view. These five columns in the pg_indexes view gives the useful information about the indexes created in postgresql table. 

schemaname - Displays the name of the schema that contains the tables and indexes

tablename - Displays the name of the tables of indexes

indexname - Displays the index names

tablespace - Displays the tablespace name that contains the indexes

indexdef - Displays the create index statement

Execute below sql statement to show the all postgresql index details in postgresql database.

SELECT * FROM pg_indexes WHERE schemaname = 'public';

Execute below sql statement to show the postgresql index details of a specific table.

SELECT * FROM pg_indexes WHERE schemaname = 'public' AND tablename = 'item';