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

我不想删除数据库本身,只想删除其中的所有表和所有数据。


当前回答

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

-- 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>';

其他回答

您可以编写一个查询来生成SQL脚本,如下所示:

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

Or:

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也将发挥作用。

使用psql的基于终端的方法最适合我。我甚至创建了一个bash函数,因为它便于开发:

psqlDropTables() {
    PGPASSWORD=<your password>
    PGTABLE=<your table name>
    PGUSER=<your pg user name>
    PGPASSWORD=$PGPASSWORD psql -ah 127.0.0.1 $PGTABLE $PGUSER -c "
      SELECT
'DROP TABLE IF EXISTS \"' || tablename || '\" CASCADE;' from
pg_tables WHERE schemaname = 'public';" | grep DROP | awk 'NR>1{print $0}' | sed "s/\"/'/g" | PGPASSWORD=$PGPASSWORD xargs -i  psql -ah 127.0.0.1 $PGTABLE $PGUSER -c {}
}

它创建了此响应中所述的所有必需的放置表语句,将“替换为”并在DB上运行它们。

如果您的所有表都在一个模式中,那么这种方法可以工作(下面的代码假设您的模式的名称是公共的)

DROP SCHEMA public CASCADE;
CREATE SCHEMA public;

如果您使用PostgreSQL 9.3或更高版本,您可能还需要恢复默认授权。

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

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

drop schema public cascade;
create schema public;

然而,如果您的意图是将公共模式恢复到其原始状态,这确实有效,但这并不能完全完成任务。在pgAdmin III for PostgreSQL 9.3.1下,如果您单击以这种方式创建的“公共”模式并在“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 web框架,使用前者会导致问题:

<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查询”),或者您可以使用插件->PSQL控制台

Note

如果您安装了任何扩展,那么当您删除架构时,这些扩展将被删除,因此您应该记下需要安装的内容,然后根据需要执行语句。例如。

创建扩展postgis;

这是一个非常有趣的问题,您可以通过多种方式完成:

1.通过删除并重新创建当前架构

这里,通常情况下,我们有一个默认的公共模式。所以,我用它作为一个例子。

-- 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表中获取所有表名。

PostgreSQL将所有表存储在其名为pg_table的记录表中。

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

如您所见,通过使用子查询,我们可以从模式中删除整个表。

赞成的意见:

当其他数据实体很重要,并且您只想从模式中删除表时,这种方法对您非常有用。

3.终端

使用shell上的postgres用户登录

$ 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;

注意:这一组命令与第一点类似,因此优点和缺点将保持不变。。