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

2010-07-25 postgresql

如何从命令行删除PostgreSQL中的所有表?

不想删除数据库本身,只是所有的表格,并在他们所有的数据。

Answers

您可以编写查询以生成如下的SQL脚本:

select 'drop table "' || tablename || '" cascade;' from pg_tables;

要么:

select 'drop table if exists "' || tablename || '" cascade;' from pg_tables;

如果由于前一句中的层叠选项而导致某些表被自动删除。

此外,如注释中所述,您可能希望按架构名称过滤要删除的表:

select 'drop table if exists "' || tablename || '" cascade;' 
  from pg_tables
 where schemaname = 'public'; -- or any other schema

然后运行它。

光荣的COPY + PASTE也将起作用。

根据上面的Pablo,就大小写而言,只是从特定模式中删除:

select 'drop table "' || tablename || '" cascade;' 
from pg_tables where schemaname = 'public';

如果您有PL / PGSQL过程语言安装 ,你可以使用下面的无壳去除一切/ Perl的外部脚本。

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();

建议您不要将其复制到文件中,而应使用“ --file”或“ -f”选项将其作为输入传递给psql:

psql -f clean_all_pg.sql

归功于应得的功劳:我编写了该函数,但认为查询(或至少第一个查询)来自多年前pgsql邮件列表之一中的某个人。不记得确切的时间或哪个。

继Pablo和LenW之后,这是一个单行代码,它可以完成所有准备工作,然后执行:

psql -U $PGUSER $PGDB -t -c "select 'drop table \"' || tablename || '\" cascade;' from pg_tables where schemaname = 'public'" | psql -U $PGUSER $PGDB

注意:用您想要的值设置或替换$PGUSER$PGDB

以防万一...清理Postgresql数据库的简单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]

确保复制后缩进正确,因为Python依赖缩进。

drop schema public cascade;

应该可以。

如果所有表都在单个模式中,则此方法可行(以下代码假定模式名称为public

DROP SCHEMA public CASCADE;
CREATE SCHEMA public;

如果使用的是PostgreSQL 9.3或更高版本,则可能还需要恢复默认授予。

GRANT ALL ON SCHEMA public TO postgres;
GRANT ALL ON SCHEMA public TO public;

您可以使用string_agg函数创建一个逗号分隔的列表,非常适合DROP TABLE。 从bash脚本中:

#!/bin/bash
TABLES=`psql $PGDB -t --command "SELECT string_agg(table_name, ',') FROM information_schema.tables WHERE table_schema='public'"`

echo Dropping tables:${TABLES}
psql $PGDB --command "DROP TABLE IF EXISTS ${TABLES} CASCADE"

在Windows批处理文件中:

@echo off
FOR /f "tokens=2 delims=|" %%G IN ('psql --host localhost --username postgres --command="\dt" YOUR_TABLE_NAME') DO (
   psql --host localhost --username postgres --command="DROP table if exists %%G cascade" sfkb
   echo table %%G dropped
)

截至撰写本文时(2014年1月),最被接受的答案是:

drop schema public cascade;
create schema public;

这确实可行,但是,如果您打算将公共架构还原到其原始状态,则不能完全完成任务。在用于PostgreSQL 9.3.1的pgAdmin III中,如果单击以这种方式创建的“公共”模式并在“ SQL窗格”中查看,则会看到以下内容:

-- Schema: public

-- DROP SCHEMA public;

CREATE SCHEMA public
  AUTHORIZATION postgres;

但是,相比之下,一个全新的数据库将具有以下内容:

-- Schema: public

-- DROP SCHEMA public;

CREATE SCHEMA public
  AUTHORIZATION postgres;

GRANT ALL ON SCHEMA public TO postgres;
GRANT ALL ON SCHEMA public TO public;
COMMENT ON SCHEMA public
  IS 'standard public schema';

对我来说,使用创建网站表(web2py)的python网络框架,使用前者会导致问题:

<class 'psycopg2.ProgrammingError'> no schema has been selected to create in 

因此,我认为完全正确的答案是:

DROP SCHEMA public CASCADE;
CREATE SCHEMA public;
GRANT ALL ON SCHEMA public TO postgres;
GRANT ALL ON SCHEMA public TO public;
COMMENT ON SCHEMA public IS 'standard public schema';

另请注意,要在pgAdmin III中发出这些命令,我​​使用了查询工具(放大镜图标“执行简单的SQL查询”),也可以使用Plugins-> PSQL Console

Rails的Rake任务,用于销毁当前数据库中的所有表

namespace :db do
  # rake db:drop_all_tables
  task drop_all_tables: :environment do
    query = <<-QUERY
      SELECT
        table_name
      FROM
        information_schema.tables
      WHERE
        table_type = 'BASE TABLE'
      AND
        table_schema NOT IN ('pg_catalog', 'information_schema');
    QUERY

    connection = ActiveRecord::Base.connection
    results    = connection.execute query

    tables = results.map do |line|
      table_name = line['table_name']
    end.join ", "

    connection.execute "DROP TABLE IF EXISTS #{ tables } CASCADE;"
  end
end

我通过照顾视图来增强了杰米的bash方法,因为他只考虑默认的表类型“基本表”。

以下bash代码首先删除视图,然后删除所有其余视图

#!/usr/bin/env bash

PGDB="yourDB"
# By exporting user & pass your dont need to interactively type them on execution
export PGUSER="PGusername"
export PGPASSWORD="PGpassword"

VIEWS=`psql -d $PGDB -t --command "SELECT string_agg(table_name, ',') FROM information_schema.tables WHERE table_schema='public' AND table_type='VIEW'"`
BASETBLS=`psql -d $PGDB -t --command "SELECT string_agg(table_name, ',') FROM information_schema.tables WHERE table_schema='public' AND table_type='BASE TABLE'"`

echo Dropping views:${VIEWS}
psql $PGDB --command "DROP VIEW IF EXISTS ${VIEWS} CASCADE"
echo Dropping tables:${BASETBLS}
psql $PGDB --command "DROP TABLE IF EXISTS ${BASETBLS} CASCADE"

您需要删除表格和序列,这对我有用

psql -qAtX -c "select 'DROP TABLE IF EXISTS ' || quote_ident(table_schema) || '.' || quote_ident(table_name) || ' CASCADE;' FROM information_schema.tables where table_type = 'BASE TABLE' and not table_schema ~ '^(information_schema|pg_.*)$'" | psql -qAtX
psql -qAtX -c "select 'DROP SEQUENCE IF EXISTS ' || quote_ident(relname) || ' CASCADE;' from pg_statio_user_sequences;" | psql -qAtX

在运行命令之前,您可能需要sudo / su到postgres用户或(导出连接详细信息PGHOSTPGPORTPGUSERPGPASSWORD ),然后export PGDATABASE=yourdatabase

如果您要删除的所有内容均由同一用户拥有 ,则可以使用:

drop owned by the_user;

这将用户拥有放下一切

其中包括the_user拥有(=创建)的物化视图,视图,序列,触发器,模式,函数,类型,集合,运算符,域等(因此,实际上是: 一切 )。

您必须用实际的用户名替换the_user ,当前无法删除“当前用户”的所有内容。即将推出的9.5版本将具有drop owned by current_user

手册中的更多详细信息: http : //www.postgresql.org/docs/current/static/sql-drop-owned.html

您可以使用

DO $$ DECLARE
    r RECORD;
BEGIN
    -- if the schema you operate on is not "current", you will want to
    -- replace current_schema() in query with 'schematodeletetablesfrom'
    -- *and* update the generate 'DROP...' accordingly.
    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 $$;

IMO优于drop schema public ,因为您无需重新创建schema并还原所有授予。

额外的好处是,这不需要外部脚本语言,也不需要将生成的SQL复制粘贴回解释器。

为了方便将生成的SQL命令作为一个字符串返回,我对Pablo的答案做了一些修改:

select string_agg('drop table "' || tablename || '" cascade', '; ') 
from pg_tables where schemaname = 'public'

如果要删除数据(而不是删除表):

-- Truncate tables and restart sequnces
SELECT 'TRUNCATE TABLE "' || table_schema || '"."' || table_name || '" RESTART IDENTITY CASCADE;' 
FROM information_schema.tables 
WHERE table_catalog = '<database>' AND table_schema = '<schema>';

或者,如果要删除表,则可以使用以下sql:

-- For tables
SELECT 'DROP TABLE "' || table_schema || '"."' || table_name || '" CASCADE;' 
FROM information_schema.tables 
WHERE table_catalog = '<database>' AND table_schema = '<schema>';

-- For sequences
SELECT 'DROP SEQUENCE d_a_seq "' || sequence_schema || '"."' || sequence_name || '";' 
FROM information_schema.sequences 
WHERE sequence_catalog = '<database>' AND sequence_schema = '<schema>';

在pgAdmin中使用以下脚本:

DO $$
DECLARE 
    brow record;
BEGIN
    FOR brow IN (select 'drop table "' || tablename || '" cascade;' as table_name from pg_tables where schemaname = 'public') LOOP
        EXECUTE brow.table_name;
    END LOOP;
END; $$

好吧,因为我喜欢从命令行工作...

psql -U <user> -d <mydb> -c '\dt' | cut -d ' ' -f 4 | sed -e "s/^/drop table if exists /" | sed -e "s/$/;/"

-c '\dt'将调用列表表命令。

List of relations Schema | Name | Type | Owner --------+-------------------+-------+---------- public | _d_psidxddlparm | table | djuser public | _d_psindexdefn | table | djuser List of relations Schema | Name | Type | Owner --------+-------------------+-------+---------- public | _d_psidxddlparm | table | djuser public | _d_psindexdefn | table | djuser List of relations Schema | Name | Type | Owner --------+-------------------+-------+---------- public | _d_psidxddlparm | table | djuser public | _d_psindexdefn | table | djuser List of relations Schema | Name | Type | Owner --------+-------------------+-------+---------- public | _d_psidxddlparm | table | djuser public | _d_psindexdefn | table | djuser List of relations Schema | Name | Type | Owner --------+-------------------+-------+---------- public | _d_psidxddlparm | table | djuser public | _d_psindexdefn | table | djuser

cut -d ' ' -f 4 ,将其输出通过管道传递到表的第4个字段(使用空格作为分隔符时)。

然后使用seddrop table添加前缀并在其后缀;命令分隔符。

| egrep '_d_'将其更多地| egrep '_d_'grep ,您可以对删除哪些表有更多选择。

drop table if exists _d_psidxddlparm; drop table if exists _d_psindexdefn; drop table if exists _d_psidxddlparm; drop table if exists _d_psindexdefn;

注意:按照本文所述,这将为\dt命令的列标题输出和最后的总行生成伪造的行。我可以通过grepping来避免这种情况,但是您可以使用headtail

最简单的方法是像其他人在以前的答案中所建议的那样删除公共架构。但是,这不是一个好方法。您永远都不知道对已被遗忘且未记录在案的公共模式做了什么。您也不知道将来是否会如此。在V9中,一切都很好,但是在V10中,所有用户都将失去对该架构的访问权限,并且必须再次被授予访问权限,否则您的应用程序将被破坏。我没有检查过V11,但要点是,当您从一台机器到另一台机器,从一个站点到另一个站点或从一个版本到另一个版本移动时,您永远都不知道会破坏什么。如果您是有权访问数据库但不能访问架构的用户,则也无法完成此操作。

如果您需要以编程方式执行此操作,则上面的其他答案可以解决此问题,但是上面答案中没有考虑的一件事就是让Postgres为您完成工作。如果您将pg_dump与-c选项一起使用,如下所示:

sudo su postgres -c "pg_dump -U postgres WhateverDB -c -f "/home/Anyone/DBBackupWhateverDB-ServerUnscheduled.sql""

这将使用sql语句创建一个数据库还原脚本,该脚本将删除所有表。

如果提出问题的唯一目的是在还原之前删除表,则还原将为您完成工作。

但是,如果您需要其他功能,则可以简单地从sql脚本复制drop语句。

注意:我的答案是关于真正删除表和其他数据库对象的。为了删除表的所有数据(即截断所有表) ,Endre Both在一个月后提供了类似执行良好(直接执行)的语句。

对于不能仅仅DROP SCHEMA public CASCADE;DROP OWNED BY current_user;或类似的东西,这是我编写的独立SQL脚本,它是事务安全的(即,您可以将其放在BEGIN; ROLLBACK;进行测试或COMMIT;实际执行操作)并清理“所有”数据库对象……好吧,我们的应用程序使用的数据库中使用的所有对象,或者我可以明智地添加,即:

  • 桌上的触发器
  • 表上的约束(FK,PK, CHECKUNIQUE
  • 指标
  • VIEW (正常或实体化)
  • 桌子
  • 顺序
  • 函数/过程(也许应该尊重 pg_proc.proisagg
  • 所有非默认(即非public或数据库内部)架构“我们”拥有:以“非数据库超级用户”身份运行时,该脚本很有用;超级用户可以删除所有纲要(尽管仍然非常明确地排除了真正重要的纲要)
  • 扩展(用户提供,但我通常故意保留它们)

不放弃是(有些是故意的;有些只是因为我在数据库中没有示例):

  • public模式(例如,其中包含扩展提供的内容)
  • 集合函数
  • 整理和其他语言环境的东西
  • 事件触发
  • 文字搜索内容,…(请参阅此处 ,我可能会错过的其他内容)
  • 角色或其他安全设置
  • 复合类型
  • 烤面包桌
  • FDW和外国表

对于要还原的转储与要还原到的数据库具有不同的数据库模式版本(例如,使用Debian dbconfig dbconfig-common ,Flyway或Liquibase / DB-Manul)而言,这确实有用。

如果有人感兴趣,我还提供了一个删除“除两个表之外的所有内容以及属于它们的内容”的版本。差异很小。如有需要,请与我联系。

的SQL

-- Copyright © 2019
--      mirabilos <[email protected]>
--
-- Provided that these terms and disclaimer and all copyright notices
-- are retained or reproduced in an accompanying document, permission
-- is granted to deal in this work without restriction, including un‐
-- limited rights to use, publicly perform, distribute, sell, modify,
-- merge, give away, or sublicence.
--
-- This work is provided “AS IS” and WITHOUT WARRANTY of any kind, to
-- the utmost extent permitted by applicable law, neither express nor
-- implied; without malicious intent or gross negligence. In no event
-- may a licensor, author or contributor be held liable for indirect,
-- direct, other damage, loss, or other issues arising in any way out
-- of dealing in the work, even if advised of the possibility of such
-- damage or existence of a defect, except proven that it results out
-- of said person’s immediate fault when using the work as intended.
-- -
-- Drop everything from the PostgreSQL database.

DO $$
DECLARE
        r RECORD;
BEGIN
        -- triggers
        FOR r IN (SELECT pns.nspname, pc.relname, pt.tgname
                FROM pg_trigger pt, pg_class pc, pg_namespace pns
                WHERE pns.oid=pc.relnamespace AND pc.oid=pt.tgrelid
                    AND pns.nspname NOT IN ('information_schema', 'pg_catalog', 'pg_toast')
                    AND pt.tgisinternal=false
            ) LOOP
                EXECUTE format('DROP TRIGGER %I ON %I.%I;',
                    r.tgname, r.nspname, r.relname);
        END LOOP;
        -- constraints #1: foreign key
        FOR r IN (SELECT pns.nspname, pc.relname, pcon.conname
                FROM pg_constraint pcon, pg_class pc, pg_namespace pns
                WHERE pns.oid=pc.relnamespace AND pc.oid=pcon.conrelid
                    AND pns.nspname NOT IN ('information_schema', 'pg_catalog', 'pg_toast')
                    AND pcon.contype='f'
            ) LOOP
                EXECUTE format('ALTER TABLE ONLY %I.%I DROP CONSTRAINT %I;',
                    r.nspname, r.relname, r.conname);
        END LOOP;
        -- constraints #2: the rest
        FOR r IN (SELECT pns.nspname, pc.relname, pcon.conname
                FROM pg_constraint pcon, pg_class pc, pg_namespace pns
                WHERE pns.oid=pc.relnamespace AND pc.oid=pcon.conrelid
                    AND pns.nspname NOT IN ('information_schema', 'pg_catalog', 'pg_toast')
                    AND pcon.contype<>'f'
            ) LOOP
                EXECUTE format('ALTER TABLE ONLY %I.%I DROP CONSTRAINT %I;',
                    r.nspname, r.relname, r.conname);
        END LOOP;
        -- indicēs
        FOR r IN (SELECT pns.nspname, pc.relname
                FROM pg_class pc, pg_namespace pns
                WHERE pns.oid=pc.relnamespace
                    AND pns.nspname NOT IN ('information_schema', 'pg_catalog', 'pg_toast')
                    AND pc.relkind='i'
            ) LOOP
                EXECUTE format('DROP INDEX %I.%I;',
                    r.nspname, r.relname);
        END LOOP;
        -- normal and materialised views
        FOR r IN (SELECT pns.nspname, pc.relname
                FROM pg_class pc, pg_namespace pns
                WHERE pns.oid=pc.relnamespace
                    AND pns.nspname NOT IN ('information_schema', 'pg_catalog', 'pg_toast')
                    AND pc.relkind IN ('v', 'm')
            ) LOOP
                EXECUTE format('DROP VIEW %I.%I;',
                    r.nspname, r.relname);
        END LOOP;
        -- tables
        FOR r IN (SELECT pns.nspname, pc.relname
                FROM pg_class pc, pg_namespace pns
                WHERE pns.oid=pc.relnamespace
                    AND pns.nspname NOT IN ('information_schema', 'pg_catalog', 'pg_toast')
                    AND pc.relkind='r'
            ) LOOP
                EXECUTE format('DROP TABLE %I.%I;',
                    r.nspname, r.relname);
        END LOOP;
        -- sequences
        FOR r IN (SELECT pns.nspname, pc.relname
                FROM pg_class pc, pg_namespace pns
                WHERE pns.oid=pc.relnamespace
                    AND pns.nspname NOT IN ('information_schema', 'pg_catalog', 'pg_toast')
                    AND pc.relkind='S'
            ) LOOP
                EXECUTE format('DROP SEQUENCE %I.%I;',
                    r.nspname, r.relname);
        END LOOP;
        -- extensions (see below), only if necessary
        FOR r IN (SELECT pns.nspname, pe.extname
                FROM pg_extension pe, pg_namespace pns
                WHERE pns.oid=pe.extnamespace
                    AND pns.nspname NOT IN ('information_schema', 'pg_catalog', 'pg_toast')
            ) LOOP
                EXECUTE format('DROP EXTENSION %I;', r.extname);
        END LOOP;
        -- functions / procedures
        FOR r IN (SELECT pns.nspname, pp.proname, pp.oid
                FROM pg_proc pp, pg_namespace pns
                WHERE pns.oid=pp.pronamespace
                    AND pns.nspname NOT IN ('information_schema', 'pg_catalog', 'pg_toast')
            ) LOOP
                EXECUTE format('DROP FUNCTION %I.%I(%s);',
                    r.nspname, r.proname,
                    pg_get_function_identity_arguments(r.oid));
        END LOOP;
        -- nōn-default schemata we own; assume to be run by a not-superuser
        FOR r IN (SELECT pns.nspname
                FROM pg_namespace pns, pg_roles pr
                WHERE pr.oid=pns.nspowner
                    AND pns.nspname NOT IN ('information_schema', 'pg_catalog', 'pg_toast', 'public')
                    AND pr.rolname=current_user
            ) LOOP
                EXECUTE format('DROP SCHEMA %I;', r.nspname);
        END LOOP;
        -- voilà
        RAISE NOTICE 'Database cleared!';
END; $$;

在PostgreSQL 9.6( jessie-backports )上进行了测试,但后来的增加(由ClémentPrévost提供了extensions )除外。错误修正和进一步的改进,欢迎您!

如果您仍然想对所有表进行核对,则可以通过将所有表放入单个语句中来省去CASCADE之类的细节。这也使执行更快。

SELECT 'TRUNCATE TABLE ' || string_agg('"' || tablename || '"', ', ') || ';' 
FROM pg_tables WHERE schemaname = 'public';

直接执行:

DO $$
DECLARE tablenames text;
BEGIN    
    tablenames := string_agg('"' || tablename || '"', ', ') 
        FROM pg_tables WHERE schemaname = 'public';
    EXECUTE 'TRUNCATE TABLE ' || tablenames;
END; $$

如果适用,将TRUNCATE替换为DROP

Related