Posted on: February 10, 2019 02:18 PM
Posted by: Renato
Views: 1275
Summary: in this tutorial, you will learn how to use the PostgreSQL DROP SCHEMA statement to delete a schema and its objects.
Deletar todas as tabelas de um banco de dados postgreSQL de uma só vez para restaurar backup
PostgreSQL DROP SCHEMA statement overview
The DROP SCHEMA removes a schema and all of its objects from a database. The following illustrates the syntax of the DROP SCHEMA statement:
| 1 | DROP SCHEMA [IF EXISTS] schema_name [ CASCADE | RESTRICT ]; |
PostgreSQL DROP SCHEMA statement examples
Note that the following examples use schemas created in the CREATE SCHEMA tutorial with some modifications in the ALTER SCHEMA tutorial.
| 1 | DROP SCHEMA IF EXISTS finance, marketing; |
Deletar todas as tabelas de um banco de dados postgreSQL de uma só vez para restaurar backup
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.
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)
# Criando um script sql (clean_all_pg.sql)
If you have the PL/PGSQL procedural language installed you can use the following to remove everything without a shell/Perl external script.
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();
Rather than type this in at the "psql" prompt I would suggest you copy it to a file and then pass the file as input to psql using the "--file" or "-f" options:
psql -f clean_all_pg.sql
Credit where credit is due: I wrote the function, but think the queries (or the first one at least) came from someone on one of the pgsql mailing lists years ago. Don't remember exactly when or which one.
Donate to Site
Renato
Developer