postgres drop unique index

Remove uniqueness of index in PostgreSQL

stackoverflow.com › questions › 18514759 › remove-uniqueness-of-index-in-postgresql

You may be able to remove the unique CONSTRAINT, and not the INDEX itself.

Check your CONSTRAINTS via select * from information_schema.table_constraints;

Then if you find one, you should be able to drop it like:

ALTER TABLE <my_table> DROP CONSTRAINT <constraint_name>

Edit: a related issue is described in this question

Answer from user2062950 on Stack Overflow

PostgreSQL

postgresql.org › docs › current › sql-dropindex.html

PostgreSQL: Documentation: 18: DROP INDEX

May 14, 2026 - July 16, 2026: PostgreSQL 19 Beta 2 Released! ... Unsupported versions: 13 / 12 / 11 / 10 / 9.6 / 9.5 / 9.4 / 9.3 / 9.2 / 9.1 / 9.0 / 8.4 / 8.3 / 8.2 / 8.1 / 8.0 / 7.4 / 7.3 / 7.2 / 7.1 ... DROP INDEX drops an existing index from the database system.

Stack Overflow

stackoverflow.com › questions › 18514759 › remove-uniqueness-of-index-in-postgresql

sql - Remove uniqueness of index in PostgreSQL - Stack Overflow

Top answer

1 of 4

168

You may be able to remove the unique CONSTRAINT, and not the INDEX itself.

Check your CONSTRAINTS via select * from information_schema.table_constraints;

Then if you find one, you should be able to drop it like:

ALTER TABLE <my_table> DROP CONSTRAINT <constraint_name>

Edit: a related issue is described in this question

2 of 4

Assume you have the following:

Indexes:
    "feature_pkey" PRIMARY KEY, btree (id, f_id)
    "feature_unique" UNIQUE, btree (feature, f_class)
    "feature_constraint" UNIQUE CONSTRAINT, btree (feature, f_class)

To drop the UNIQUE CONSTRAINT, you would use ALTER TABLE:

ALTER TABLE feature DROP CONSTRAINT feature_constraint;

To drop the PRIMARY KEY, you would also use ALTER TABLE:

ALTER TABLE feature DROP CONSTRAINT feature_pkey;

To drop the UNIQUE [index], you would use DROP INDEX:

DROP INDEX feature_unique;

sql - Add unique constraint to an existing PostgreSQL index

sql - Postgres unique constraint vs index

postgresql - Does creating a unique constraint also create an index in Postgres?

postgresql - How to drop a unique constraint on a column in Postgres?

How do I reverse a Postgresql unique index constraint in a rails migration?

More results from stackoverflow.com

Stack Exchange

dba.stackexchange.com › questions › 326734 › drop-unique-index-of-a-unique-constraint

postgresql - Drop unique index of a unique constraint - Database Administrators Stack Exchange

May 4, 2023 - CREATE UNIQUE INDEX IF NOT EXISTS _index_measure_timestamp_variable ON measures (timestamp DESC, variable_id DESC ); It creates another index which is duplicated with the constraint index. The only difference is the order. ... Thx for your help. ... There should be no reason to change an index order from ASC to DESC, because Postgresql can use the same index for queries in ASCending or DESCending order without performance loss.

Neon

neon.com › postgresql › indexes › drop-index

PostgreSQL DROP INDEX Statement

The statement removed the index as expected. Use the PostgreSQL DROP INDEX statement to delete an index.

PostgreSQL

postgresql.org › docs › 7.2 › sql-altertable.html

PostgreSQL: Documentation: 7.2: ALTER TABLE

January 1, 2012 - The CASCADE option is unsupported. Currently DROP CONSTRAINT drops only CHECK constraints. To remove a PRIMARY or UNIQUE constraint, drop the relevant index using the DROP INDEX command.

GeeksforGeeks

geeksforgeeks.org › postgresql › postgresql-drop-index

PostgreSQL - DROP INDEX - GeeksforGeeks

July 15, 2025 - If the index has dependent objects (e.g., unique constraints), we can use the CASCADE option to automatically drop those objects. However, be careful with CASCADE, as it will remove all objects that depend on the index.

GitHub

github.com › tortoise › aerich › issues › 180

PostgreSQL does not allow dropping unique indexes via DROP INDEX · Issue #180 · tortoise/aerich

July 24, 2021 - If a migration containing a "DROP INDEX" command with a unique index is executed on PostgreSQL, an error is raised, claiming asyncpg.exceptions.DependentObjectsStillExistError: cannot drop index uid_imagetempla_guild_i_25a69e because con...

Author tortoise

PostgreSQL

postgresql.org › docs › current › indexes-unique.html

PostgreSQL: Documentation: 18: 11.6. Unique Indexes

May 14, 2026 - 11.6. Unique Indexes # Indexes can also be used to enforce uniqueness of a column's value, or the uniqueness of the …

Stack Exchange

dba.stackexchange.com › questions › 342040 › postgres-drop-create-unique-index-concurrently

postgresql - postgres drop/create unique index concurrently - Database Administrators Stack Exchange

1. `CREATE INDEX CONCURRENTLY`

... but I could apply two commands with CREATE INDEX CONCURRENTLY

You can create any number of indexes with CREATE INDEX CONCURRENTLY. They just cannot be created concurrently. Only one index can be built concurrently at a time. You probably built them sequentially, i.e. one at a time. CONCURRENTLY is not a property of the index, but an instruction how to build it.

Seems related to this misunderstanding:

CREATE INDEX CONCURRENTLY is executed but the CONCURRENTLY option lost after creation?

2. `DROP INDEX CONCURRENTLY`

I [...] removed an index with DROP INDEX CONCURRENTLY that was UNIQUE.

UNIQUE constraints (ALTER TABLE tbl ADD CONSTRAINT ...) are part of the SQL standard and implemented in Postgres using a UNIQUE index, which you can also create on its own (CREATE UNIQUE INDEX ...). The functionality of the former is a superset of the later.

You can drop a unique index CONCURRENTLY, but not if it's just an implementation detail of a constraint. That would need the CASCADE functionality which is not supported - as per your quote.

What does a CONSTRAINT have to do with my unique index?
How does PostgreSQL enforce the UNIQUE constraint / what type of index does it use?

Find elsewhere

Google Bing Mojeek

Neon

neon.com › postgresql › indexes › unique-index

PostgreSQL UNIQUE Index

First, specify the index name in the CREATE UNIQUE INDEX statement. Second, provide the name of the table along with a list of indexed columns in the ON clause. Third, the NULLS NOT DISTINCT option treats nulls as equal, whereas NULLS DISTINCT treats nulls as distinct values. By default, the statement uses NULLS DISTINCT, meaning that the indexed column may contain multiple nulls. PostgreSQL offers multiple index types, but only the B-tree index type supports unique indexes.

DataCamp

datacamp.com › doc › postgresql › dropping-unused-indexes

PostgreSQL Dropping Unused Indexes

In this syntax, DROP INDEX IF EXISTS index_name removes the index if it exists, preventing error messages if the index is not present.

PostgreSQL Tutorial

pgtutorial.com › home › postgresql tutorial › postgresql drop index statement

PostgreSQL DROP INDEX statement

March 7, 2025 - DROP INDEX [CONCURRENTLY] [IF EXISTS] index_name [CASCADE | RESTRICT]Code language: PostgreSQL SQL dialect and PL/pgSQL (pgsql)

Postgres Professional

postgrespro.com › list › thread-id › 2639979

Thread: Dropping behavior for unique CONSTRAINTs : Postgres Professional

On 3/4/23 02:03, Peter J. Holzer wrote: [snip] > So your plan is to create a unique constraint (backed by a unique > index) and then to drop the index and keep the constraint? > > That doesn't work. A unique constraint can't exist without a (unique) > index. Think about it: With a unique constraint PostgreSQL needs to > check for every insert whether the value already exists in the table.

Liquibase

docs.liquibase.com › change-types › drop-unique-constraint.html

dropUniqueConstraint

Drops an existing unique constraint. Note: For OracleDB, the index associated with the constraint is also dropped.

PostgreSQL

docs.postgresql.fr › 17 › sql-dropindex.html

DROP INDEX

DROP INDEX [ IF EXISTS ] nom [, ...] [ CASCADE | RESTRICT ] DROP INDEX [ CONCURRENTLY ] [ IF EXISTS ] nom [, ...] [ CASCADE | RESTRICT ]

GeeksforGeeks

geeksforgeeks.org › postgresql-unique-index

PostgreSQL - UNIQUE Index - GeeksforGeeks

November 18, 2024 - In PostgreSQL, there are instances where you might need to remove unnecessary or obsolete columns from your database tables. The DROP COLUMN clause in the ALTER TABLE statement allows you to do this with ease. When you drop a column from a table, ...

Tpoint Tech

tpointtech.com › postgresql-drop-index

PostgreSQL Drop Index - Tpoint Tech

PostgreSQL REINDEX In this section, we are going to understand the working of the PostgreSQL REINDEX command, which is used to recreate multiple indexes and see the difference between the REINDEX command, drop and create index command. What is the PostgreSQL REINDEX command? In reality, the PostgreSQL index... ... In this section, we are going to understand the working of the and the examples of the Create indexes command. Why we use the PostgreSQL Create indexes command? In PostgreSQL, the Create indexes command is used to create a new index by defining the index name and... ... PostgreSQL UNIQUE Index In this section, we are going to understand the working of the UNIQUE Index, which is used to make sure the individuality of data in various columns.

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.

CockroachDB

cockroachlabs.com › statements › drop index

DROP INDEX - CockroachDB

PostgreSQL

postgresql.org › message-id › CAF-QHFXWM2aqb_Lt-qfC8ZQvd8NchdbtsnH8j23w67CjzN0Bfw@mail.gmail.com

PostgreSQL: DROP INDEX CASCADE doesn't want to drop unique constraints?

April 24, 2017 - "employer_employerid_key" UNIQUE CONSTRAINT, btree (employerid) I get this error: ERROR: cannot drop index employer_employerid_key because constraint employer_employerid_key on table employer requires it HINT: You can drop constraint employer_employerid_key on table employer instead. I'm using the CASCADE and IF EXISTS arguments and the docs say nothing about any special cases ( https://www.postgresql.org/docs/9.3/static/sql-dropindex.html) This is with PostgreSQL 9.3.15.

1. CREATE INDEX CONCURRENTLY

2. DROP INDEX CONCURRENTLY

1. `CREATE INDEX CONCURRENTLY`

2. `DROP INDEX CONCURRENTLY`