如何删除Postgres中特定架构中的每个表？ [英] How to delete every table in a specific schema in postgres?

问题描述

如何删除特定架构中的所有表？仅架构中的表应删除。
我已经拥有使用以下代码获取的所有表名，但是如何删除所有这些表？

How do I delete all the tables I have in a specific schema? Only the tables in the schema should be deleted. I already have all the table names that I fetched with the code below, but how do delete all those tables?

以下是一些psycopg2代码，并且下面是生成的SQL

The following is some psycopg2 code, and below that is the SQL generated

writeCon.execute("SELECT table_name FROM information_schema.tables WHERE table_schema='mySchema'")

SELECT table_name FROM information_schema.tables WHERE table_schema ='mySchema'

推荐答案

您可以使用为此，匿名代码块 。

You can use an anonymous code block for that.

注意：我们正在处理 DROP TABLE 语句，如果您输入有误，它们的意思是;; CASCADE 选项也会删除依赖对象。

Attention: We're dealing with DROP TABLE statements, and they are really mean if you make a mistake ;) The CASCADE option drops the depending objects as well. Use it with care!

DO $$
DECLARE
  row record;
BEGIN
    FOR row IN SELECT * FROM pg_tables WHERE schemaname = 'mySchema' 
    LOOP
      EXECUTE 'DROP TABLE mySchema.' || quote_ident(row.tablename) || ' CASCADE';
    END LOOP;
END;
$$;

如果要删除架构中的所有内容（包括包装，序列等），请考虑删除架构本身并再次创建它：

In case you want to drop everything in your schema, including wrappers, sequences, etc., consider dropping the schema itself and creating it again:

DROP SCHEMA mySchema CASCADE;
CREATE SCHEMA mySchema;

这篇关于如何删除Postgres中特定架构中的每个表？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！