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

мне регулярно нужно удалять все данные из моей базы данных PostgreSQL перед перестроением. Как бы я сделал это непосредственно в SQL?



на данный момент мне удалось придумать инструкцию SQL, которая возвращает все команды, которые мне нужно выполнить: 



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


но я не вижу способа выполнить их программно, как только они у меня есть.

576   9  

9 ответов:

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');
177  
2017-09-03 02:25:21

явные курсоры редко требуются в 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    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() требуется 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
   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;

звоните:

SELECT truncate_tables('postgres');

изысканный запрос

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

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$;

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

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

для повторного использования

это может быть проще и (намного) быстрее, чтобы создать базу данных " шаблон (назовем его my_template) С вашей структурой ванили и всеми пустыми таблицами. Тогда пройдите через DROP/CREATE DATABASE цикл:

DROP DATABASE mydb;
CREATE DATABASE mydb TEMPLATE my_template;

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

65  
2018-08-02 14:03:37

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

Ниже приведены шаги, связанные с:

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

pg_dump mydb -s > schema.sql

2) Drop database

drop database mydb;

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

create database mydb;

4) Импорт Схемы

psql mydb < schema.sql

24  
2016-11-14 12:59:51

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

7  
2010-05-13 23:35:44

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

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

2  
2010-05-13 21:29:44

вы можете сделать это с 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 "{}"

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

1  
2013-04-09 16:56:53

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

  • щелкните правой кнопкой мыши база данных - > резервное копирование, выберите "только схема"
  • удалить базу данных
  • создайте новую базу данных и назовите ее как прежнюю
  • щелкните правой кнопкой мыши новую базу данных - > восстановить - > выберите резервную копию, выберите "только схема"
0  
2017-03-29 15:09:33

уборка 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;
0  
2017-08-17 22:42:17

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

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

2) падение базы данных drop database mydb;

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

4) Схема Импорта используя psql базы данных mydb

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

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

0  
2018-06-05 18:01:10

Comments

    Ничего не найдено.
Click here to cancel reply.