Artigos - Artigos e dicas feito com PHP + Laravel

Posted on: February 10, 2019 12:18 PM

Posted by: Renato

Categories: Variados postgresql database linux ubuntu sql

Views: 1120

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:

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.

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.

Renato

Developer

No comments yet! Be the first to comment