如何删除PostgreSQL数据库中的所有表

技术背景

在PostgreSQL数据库管理中，有时需要删除数据库中的所有表，比如在测试环境重置数据、迁移数据前清理旧表等场景。不同的删除方式适用于不同的需求和场景。

实现步骤

1. 通过删除并重新创建当前模式

一般默认有一个public模式，以下是示例代码：

-- Recreate the schema
DROP SCHEMA public CASCADE;
CREATE SCHEMA public;

-- Restore default permissions
GRANT ALL ON SCHEMA public TO postgres;
GRANT ALL ON SCHEMA public TO public;

如果使用的是PostgreSQL 9.3或更高版本，可能还需要恢复默认权限。

2. 通过从pg_tables表中获取所有表名

SELECT
  'DROP TABLE IF EXISTS "' || tablename || '" CASCADE;' 
from
  pg_tables WHERE schemaname = 'public';

通过子查询可以从模式中删除所有表。

3. 使用终端操作

• 使用postgres用户登录shell：

$ sudo -u postgres psql

• 连接到数据库：

$ \c mydatabase

• 粘贴以下命令：

DROP SCHEMA public CASCADE;
CREATE SCHEMA public;
     
GRANT ALL ON SCHEMA public TO postgres;
GRANT ALL ON SCHEMA public TO public;

4. 删除用户拥有的所有对象

drop owned by the_user;

这将删除该用户拥有的所有对象。

5. 使用PL/PGSQL函数

DROP FUNCTION IF EXISTS remove_all();

CREATE FUNCTION remove_all() RETURNS void AS $$
DECLARE
    rec RECORD;
    cmd text;
BEGIN
    cmd := '';

    FOR rec IN SELECT
            'DROP SEQUENCE ' || quote_ident(n.nspname) || '.'
                || quote_ident(c.relname) || ' CASCADE;' AS name
        FROM
            pg_catalog.pg_class AS c
        LEFT JOIN
            pg_catalog.pg_namespace AS n
        ON
            n.oid = c.relnamespace
        WHERE
            relkind = 'S' AND
            n.nspname NOT IN ('pg_catalog', 'pg_toast') AND
            pg_catalog.pg_table_is_visible(c.oid)
    LOOP
        cmd := cmd || rec.name;
    END LOOP;

    FOR rec IN SELECT
            'DROP TABLE ' || quote_ident(n.nspname) || '.'
                || quote_ident(c.relname) || ' CASCADE;' AS name
        FROM
            pg_catalog.pg_class AS c
        LEFT JOIN
            pg_catalog.pg_namespace AS n
        ON
            n.oid = c.relnamespace WHERE relkind = 'r' AND
            n.nspname NOT IN ('pg_catalog', 'pg_toast') AND
            pg_catalog.pg_table_is_visible(c.oid)
    LOOP
        cmd := cmd || rec.name;
    END LOOP;

    FOR rec IN SELECT
            'DROP FUNCTION ' || quote_ident(ns.nspname) || '.'
                || quote_ident(proname) || '(' || oidvectortypes(proargtypes)
                || ');' AS name
        FROM
            pg_proc
        INNER JOIN
            pg_namespace ns
        ON
            (pg_proc.pronamespace = ns.oid)
        WHERE
            ns.nspname =
            'public'
        ORDER BY
            proname
    LOOP
        cmd := cmd || rec.name;
    END LOOP;

    EXECUTE cmd;
    RETURN;
END;
$$ LANGUAGE plpgsql;

SELECT remove_all();

6. 使用Python脚本

import psycopg2
import sys

# Drop all tables from a given database

try:
    conn = psycopg2.connect("dbname='akcja_miasto' user='postgres' password='postgres'")
    conn.set_isolation_level(0)
except:
    print "Unable to connect to the database."

cur = conn.cursor()

try:
    cur.execute("SELECT table_schema,table_name FROM information_schema.tables WHERE table_schema = 'public' ORDER BY table_schema,table_name")
    rows = cur.fetchall()
    for row in rows:
        print "dropping table: ", row[1]   
        cur.execute("drop table " + row[1] + " cascade") 
    cur.close()
    conn.close()        
except:
    print "Error: ", sys.exc_info()[1]

核心代码

以下是一个通用的删除当前模式下所有表的代码：

DO $$ DECLARE
    r RECORD;
BEGIN
    FOR r IN (SELECT tablename FROM pg_tables WHERE schemaname = current_schema()) LOOP
        EXECUTE 'DROP TABLE IF EXISTS ' || quote_ident(r.tablename) || ' CASCADE';
    END LOOP;
END $$;

最佳实践

• 在删除表之前，一定要备份数据库，以防误操作导致数据丢失。
• 如果只需要删除表中的数据而不是表本身，可以使用TRUNCATE语句。
• 对于不同的场景，选择合适的删除方式。例如，如果需要保留其他数据库对象（如函数、视图等），可以选择从pg_tables表获取表名并逐个删除的方式。

常见问题

1. 删除模式会导致其他对象丢失

使用DROP SCHEMA ... CASCADE会删除模式下的所有对象，包括函数、视图等。如果需要保留这些对象，应选择其他删除方式。

2. 权限问题

某些操作可能需要特定的权限，如删除用户拥有的所有对象需要相应的权限。确保以具有足够权限的用户身份执行操作。

3. 依赖关系问题

使用CASCADE关键字删除表时，会自动删除依赖于该表的对象（如视图、外键等）。在执行操作前，需要明确了解这些依赖关系，避免不必要的对象丢失。