Logo Questions Linux Laravel Mysql Ubuntu Git Menu
HTML CSS JAVASCRIPT SQL PYTHON PHP BOOTSTRAP JAVA JQUERY R React Kotlin
 

If I drop a PG table with an index, does the index still exist?

Tags:

postgresql

postgresql-9.2

If I'm dropping and re-creating a Postgres table that is indexed, will the index still exist?

Do I need to drop and re-index the table after re-creating it or do I just need to re-create the index on the new table and leave the cleaning up of the old index to Postgres?

like image 372
AK47 Avatar asked Jul 20 '16 13:07

AK47

People also ask

Does dropping a table also drop the index?

DROP TABLE removes tables from the database. Only the table owner, the schema owner, and superuser can drop a table. To empty a table of rows without destroying the table, use DELETE or TRUNCATE . DROP TABLE always removes any indexes, rules, triggers, and constraints that exist for the target table.

Does drop index lock table Postgres?

When you execute the DROP INDEX statement, PostgreSQL acquires an exclusive lock on the table and block other accesses until the index removal completes. To force the command waits until the conflicting transaction completes before removing the index, you can use the CONCURRENTLY option.

How do you delete a index from a table in Postgres?

In PostgreSQL, the DROP INDEX statement to remove an existing index. Syntax: DROP INDEX [ CONCURRENTLY] [ IF EXISTS ] index_name [ CASCADE | RESTRICT ]; Let's analyze the above syntax: index_name : This is used to specify the name of the index that you want to remove after the DROP INDEX clause.

How do you drop a table that contains referential constraints?

To drop foreign key (referential) constraints, use the DROP CONSTRAINT clause of the ALTER TABLE statement. When a foreign key constraint is dropped, packages or cached dynamic statements containing the following might be marked as invalid: Statements that insert or update the table containing the foreign key.

1 Answers

Drop table will delete all related constraints, & index, no need for you to do any kind of cleanup. Postgres will handle that.

like image 138
131 Avatar answered Sep 18 '22 18:09

131
Sign in to Comment

Related questions

Execute some code when an SQLAlchemy object's deletion is actually committed
No errors logged with failed Postgres/Psycopg2 copy_from
Create Alias for PostgreSQL Table
Postgresql Select Constant
Go and IN clause in Postgres
Insert Row into Postgresql Table with Only Default Values
PostgreSQL select 20 random records [duplicate]
Deadlock detected in PL/pgSQL function
Backup and Restore option not available in pgAdmin III
postgres array_agg ERROR: cannot accumulate arrays of different dimensionality
"to_tsquery" on tsvector yields different results when using "simple" and "english"?
osm2pgsql: Function AddGeometryColumn doesn't exist
Check statistics targets in PostgreSQL
Using return value from DELETE for UPDATE in Postgres
Memory leaks on postgresql server after upgrade to Rails 4
Postgres math expression calculcated for each row in table
ERROR: operator does not exist: character varying = bytea
Postgresql: inserting value of a column from a file
Where does Homebrew install Postgres executables?
Django 1.8 migration unable to cast column id to integer

Donate For Us

If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!