nōn-default schemata we own assume to be run by a not-superuserįROM pg_catalog.pg_namespace pns, pg_catalog.pg_roles prĪND pns.nspname NOT IN ('information_schema', 'pg_catalog', 'pg_toast', 'public')ĮXECUTE format('DROP SCHEMA %I ', r.nspname) WHERE attrelid='pg_catalog.pg_proc'::regclassĮLSIF EXISTS (SELECT * FROM pg_catalog.pg_attributeįOR r IN EXECUTE 'SELECT pns.nspname, pp.proname, pp.oid, ' || q || ' AS ptįROM pg_catalog.pg_proc pp, pg_catalog.pg_namespace pnsĪND pns.nspname NOT IN (''information_schema'', ''pg_catalog'', ''pg_toast'')ĮXECUTE format('DROP %s %I.%I(%s) ', r.pt, IF EXISTS (SELECT * FROM pg_catalog.pg_attribute routines (functions, aggregate functions, procedures, window functions) Pg_get_function_identity_arguments(r.oid)) aggregate functions first (because they depend on other functions)įOR r IN (SELECT pns.nspname, pp.proname, pp.oidįROM pg_catalog.pg_proc pp, pg_catalog.pg_namespace pns, pg_catalog.pg_aggregate paggĮXECUTE format('DROP AGGREGATE %I.%I(%s) ', extensions (only if necessary keep them normally)įROM pg_catalog.pg_extension pe, pg_catalog.pg_namespace pnsĮXECUTE format('DROP EXTENSION %I ', r.extname) WHERE pns.oid=pc.relnamespace AND pc.oid=nrelidĮXECUTE format('ALTER TABLE ONLY %I.%I DROP CONSTRAINT %I ',įROM pg_catalog.pg_class pc, pg_catalog.pg_namespace pns WHERE pns.oid=pc.relnamespace AND pc.oid=pt.tgrelidĪND pns.nspname NOT IN ('information_schema', 'pg_catalog', 'pg_toast')ĮXECUTE format('DROP TRIGGER %I ON %I.%I ',įOR r IN (SELECT pns.nspname, pc.relname, nnameįROM pg_catalog.pg_constraint pcon, pg_catalog.pg_class pc, pg_catalog.pg_namespace pns Drop everything from the PostgreSQL database.įOR r IN (SELECT pns.nspname, pc.relname, pt.tgnameįROM pg_catalog.pg_trigger pt, pg_catalog.pg_class pc, pg_catalog.pg_namespace pns of said person’s immediate fault when using the work as intended. damage or existence of a defect, except proven that it results out of dealing in the work, even if advised of the possibility of such direct, other damage, loss, or other issues arising in any way out may a licensor, author or contributor be held liable for indirect, implied without malicious intent or gross negligence. the utmost extent permitted by applicable law, neither express nor This work is provided “AS IS” and WITHOUT WARRANTY of any kind, to limited rights to use, publicly perform, distribute, sell, modify, is granted to deal in this work without restriction, including un‐ are retained or reproduced in an accompanying document, permission Provided that these terms and disclaimer and all copyright notices Contact me or check this repo if interested. I’ve also got a version which deletes “everything except two tables and what belongs to them” (a sequence, tested manually, sorry, I know, boring) in case someone is interested the diff is small. with Debian dbconfig-common, Flyway or Liquibase/DB-Manul) than the database you want to restore it into. This is really useful for the cases when the dump you want to restore is of a different database schema version (e.g. text search stuff, … (see here for other stuff I might have missed).Not dropped are (some deliberate some only because I had no example in our DB): extensions (user-contributed but I normally deliberately leave them in). not public or DB-internal) schemata “we” own: the script is useful when run as “not a database superuser” a superuser can drop all schemata (the really important ones are still explicitly excluded, though) routines (aggregate functions, functions, procedures).constraints on tables (FK, PK, CHECK, UNIQUE).you can put it between BEGIN and either ROLLBACK to just test it out or COMMIT to actually do the deed) and cleans up “all” database objects… well, all those used in the database our application uses or I could sensibly add, which is: truncating all tables, Endre Both has provided a similarily well-executed (direct execution) statement a month later.įor the cases where you can’t just DROP SCHEMA public CASCADE, DROP OWNED BY current_user or something, here’s a stand-alone SQL script I wrote, which is transaction-safe (i.e. Note: my answer is about really deleting the tables and other database objects for deleting all data in the tables, i.e.
0 Comments
Leave a Reply. |
AuthorWrite something about yourself. No need to be fancy, just an overview. ArchivesCategories |