Obcinanie wszystkich tabel w bazie danych Postgres

Przed przebudową regularnie muszę usuwać wszystkie dane z mojej bazy danych PostgreSQL. Jak mam to zrobić bezpośrednio w SQL?

W tej chwili udało mi się wymyślić instrukcję SQL, która zwraca wszystkie polecenia, które muszę wykonać:

SELECT 'TRUNCATE TABLE ' ||  tablename || ';' FROM pg_tables WHERE tableowner='MYUSER';

Ale nie widzę sposobu na ich programowe wykonanie, gdy już je mam.

FrustratedWithFormsDesigner ma rację, PL / pgSQL może to zrobić. Oto scenariusz:

CREATE OR REPLACE FUNCTION truncate_tables(username IN VARCHAR) RETURNS void AS $$
DECLARE
    statements CURSOR FOR
        SELECT tablename FROM pg_tables
        WHERE tableowner = username AND schemaname = 'public';
BEGIN
    FOR stmt IN statements LOOP
        EXECUTE 'TRUNCATE TABLE ' || quote_ident(stmt.tablename) || ' CASCADE;';
    END LOOP;
END;
$$ LANGUAGE plpgsql;

Tworzy to zapisaną funkcję (musisz to zrobić tylko raz), której możesz później użyć w następujący sposób:

SELECT truncate_tables('MYUSER');

W plpgsql rzadko potrzebne są jawne kursory. Za pomocą prostsze i szybsze niejawny wskaźnik o FORpętli:

Uwaga: Ponieważ nazwy tabel nie są unikalne dla każdej bazy danych, dla pewności należy kwalifikować nazwy tabel według schematu. Ograniczam również funkcję do domyślnego schematu „public”. Dostosuj się do swoich potrzeb, ale pamiętaj o wykluczeniu schematów systemowych pg_*i information_schema.

Zachowaj ostrożność podczas korzystania z tych funkcji. Niszczą twoją bazę danych. Dodałem zabezpieczenie przed dziećmi. Skomentuj RAISE NOTICElinię i usuń komentarz, EXECUTEaby zalać bombę ...

CREATE OR REPLACE FUNCTION f_truncate_tables(_username text)
  RETURNS void AS
$func$
DECLARE
   _tbl text;
   _sch text;
BEGIN
   FOR _sch, _tbl IN 
      SELECT schemaname, tablename
      FROM   pg_tables
      WHERE  tableowner = _username
      AND    schemaname = 'public'
   LOOP
      RAISE NOTICE '%',
      -- EXECUTE  -- dangerous, test before you execute!
         format('TRUNCATE TABLE %I.%I CASCADE', _sch, _tbl);
   END LOOP;
END
$func$ LANGUAGE plpgsql;

format()wymaga Postgres 9.1 lub nowszego. W starszych wersjach połącz ciąg zapytania w następujący sposób:

'TRUNCATE TABLE ' || quote_ident(_sch) || '.' || quote_ident(_tbl)  || ' CASCADE';

Pojedyncze polecenie, bez pętli

Ponieważ możemy TRUNCATEjednocześnie wiele tabel, nie potrzebujemy żadnego kursora ani pętli:

• Przekazywanie nazw tabel w tablicy

Zgromadź wszystkie nazwy tabel i wykonaj jedną instrukcję. Prostsze, szybsze:

CREATE OR REPLACE FUNCTION f_truncate_tables(_username text)
  RETURNS void AS
$func$
BEGIN
   RAISE NOTICE '%', 
   -- EXECUTE  -- dangerous, test before you execute!
  (SELECT 'TRUNCATE TABLE '
       || string_agg(format('%I.%I', schemaname, tablename), ', ')
       || ' CASCADE'
   FROM   pg_tables
   WHERE  tableowner = _username
   AND    schemaname = 'public'
   );
END
$func$ LANGUAGE plpgsql;

Połączenie:

SELECT truncate_tables('postgres');

Udoskonalone zapytanie

Nie potrzebujesz nawet funkcji. W Postgres 9.0+ możesz wykonywać dynamiczne polecenia w DOinstrukcji. A w Postgres 9.5+ składnia może być jeszcze prostsza:

DO
$func$
BEGIN
   RAISE NOTICE '%', 
   -- EXECUTE
   (SELECT 'TRUNCATE TABLE ' || string_agg(oid::regclass::text, ', ') || ' CASCADE'
    FROM   pg_class
    WHERE  relkind = 'r'  -- only tables
    AND    relnamespace = 'public'::regnamespace
   );
END
$func$;

O różnicy między pg_class, pg_tablesoraz information_schema.tables:

• Jak sprawdzić, czy tabela istnieje w danym schemacie

Informacje regclassi cytowane nazwy tabel:

• Nazwa tabeli jako parametr funkcji PostgreSQL

Do wielokrotnego użytku

Utwórz bazę danych „szablonu” (nazwijmy ją my_template) ze swoją strukturą waniliową i wszystkimi pustymi tabelami. Następnie przejdź przez DROP/CREATE DATABASE cykl:

DROP DATABASE mydb;
CREATE DATABASE mydb TEMPLATE my_template;

Jest to niezwykle szybkie , ponieważ Postgres kopiuje całą strukturę na poziomie pliku. Żadnych problemów ze współbieżnością ani innych kosztów ogólnych, które spowalniają.

Jeśli równoczesne połączenia uniemożliwiają porzucenie bazy danych, rozważ:

• Wymuś spadek db, podczas gdy inne mogą być połączone

Jeśli muszę to zrobić, po prostu utworzę schemat sql bieżącej bazy danych, a następnie upuść i utworzę bazę danych, a następnie załaduję bazę danych za pomocą schematu sql.

Poniżej znajdują się wymagane kroki:

1) Utwórz zrzut schematu bazy danych ( --schema-only)

pg_dump mydb -s > schema.sql

2) Drop bazy danych

drop database mydb;

3) Utwórz bazę danych

create database mydb;

4) Importuj schemat

psql mydb < schema.sql

W takim przypadku prawdopodobnie lepiej byłoby mieć po prostu pustą bazę danych, której używasz jako szablonu, a gdy musisz odświeżyć, usuń istniejącą bazę danych i utwórz nową z szablonu.

Czy mógłbyś użyć dynamicznego SQL do wykonywania każdej instrukcji po kolei? Aby to zrobić, prawdopodobnie musiałbyś napisać skrypt PL / pgSQL.

http://www.postgresql.org/docs/8.3/static/plpgsql-statements.html (sekcja 38.5.4. Wykonywanie poleceń dynamicznych)

Możesz to zrobić również za pomocą basha:

#!/bin/bash
PGPASSWORD='' psql -h 127.0.0.1 -Upostgres sng --tuples-only --command "SELECT 'TRUNCATE TABLE ' || schemaname || '.' ||  tablename || ';' FROM pg_tables WHERE schemaname in ('cms_test', 'ids_test', 'logs_test', 'sps_test');" | 
tr "\\n" " " | 
xargs -I{} psql -h 127.0.0.1 -Upostgres sng --command "{}"

Będziesz musiał dostosować nazwy schematów, hasła i nazwy użytkowników, aby pasowały do ​​twoich schematów.

AUTO_INCREMENTWersja czyszcząca :

CREATE OR REPLACE FUNCTION truncate_tables(username IN VARCHAR) RETURNS void AS $$
DECLARE
    statements CURSOR FOR
        SELECT tablename FROM pg_tables
        WHERE tableowner = username AND schemaname = 'public';
BEGIN
    FOR stmt IN statements LOOP
        EXECUTE 'TRUNCATE TABLE ' || quote_ident(stmt.tablename) || ' CASCADE;';

        IF EXISTS (
            SELECT column_name 
            FROM information_schema.columns 
            WHERE table_name=quote_ident(stmt.tablename) and column_name='id'
        ) THEN
           EXECUTE 'ALTER SEQUENCE ' || quote_ident(stmt.tablename) || '_id_seq RESTART WITH 1';
        END IF;

    END LOOP;
END;
$$ LANGUAGE plpgsql;

Chłopaki, lepszym i czystszym sposobem jest:

1) Utwórz zrzut schematu bazy danych (--schema-only) pg_dump mydb -s> schema.sql

2) Drop bazy danych drop bazy danych mydb;

3) Utwórz bazę danych stwórz bazę danych mydb;

4) Importuj schemat psql mydb <schema.sql

Na mnie to działa!

Miłego dnia. Hiram Walker

Jeśli możesz użyć psql , możesz użyć \gexecpolecenia meta do wykonania zapytania;

SELECT
    format('TRUNCATE TABLE %I.%I', ns.nspname, c.relname)
  FROM pg_namespace ns 
  JOIN pg_class c ON ns.oid = c.relnamespace
  JOIN pg_roles r ON r.oid = c.relowner
  WHERE
    ns.nspname = 'table schema' AND                               -- add table schema criteria 
    r.rolname = 'table owner' AND                                 -- add table owner criteria
    ns.nspname NOT IN ('pg_catalog', 'information_schema') AND    -- exclude system schemas
    c.relkind = 'r' AND                                           -- tables only
    has_table_privilege(c.oid, 'TRUNCATE')                        -- check current user has truncate privilege
  \gexec 

Zwróć uwagę, że \gexeczostało wprowadzone w wersji 9.6

Aby usunąć dane i zachować struktury tabel w pgAdmin , możesz wykonać:

• Baza danych kliknij prawym przyciskiem myszy -> kopia zapasowa, wybierz „Tylko schemat”
• Usuń bazę danych
• Utwórz nową bazę danych i nazwij ją tak, jak poprzednia
• Kliknij prawym przyciskiem myszy nową bazę danych -> przywróć -> wybierz kopię zapasową, wybierz „Tylko schemat”