Postgres index naming

Reference from satck overflow

The standard names for indexes in PostgreSQL are:

{tablename}_{columnname(s)}_{suffix}

where the suffix is one of the following:

• pkey for a Primary Key constraint
• key for a Unique constraint
• excl for an Exclusion constraint
• idx for any other kind of index
• fkey for a Foreign key
• check for a Check constraint

Standard suffix for sequences is

• seq for all sequences

Proof of your UNIQUE-constraint:

NOTICE: CREATE TABLE / UNIQUE will create implicit index “example_a_b_key” for table “example”

Demonstration

Create an index

create index my_table_column_a_idx on my_table(column_a);

Create a multicolumn index

create index my_table_column_a_column_b_idx on my_table(column_a, column_b);

Create a unique index

create unique index my_table_column_a_key on my_table(column_a);

Create a multicolumn unique index

create unique index my_table_column_a_column_b_key on my_table(column_a, column_b);