Menu
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?
372
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.
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.
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.
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.
Drop table will delete all related constraints, & index, no need for you to do any kind of cleanup. Postgres will handle that.
138
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With
