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

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

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

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

Но я не могу понять, как их программно реализовать, как только я их получу.

9 Solutions collect form web for “Усечение всех таблиц в базе данных 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 :

Примечание. Так как имена таблиц не уникальны для каждой базы данных, вы должны убедиться в правильности имен таблиц. Кроме того, я ограничиваю функцию по умолчанию «общедоступной». Адаптируйте к вашим потребностям, но обязательно исключите системные схемы 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 и цитируемых таблиц:

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

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

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

 DROP DATABASE mydb; CREATE DATABASE mydb TEMPLATE my_template ; 

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

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

Ниже приведены шаги:

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

pg_dump mydb -s > schema.sql

2) База данных Drop

drop database mydb;

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

create database mydb;

4) Импортная схема

psql mydb < schema.sql

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

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

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

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

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

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

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

Очистка 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; 

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

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

2) База данных о распаке базы данных mydb;

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

4) Импортная схема psql mydb

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

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

Interesting Posts

Как отладить ошибку w3wp clr.dll

Переходы на дисплее: свойство

Не удалось выполнить развертывание Heroku из-за ошибки sqlite3 gem

Почему StyleCop рекомендует использовать метод префикса или свойства с помощью «этого»?

Что может привести к тому, что изображения не будут отображаться в справке HTML (chm-файлы), если UAC включен?

Когда использовать утверждение и когда использовать исключение

Команды вызова WPF через события

self.title устанавливает заголовок navigationController и tabBarItem? Зачем?

Замена NA с последним значением, отличным от NA

Отображение негативных меток времени в Excel 2007chart

Android: Как я могу проверить ввод EditText?

Угловое: не удается найти Promise, Map, Set и Iterator

Как получить размеры таблиц базы данных MySQL?

Google Chrome не может получить доступ к доменам localhost

Имеет ли этот код из раздела «Язык программирования C ++» раздел 36.3.6 четкого поведения?

Давайте будем гением компьютера.