You can check, if an index with a given name exists with this statement.

If your index name is some_table_some_field_idx

SELECT count(*) > 0
FROM pg_class c
WHERE c.relname = 'some_table_some_field_idx' 
AND c.relkind = 'i';

Starting from Postgres 9.5 you can even use

CREATE INDEX IF NOT EXISTS
Answer from Torge on Stack Overflow
🌐
PostgreSQL
postgresql.org › docs › current › sql-createindex.html
PostgreSQL: Documentation: 18: CREATE INDEX
May 14, 2026 - Index name is required when IF NOT EXISTS is specified. ... The optional INCLUDE clause specifies a list of columns which will be included in the index as non-key columns. A non-key column cannot be used in an index scan search qualification, and it is disregarded for purposes of any uniqueness or exclusion constraint enforced by the index.
Top answer
1 of 2
137

Index names in PostgreSQL

  • Index names are unique across a single database schema.
  • Index names cannot be the same as any other index, (foreign) table, (materialized) view, sequence or user-defined composite type in the same schema.
  • Two tables in the same schema cannot have an index of the same name. (Follows logically.)

If you do not care about the name of the index, have Postgres auto-name it:

CREATE INDEX ON tbl1 (col1);

is (almost) the same as:

CREATE INDEX tbl1_col1_idx ON tbl1 USING btree (col1);

Except that Postgres will avoid a naming collisions and automatically pick the next free name:

tbl1_col1_idx 
tbl1_col1_idx2
tbl1_col1_idx3
...

Just try it. But, obviously, you would not want to create multiple redundant indexes. So it wouldn't be a good idea to just blindly create a new one.

Test for existence

Postgres 9.5 or newer

Now available:

CREATE INDEX IF NOT EXISTS ...

Also works for CREATE INDEX CONCURRENTLY IF NOT EXISTS.

However, the manual warns:

Note that there is no guarantee that the existing index is anything like the one that would have been created.

It's a plain check for the object name. (Applies to variants for older versions below, too.)
To find existing indexes on the same table for the same column(s):

SELECT pg_get_indexdef(indexrelid)
FROM   pg_index
WHERE  indrelid = 'public.big'::regclass
AND   (indkey::int2[])[:] = ARRAY (
   SELECT attnum
   FROM   unnest('{usr_id, created_at}'::text[]) WITH ORDINALITY i(attname, ord)
   JOIN  (
      SELECT attname, attnum
      FROM   pg_attribute
      WHERE  attrelid = 'public.big'::regclass
      ) a USING (attname)
   ORDER BY ord
   );

Restrictions:

  • Only works for columns, not other index expressions.
  • Also reports partial indexes (with WHERE clause) and covering indexes (with INCLUDE clause).
  • Reports any type of index, not just B-tree indexes.

Study the results (if any) before proceeding, or refine the query to your needs ...

Further reading:

  • Find tables with multiple indexes on same column
  • Normalize array subscripts for 1-dimensional array so they start with 1

Postgres 9.4

You can use the new function to_regclass() to check without throwing an exception:

DO
$$
BEGIN
   IF to_regclass('myschema.mytable_mycolumn_idx') IS NULL THEN
      CREATE INDEX mytable_mycolumn_idx ON myschema.mytable (mycolumn);
   END IF;

END
$$;

Returns NULL if an index (or another object) of that name does not exist. See:

  • How to check if a table exists in a given schema

This doesn't work for CREATE INDEX CONCURRENTLY, since that variant cannot be wrapped in an outer transaction. See comment by @Gregory below.

Postgres 9.3 or older

Cast the schema-qualified name to regclass:

SELECT 'myschema.myname'::regclass;

If it throws an exception, the name is free.
Or, to test the same without throwing an exception, use a DO statement:

DO
$$
BEGIN
   IF NOT EXISTS (
      SELECT
      FROM   pg_class c
      JOIN   pg_namespace n ON n.oid = c.relnamespace
      WHERE  c.relname = 'mytable_mycolumn_idx'
      AND    n.nspname = 'myschema'
   ) THEN
    
        CREATE INDEX mytable_mycolumn_idx ON myschema.mytable (mycolumn);
    END IF;
END
$$;

The DO statement was introduced with Postgres 9.0. In earlier versions you have to create a function to do the same.
Details about pg_class in the manual.
Basics about indexes in the manual.

2 of 2
13

It will be available in 9.5. Here is actual git commit https://github.com/postgres/postgres/commit/08309aaf74ee879699165ec8a2d53e56f2d2e947

Discussion on pg hackers http://postgresql.nabble.com/CREATE-IF-NOT-EXISTS-INDEX-td5821173.html

🌐
PostgreSQL
postgresql.org › docs › current › indexes-unique.html
PostgreSQL: Documentation: 18: 11.6. Unique Indexes
May 14, 2026 - ... Unsupported versions: 13 / ... of the combined values of more than one column. CREATE UNIQUE INDEX name ON table (column [, ...]) [ NULLS [ NOT ] DISTINCT ];...
🌐
Sqliz
sqliz.com › postgresql › create-index
PostgreSQL Create Indexes
The UNIQUE Instructs to create a unique index. The IF NOT EXISTS instructs the index to be created only if the specified index name does not exist. PostgreSQL automatically creates indexes on primary key columns.
🌐
VMware
techdocs.broadcom.com › us › en › vmware-tanzu › data-solutions › tanzu-greenplum › 7 › greenplum-database › ref_guide-sql_commands-CREATE_INDEX.html
CREATE INDEX - Broadcom TechDocs
1 month ago - Refer to Operator Classes and Operator Families and Interfacing Extensions to Indexes in the PostgreSQL documentation for more information about operator classes. When CREATE INDEX is invoked on a partitioned table, the default behavior is to recurse to all partitions to ensure they all have matching indexes. Each partition is first checked to determine whether an equivalent index already exists, and if so, that index will become attached as a partition index to the index being created, which will become its parent index.
🌐
PostgreSQL Tutorial
pgtutorial.com › home › postgresql tutorial › postgresql unique index
PostgreSQL Unique Index
March 7, 2025 - First, specify the index name after the CREATE UNIQUE INDEX keywords. If you omit it, PostgreSQL will automatically generate a name. Second, provide the table name on which you want to create the index. Third, list one or more columns included in the index. Third, the NULL NOT DISTINCT treats ...
🌐
PostgreSQL
postgresql.org › docs › 9.5 › sql-createindex.html
PostgreSQL: Documentation: 9.5: CREATE INDEX
June 9, 2021 - Index name is required when IF NOT EXISTS is specified. ... The name of the index to be created. No schema name can be included here; the index is always created in the same schema as its parent table.
Find elsewhere
🌐
Medium
medium.com › dovetail-engineering › how-to-safely-create-unique-indexes-in-postgresql-e35980e6beb5
How to safely create unique indexes in PostgreSQL | by Dovetail Engineering | Dovetail Engineering | Medium
May 23, 2023 - 📚 An upsert is a piece of DB ... columns. In PostgreSQL, this is achieved by using ON CONFLICT DO UPDATE which requires us to have a unique index matching that identifier....
🌐
Javatpoint
javatpoint.com › postgresql-unique-index
PostgreSQL UNIQUE Index - javatpoint
PostgreSQL UNIQUE Index with examples database, table, create, select, insert, update, delete, join, function, index, clause, trigger, view, procedure etc.
🌐
Hacker News
news.ycombinator.com › item
Don't rely on IF NOT EXISTS for concurrent index creation in PostgreSQL | Hacker News
August 14, 2024 - In my experience on large tables that are “busy”, sometimes indexes need to be added manually first from a utility session, perhaps inside tmux/screen that's detached from while they are created. This could take hours for large tables. Then once done, and the index is valid, an Active Record ...
🌐
GeeksforGeeks
geeksforgeeks.org › postgresql › postgresql-unique-index
PostgreSQL - UNIQUE Index - GeeksforGeeks
July 15, 2025 - Therefore, PostgreSQL created two UNIQUE indexes, one for each column. ... CREATE TABLE employees ( employee_id SERIAL PRIMARY KEY, first_name VARCHAR(255) NOT NULL, last_name VARCHAR(255) NOT NULL, email VARCHAR(255) UNIQUE );.
🌐
PostgreSQL Tutorial
pgtutorial.com › home › postgresql tutorial › postgresql create index statement
PostgreSQL CREATE INDEX Statement
March 7, 2025 - 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)
🌐
Neon
neon.com › postgresql › indexes › unique-index
PostgreSQL UNIQUE Index
When you define a primary key or a unique constraint for a table, PostgreSQL automatically creates a corresponding unique index. Let's explore some examples of using the PostgreSQL unique indexes.
🌐
Flatiron
resources.flatiron.com › flatiron-stories › uniqueness-in-postgresql-constraints-versus-indexes
Uniqueness in PostgreSQL: Constraints versus Indexes
March 29, 2024 - PostgreSQL Documentation - Unique Indexes · The documentation for unique constraint specifies that a unique index is created under the hood upon creation of a unique constraint. This means we're already getting a unique index with each unique constraint, and adding another unique index is simply duplicating the one underneath our unique constraint. If you think about it, this makes perfect sense from a performance perspective; a constraint needs to quickly look up all values in a column upon insert or update in order to verify duplicates have not been introduced.
🌐
dbt
docs.getdbt.com › platform-specific configs
Postgres configurations | dbt Developer Hub
3 weeks ago - For the index's name, dbt uses a hash of its properties and the current timestamp, in order to guarantee uniqueness and avoid namespace conflict with other indexes. create index if not exists "3695050e025a7173586579da5b27d275" on "my_target_database"."my_target_schema"."indexed_model" using hash (column_a); create unique index if not exists "1bf5f4a6b48d2fd1a9b0470f754c1b0d" on "my_target_database"."my_target_schema"."indexed_model" (column_a, column_b);
🌐
Pretagteam
pretagteam.com › question › create-unique-index-if-not-exists-in-postgresql
Pretagteam
November 3, 2021 - Questions - Find the Best Solution For Your questions