CODEWITHSUNDEEP
postgresql
ericmjl
ericmjl

Reputation: 14684

How to nuke a postgres schema?

I'm coming up on the limits of my Postgres SQL knowledge, and I'm quite unsure how to diagnose this issue. Please pardon the noob-ness in my questions; I'm open to updating the question as the (expected) follow-up questions come.

I have a fairly complex database structure, in which under a schema, a number of tables are connected to one another by foreign keys. I unfortunately cannot reveal the schema itself.

One of the tables, let's call it "A", used to store close to 100K records. It's got foreign key relationships to two other tables, one called "B" with also approx. 100K records, and the other called "C" with approx. 100 records. There are 5 more tables as well.

I wanted to drop all of the tables. However, using:

truncate table schema.A cascade

takes a very long time (over 10 minutes without finishing), even though I have already removed all rows from the table (yes, I understand truncate is designed to do that exact operation). This is the first point that I don't understand: why would it take a long time to perform this operation?

Secondly, I tried:

drop table schema.A;

(using Postico, a GUI, rather than by entering SQL commands directly)

That also runs for over 10 minutes without finishing.

Are the foreign key relations the key blocker here?

If I wanted to "just quickly nuke" the schema, and start over from scratch (all of my table schemas are defined in a SQLAlchemy file, so recreating is trivial), would I have to drop the entire schema using admin privileges, or is it possible to do it as a user without admin privileges?

Upvotes: 1

Views: 1073

Answers (1)

tadman
tadman

Reputation: 211560

If you want to drop the schema:

DROP SCHEMA schema_name CASCADE

For the default schema:

DROP SCHEMA public CASCADE

To quickly reset a single schema database:

DROP SCHEMA public CASCADE;
CREATE SCHEMA public;

Upvotes: 2

Related Questions