-1

I was wondering if there is a quick and efficient way to order tables in PostgreSQL so that, if I ran a DELETE FROM <table_name> they would all succeed without breaking referential integrity.

E.g. if the tables were USER -->* ACCOUNT -->* TRANSACTION, I want to delete first TRANSACTION, then ACCOUNT, then USER because any other order would break the foreign key relationships.

I have done this before with other DBs by querying system tables and building a referential integrity tree in memory of some scripting language. But I was wondering if there is a quick(er) way to produce such a list.

amphibient
  • 29,770
  • 54
  • 146
  • 240
  • The title says `truncate`, but the post itself says `delete` - which are you going to use? – Mureinik Oct 23 '13 at 15:27
  • does it matter ? the end result is the same, wiping all the data and having an empty schema – amphibient Oct 23 '13 at 15:50
  • I was kind of hoping you have FKs in place, so you could just do `TRUNCATE TABLE users CASCADE`, and leave postgres to worry about the order. – Mureinik Oct 23 '13 at 16:04
  • @amphibient: `DELETE` or `TRUNCATE` matters when talking about referential integrity. [Read the fine manual here](http://www.postgresql.org/docs/current/interactive/sql-truncate.html). Either way, you can find two different approaches to get the recursive tree of referenced tables [in this related answer](http://stackoverflow.com/questions/13099226/in-postgres-how-could-i-replace-all-tables-of-a-database-except-one-with-data/13099617#13099617). – Erwin Brandstetter Oct 23 '13 at 16:52

1 Answers1

0

How about defining foreign keys that cascade deletes, so you do not need to do this?

David Aldridge
  • 51,479
  • 8
  • 68
  • 96
  • this does not answer my question so it is more appropriate to be a comment – amphibient Oct 23 '13 at 15:52
  • It answers the problem you have. You're asking for a manual methodology for a task that RDBMS suppliers have already automated. – David Aldridge Oct 23 '13 at 16:38
  • @amphibient: Some more info and maybe a link to the manual would be nice, but it is the proper solution. – Erwin Brandstetter Oct 23 '13 at 16:39
  • i am not the DBA. i cannot change the schema. i can delete data but cannot change schema. i wish ppl here didn't make assumptions and adhered to what the question stipulates. – amphibient Oct 23 '13 at 17:23
  • I wish that people would remember that Stackoverflow is not just here to answer their particular question, particularly when they have constraints on the way they can solve a problem that they do not list in the question. Remember that questions and answers are of most value when others can learn from them. – David Aldridge Oct 23 '13 at 17:28