Obcinanie wszystkich tabel w bazie danych Postgres


155

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.

Odpowiedzi:


226

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

1
Musiałem trochę odpocząć, ale potem zadziałało jak urok! Nigdy wcześniej nie używałem plpgsql, więc zajęłoby mi to wieki. Dzięki! Dla każdego, kto tego potrzebuje, dodałem kod, którego użyłem, na dole tego posta.
Sig

Przepraszam, prawdopodobnie myślałem w Oracle PL / SQL :( Naprawiłem błąd składni w moim kodzie powyżej.
Henning

1
możesz również przenieść instrukcję SELECT bezpośrednio do pętli FOR. DECLARE r RECORD;potem na pętlę: FOR r IN SELECT tablename FROM pg_tables LOOP
Michael Buen

6
Dodałbym CASCADE do TRUNCATE TABLE
Bogdan Gusiev

3
O MÓJ BOŻE!! Właśnie obciąłem wszystkie moje tabele w schemacie „publicznym” .... pls dodaj kolejny parametr „schematu”, aby funkcja obcinała tabele tylko na dostarczonym schemacie!
roneo

95

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:

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:

Informacje regclassi cytowane nazwy tabel:

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ż:


1
Warto zauważyć, że ta ostatnia funkcja wyczyściła WSZYSTKIE bazy danych. Nie tylko obecnie podłączony… tak… nazwij mnie naiwnym, ale tak naprawdę nie było to jasne z tego postu.
Amalgovinus,

@Amalgovinus: Która ostatnia funkcja? Żadna z funkcji w mojej odpowiedzi nie dotyka niczego poza bieżącą bazą danych (z wyjątkiem DROP DATABASE mydb, oczywiście). Może mylisz schematy z bazami danych?
Erwin Brandstetter,

3
@Amalgovinus: Nie, to niemożliwe. DOPoleceń (jak każdy inny SQL) jest wykonywana w bieżącej bazie danych wyłącznie . Postgres nie ma możliwości dostępu do innych baz danych w tej samej transakcji. Aby to zrobić, musiałbyś użyć dblink lub FDW. Ale ma to wpływ na wszystkie schematy w bieżącej bazie danych - chyba że dodasz, WHERE t.schemaname = 'public'aby ograniczyć efekt do jednego konkretnego schematu w tym konkretnym przypadku.
Erwin Brandstetter,

1
Naprawdę miło wiedzieć o tych szablonach. Może to być przydatne nawet w scenariuszach testów automatycznych, w których może być potrzebny reset / przygotowanie bazy danych.
hbobenicio

3
Dzięki za świetną odpowiedź. Używam polecenia „Pojedyncze polecenie, bez pętli”, które zwraca polecenie TRUNCATE. Jak mam go wykonać?
Mahyar

40

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


9

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.



3

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.


3

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;

3

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


2

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


1

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”
Korzystając z naszej strony potwierdzasz, że przeczytałeś(-aś) i rozumiesz nasze zasady używania plików cookie i zasady ochrony prywatności.
Licensed under cc by-sa 3.0 with attribution required.