Tronquer toutes les tables d'une base de données Postgres

Question

J'ai régulièrement besoin de supprimer toutes les données de ma base de données PostgreSQL avant une reconstruction. Comment puis-je le faire directement en SQL ?

Pour l'instant, j'ai réussi à trouver une instruction SQL qui renvoie toutes les commandes que je dois exécuter :

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

Mais je ne vois pas comment les exécuter par programme une fois que je les ai.

Answer 1

FrustratedWithFormsDesigner a raison, PL/pgSQL peut le faire. Voici le script :

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;

Cela crée une fonction stockée (vous ne devez le faire qu'une seule fois) que vous pouvez ensuite utiliser comme ceci :

SELECT truncate_tables('MYUSER');

Answer 2

Les curseurs explicites sont rarement nécessaires en plpgsql. Utilisez la méthode plus simple et plus rapide curseur implicite d'un FOR boucle :

Nota: Comme les noms de table ne sont pas uniques par base de données, vous devez qualifier les noms de table en fonction du schéma pour être sûr. En outre, je limite la fonction au schéma par défaut "public". Adaptez-la à vos besoins, mais veillez à exclure les schémas du système. pg_* y information_schema .

Soyez très prudent avec ces fonctions. Ils détruisent votre base de données. J'ai ajouté une sécurité enfant. Commente le RAISE NOTICE et décommentez EXECUTE pour amorcer la bombe ...

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() nécessite Postgres 9.1 ou une version ultérieure. Dans les versions plus anciennes, concaténer la chaîne de requête comme ceci :

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

Commande unique, pas de boucle

Puisque nous pouvons TRUNCATE plusieurs tables à la fois, nous n'avons pas besoin de curseur ou de boucle :

• Passer les noms de tables dans un tableau

Agrégez tous les noms de table et exécutez une seule instruction. Plus simple, plus rapide :

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;

Appelez :

SELECT truncate_tables('postgres');

Requête affinée

Vous n'avez même pas besoin d'une fonction. Dans Postgres 9.0+, vous pouvez exécuter des commandes dynamiques dans une fonction DO déclaration. Et dans Postgres 9.5+, la syntaxe peut être encore plus simple :

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

A propos de la différence entre pg_class , pg_tables y information_schema.tables :

• Comment vérifier si une table existe dans un schéma donné ?

À propos de regclass et les noms de table cités :

• Nom de table comme paramètre de fonction PostgreSQL

Pour un usage répété

Crear un Base de données "modèle". (appelons-la my_template ) avec votre structure vanille et toutes les tables vides. Puis passez par un DROP / CREATE DATABASE cycle :

DROP DATABASE mydb;
CREATE DATABASE mydb TEMPLATE my_template;

C'est extrêmement rapide car Postgres copie l'ensemble de la structure au niveau du fichier. Il n'y a pas de problèmes de concurrence ou d'autres frais généraux qui vous ralentissent.

Si les connexions simultanées vous empêchent d'abandonner le DB, envisagez de le faire :

• Force drop db alors que d'autres peuvent être connectés

Answer 3

Si je dois le faire, je vais simplement créer un schéma sql de la base de données actuelle, puis abandonner et créer la base de données, puis charger la base de données avec le schéma sql.

Voici les étapes à suivre :

1) Créer un dump de schéma de la base de données ( --schema-only )

pg_dump mydb -s > schema.sql

2) Abandon de la base de données

drop database mydb;

3) Créer une base de données

create database mydb;

4) Importer le schéma

psql mydb < schema.sql

Answer 4

Dans ce cas, il serait probablement préférable de disposer d'une base de données vide que vous utilisez comme modèle et, lorsque vous avez besoin d'une actualisation, de supprimer la base de données existante et d'en créer une nouvelle à partir du modèle.

Answer 5

Légèrement adapté...

CREATE OR REPLACE FUNCTION truncate_tables(username IN VARCHAR) RETURNS void AS $$
DECLARE
    stmt RECORD;
    statements CURSOR FOR SELECT tablename FROM pg_tables WHERE tableowner = username;
BEGIN
    FOR stmt IN statements LOOP
        EXECUTE 'TRUNCATE TABLE ' || quote_ident(stmt.tablename) || ' CASCADE;';
    END LOOP;
END;                         
$$ LANGUAGE 'plpgsql';