0

I had a table from which I wanted to delete a lot of rows but that was failing because of memory constraints. I saw a suggestion to "simply" copy all the rows that I don't want to delete into a new table, drop the old table, then rename the new table to the old table's name. That would be fine except for other objects that depend on the original table. From what I can tell that means I'd have to drop cascade and then recreate everything that depends on the original table.

Is there a way to either script everything that depends on the original table so I can easily recreate them in pgadmin or psql?

Alternatively, is there a way to ignore the dependency long enough to delete the old table and rename the new table to the old table's name?

I believe this is how pg_repack works so there must be a way.

  • 1
    Why don't you just delete the referencing rows too? Seems like they become pointless anyway, if you delete the referenced rows. That's why a constraint was created I'd guess. – sticky bit May 5 '18 at 16:28
  • @stickybit i think I didn't explain what I meant correctly. I have a views that depend on the table. I'm not sure what you mean by referenced rows – Dean MacGregor May 5 '18 at 16:58
  • I assumed the constraints fired were foreign key constraints. That is, a value of the primary key of the table you want to delete records from is referenced as a foreign key in another table and therefore cannot be deleted. As a consequence you'd have to delete these referencing row too (or set the foreign key to NULL if no NOT NULL constraints are in place). – sticky bit May 5 '18 at 17:10
  • If that is not the case, you might need to provide more details if the environment you're facing the problem in (DDL of the tables, views, constraints, triggers, etc.) along with sample data. Constraints on views sound dubious enough to call for that anyway. – sticky bit May 5 '18 at 17:10
0

DROP TABLE example CASCADE;

will drop the table example. and drop all REFERENCES constraints that point into that table.

so after replacing the table you will also need to rebuild the references.

to get the dependencies you could use pg_dump --schema-only and then grep for REFERENCES example(

You will not be able to rebuild the references until the needed data is present in the table.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.