Усечение всех таблиц в базе данных Postgres

FrustratedWithFormsDesigner верен, PL / pgSQL может это сделать. Вот сценарий:

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;

Это создает сохраненную функцию (вам нужно сделать это только один раз), которую вы впоследствии можете использовать следующим образом:

SELECT truncate_tables('MYUSER');

Явные курсоры редко нужны в plpgsql. Используйте более простой и быстрый неявный курсор FOR цикл:

Примечание. Поскольку имена таблиц не уникальны для каждой базы данных, для уверенности необходимо указать имена таблиц с указанием схемы. Кроме того, я ограничиваю функцию схемой по умолчанию public. Адаптируйте к своим потребностям, но не забудьте исключить системные схемы pg_* и information_schema.

Будьте очень осторожны с этими функциями. Они уничтожают вашу базу данных. Я добавил устройство безопасности детей. Прокомментируйте строку RAISE NOTICE и раскомментируйте EXECUTE, чтобы заправить бомбу ...

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  
      -- dangerous, test before you execute!
      RAISE NOTICE '%',  -- once confident, comment this line ...
      -- EXECUTE         -- ... and uncomment this one
         format('TRUNCATE TABLE %I.%I CASCADE', _sch, _tbl);
   END LOOP;
END
$func$ LANGUAGE plpgsql;

Для format() требуется Postgres 9.1 или новее. В более старых версиях объедините строку запроса следующим образом:

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

Одиночная команда, без цикла

Поскольку мы можем TRUNCATE одновременно несколько таблиц, нам вообще не нужен курсор или цикл:

• Передача имен таблиц в массиве

Сгруппируйте все имена таблиц и выполните одну инструкцию. Проще, быстрее:

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

Вызов:

SELECT truncate_tables('postgres');

Уточненный запрос

Вам даже не нужна функция. В Postgres 9.0+ вы можете выполнять динамические команды в DO заявление. А в Postgres 9.5+ синтаксис может быть еще проще:

DO
$func$
BEGIN
   -- dangerous, test before you execute!
   RAISE NOTICE '%',  -- once confident, comment this line ...
   -- EXECUTE         -- ... and uncomment this one
   (SELECT 'TRUNCATE TABLE ' || string_agg(oid::regclass::text, ', ') || ' CASCADE'
    FROM   pg_class
    WHERE  relkind = 'r'  -- only tables
    AND    relnamespace = 'public'::regnamespace
   );
END
$func$;

О разнице между pg_class, pg_tables и information_schema.tables:

• Как проверить, есть ли таблица существует в данной схеме

О regclass и именах таблиц в кавычках:

• Имя таблицы как параметр функции PostgreSQL

Для многократного использования

Создайте базу данных шаблонов (назовем ее my_template) с вашей ванильной структурой и всеми пустыми таблицами. Затем пройдите цикл DROP / CREATE DATABASE. :

DROP DATABASE mydb;
CREATE DATABASE mydb TEMPLATE my_template;

Это чрезвычайно быстро, потому что Postgres копирует всю структуру на уровне файлов. Никаких проблем с параллелизмом или других накладных расходов, замедляющих вас.

Если одновременные подключения не позволяют вам отбросить БД, примите во внимание:

• Принудительно сбросить базу данных, пока другие могут быть подключены

Если мне нужно это сделать, я просто создам схему sql текущей базы данных, затем отброшу и создаю базу данных, а затем загрузю базу данных со схемой sql.

Ниже приведены необходимые шаги:

1) Создать дамп схемы базы данных (--schema-only)

pg_dump mydb -s > schema.sql

2) Отбросить базу данных

drop database mydb;

3) Создать базу данных

create database mydb;

4) Импортировать схему

psql mydb < schema.sql

В этом случае, вероятно, было бы лучше просто иметь пустую базу данных, которую вы используете в качестве шаблона, и, когда вам нужно обновить, удалите существующую базу данных и создайте новую из шаблона.

Просто выполните запрос ниже:

DO $$ DECLARE
    r RECORD;
BEGIN
    FOR r IN (SELECT tablename FROM pg_tables WHERE schemaname = current_schema()) LOOP
        EXECUTE 'TRUNCATE TABLE ' || quote_ident(r.tablename) || '';
    END LOOP;
END $$;

Ребята, лучший и чистый способ:

1) Создать дамп схемы базы данных (--schema-only) pg_dump mydb -s> schema.sql

2) Отбросить базу данных Удалить базу данных mydb;

3) Создать базу данных создать базу данных mydb;

4) Импортировать схему psql mydb ‹schema.sql

Это работает для меня!

Хорошего дня. Хирам Уокер

Могли бы вы использовать динамический SQL для выполнения каждого оператора по очереди? Для этого вам, вероятно, придется написать сценарий PL / pgSQL.

http://www.postgresql.org/docs/8.3/static/plpgsql-statements.html (раздел 38.5.4. Выполнение динамических команд)

Очистка AUTO_INCREMENT версия:

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;

Вы также можете сделать это с помощью bash:

#!/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 "{}"

Вам нужно будет настроить имена схем, пароли и имена пользователей в соответствии с вашими схемами.

Если вы можете использовать psql, вы можете использовать \gexec мета-команду для выполнения запроса выход;

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 

Обратите внимание, что \gexec введен в версию 9.6.

Для удаления данных и сохранения структур таблиц в pgAdmin вы можете:

• Щелкните правой кнопкой мыши по базе данных -> резервное копирование, выберите «Только схема».
• Отбросить базу данных
• Создайте новую базу данных и назовите ее как прежнюю
• Щелкните правой кнопкой мыши новую базу данных -> восстановить -> выберите резервную копию, выберите «Только схема»