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

Question

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

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

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

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

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

220

postgresql plpgsql dynamic-sql truncate database-table

Источник

user340567 13 май '10 в 18:04

14 ответов

Решение

Явные курсоры редко нужны в 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 и цитируемые имена таблиц:

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

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

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

DROP DATABASE mydb;
CREATE DATABASE mydb TEMPLATE my_template;

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

128

Источник

user939860 22 авг '12 в 22:01

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

Ниже приведены этапы:

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

pg_dump mydb -s > schema.sql

2) Удалить базу данных

drop database mydb;

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

create database mydb;

4) Схема импорта

psql mydb < schema.sql

54

Источник

user629873 05 авг '14 в 10:21

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

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

30

Источник

user5597680 03 авг '20 в 12:53

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

10

Источник

user287611 13 май '10 в 20:35

Просто вы можете запустить этот кусок 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) || ' CASCADE';
  END LOOP;
END $$;

7

Источник

user13404524 28 ноя '22 в 08:21

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

      TRUNCATE TABLE table_name RESTART IDENTITY CASCADE;

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

7

Источник

user1673161 21 авг '22 в 17:41

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

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

2) Удалить базу данных, удалить базу данных mydb;

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

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

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

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

5

Источник

user9658864 05 июн '18 в 15:01

Очищающий 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;

4

Источник

user2332416 17 авг '17 в 19:42

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

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

3

Источник

user87191 09 апр '13 в 13:56

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

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

3

Источник

user192801 13 май '10 в 18:06

Если вы можете использовать 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

2

Источник

user3557676 17 мар '20 в 15:23

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

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

2

Источник

user3424416 29 мар '17 в 12:09

Вы можете использовать что-то подобное, чтобы получить все усеченные запросы.

      SELECT 'TRUNCATE TABLE ' ||  table_name || ';' 
  FROM information_schema.tables
 WHERE table_schema='schema_name'
   AND table_type='BASE TABLE';

-2

Источник

user1700885 26 апр '22 в 07:07

Другие вопросы по тегам postgresql plpgsql dynamic-sql truncate database-table

user29549 13 май '10 в 19:02 2010-05-13 19:02 · Accepted Answer · 2010-05-13 19:02

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