Drop all tables in PostgreSQL?

How can I delete all tables in PostgreSQL, working from the command line?

I don't want to drop the database itself, just all tables and all the data in them.

---

If all of your tables are in a single schema, this approach could work (below code assumes that the name of your schema is public )

DROP SCHEMA public CASCADE;
CREATE SCHEMA public;

If you are using PostgreSQL 9.3 or greater, you may also need to restore the default grants.

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

---

You can write a query to generate a SQL script like this:

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

Or:

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

In case some tables are automatically dropped due to cascade option in a previous sentence.

Additionally, as stated in the comments, you might want to filter the tables you want to drop by schema name:

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

And then run it.

Glorious COPY+PASTE will also work.

---

The most accepted answer as of this writing (January 2014) is:

drop schema public cascade;
create schema public;

This does work, however if your intention is to restore the public schema to its virgin state this does not fully accomplish the task. Under pgAdmin III for PostgreSQL 9.3.1, if you click on the "public" schema created this way and look in the "SQL pane" you will see the following:

-- Schema: public

-- DROP SCHEMA public;

CREATE SCHEMA public
  AUTHORIZATION postgres;

However, by contrast a brand new database will have the following:

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

For me using a python web framework which creates database tables (web2py), using the former caused problems:

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

So to my mind the fully correct answer is:

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

(also note to issue these commands from pgAdmin III, I went to Plugins-> PSQL Console)

链接地址: http://www.djcxy.com/p/25502.html

上一篇: Postgresql不允许远程连接

下一篇: 删除PostgreSQL中的所有表？