Copier une table (y compris les index) dans postgres

Question

J'ai une table postgres. J'ai besoin de supprimer certaines données de cette table. J'allais créer une table temporaire, y copier les données, recréer les index et supprimer les lignes dont j'ai besoin. Je ne peux pas supprimer les données de la table originale, car celle-ci est la source des données. Dans un cas, j'ai besoin d'obtenir des résultats qui dépendent de la suppression de X, dans un autre cas, j'aurai besoin de supprimer Y. J'ai donc besoin que toutes les données originales soient toujours présentes et disponibles.

Cependant, il semble un peu idiot de recréer la table, de la copier à nouveau et de recréer les index. Existe-t-il un moyen de dire à Postgres "Je veux une copie séparée complète de cette table, y compris la structure, les données et les index" ?

Malheureusement, PostgreSQL ne dispose pas d'une fonction "CREATE TABLE COMME X INCLUANT LES INDEX".

Answer 1

Les nouveaux PostgreSQL (depuis la version 8.3 selon la documentation) peuvent utiliser les "INCLUDING INDEXES" :

# select version();
                                             version
-------------------------------------------------------------------------------------------------
 PostgreSQL 8.3.7 on x86_64-pc-linux-gnu, compiled by GCC cc (GCC) 4.2.4 (Ubuntu 4.2.4-1ubuntu3)
(1 row)

Comme vous pouvez le voir, je teste la version 8.3.

Maintenant, créons une table :

# create table x1 (id serial primary key, x text unique);
NOTICE:  CREATE TABLE will create implicit sequence "x1_id_seq" for serial column "x1.id"
NOTICE:  CREATE TABLE / PRIMARY KEY will create implicit index "x1_pkey" for table "x1"
NOTICE:  CREATE TABLE / UNIQUE will create implicit index "x1_x_key" for table "x1"
CREATE TABLE

Et voyez ce que cela donne :

# \d x1
                         Table "public.x1"
 Column |  Type   |                    Modifiers
--------+---------+-------------------------------------------------
 id     | integer | not null default nextval('x1_id_seq'::regclass)
 x      | text    |
Indexes:
    "x1_pkey" PRIMARY KEY, btree (id)
    "x1_x_key" UNIQUE, btree (x)

Nous pouvons maintenant copier la structure :

# create table x2 ( like x1 INCLUDING DEFAULTS INCLUDING CONSTRAINTS INCLUDING INDEXES );
NOTICE:  CREATE TABLE / PRIMARY KEY will create implicit index "x2_pkey" for table "x2"
NOTICE:  CREATE TABLE / UNIQUE will create implicit index "x2_x_key" for table "x2"
CREATE TABLE

Et vérifiez la structure :

# \d x2
                         Table "public.x2"
 Column |  Type   |                    Modifiers
--------+---------+-------------------------------------------------
 id     | integer | not null default nextval('x1_id_seq'::regclass)
 x      | text    |
Indexes:
    "x2_pkey" PRIMARY KEY, btree (id)
    "x2_x_key" UNIQUE, btree (x)

Si vous utilisez PostgreSQL avant la version 8.3, vous pouvez simplement utiliser pg_dump avec l'option "-t" pour spécifier une table, changer le nom de la table dans le dump, et le charger à nouveau :

=> pg_dump -t x2 | sed 's/x2/x3/g' | psql
SET
SET
SET
SET
SET
SET
SET
SET
CREATE TABLE
ALTER TABLE
ALTER TABLE
ALTER TABLE

Et maintenant, la table est dressée :

# \d x3
                         Table "public.x3"
 Column |  Type   |                    Modifiers
--------+---------+-------------------------------------------------
 id     | integer | not null default nextval('x1_id_seq'::regclass)
 x      | text    |
Indexes:
    "x3_pkey" PRIMARY KEY, btree (id)
    "x3_x_key" UNIQUE, btree (x)

Answer 2

[CREATE [ [ GLOBAL | LOCAL ] { TEMPORARY | TEMP } ] TABLE table_name
    [ (column_name [, ...] ) ]
    [ WITH ( storage_parameter [= value] [, ... ] ) | WITH OIDS | WITHOUT OIDS ]
    [ ON COMMIT { PRESERVE ROWS | DELETE ROWS | DROP } ]
    [ TABLESPACE tablespace ]
    AS query][1]  

Voici un exemple exemple

CREATE TABLE films_recent AS
  SELECT * FROM films WHERE date_prod >= '2002-01-01';

L'autre façon de créer une nouvelle table à partir de la première est d'utiliser

    CREATE TABLE films_recent (LIKE films INCLUDING INDEXES);  

    INSERT INTO films_recent
         SELECT *
           FROM books
          WHERE date_prod >= '2002-01-01';  

Notez que Postgresql dispose d'une fonction correctif pour résoudre les problèmes liés à l'espace de stockage si la deuxième méthode est utilisée

Answer 3

Il existe de nombreuses réponses sur le web, l'une d'entre elles se trouve à l'adresse suivante aquí .

J'ai fini par faire quelque chose comme ça :

create table NEW ( like ORIGINAL including all);
insert into NEW select * from ORIGINAL

Cette opération permet de copier le schéma et les données, y compris les index, mais pas les déclencheurs ni les contraintes. Notez que les index sont partagés avec la table d'origine, donc lorsque vous ajoutez une nouvelle ligne dans l'une ou l'autre table, le compteur s'incrémente.

Answer 4

J'ai une table postgres. [ ] supprimer certaines données.

Je suppose que ...

delete from yourtable
where <condition(s)>

... ne fonctionne pas pour une raison quelconque. (Vous pouvez nous faire part de cette raison ?)

I copier les données, recréer les index. index et supprimer les lignes dont j'ai besoin. besoin.

Consultez pg_dump et pg_restore. L'utilisation de pg_dump avec quelques options intelligentes et peut-être l'édition de la sortie avant pg_restore pourraient faire l'affaire.

---

Étant donné que vous effectuez des analyses de type "what if" sur les données, je me demande s'il ne serait pas préférable d'utiliser des vues.

Vous pourriez définir une vue pour chaque scénario que vous souhaitez tester en vous basant sur la négation de ce que vous souhaitez exclure. C'est-à-dire définir une vue basée sur ce que vous voulez INclure. Par exemple, si vous voulez une "fenêtre" sur les données où vous avez "supprimé" les lignes où X=Y, vous devez créer une vue comme lignes où (X != Y).

Les vues sont stockées dans la base de données (dans le catalogue des systèmes) sous la forme de la requête qui les définit. Chaque fois que vous interrogez la vue, le serveur de base de données recherche la requête sous-jacente qui la définit et l'exécute (ET avec toutes les autres conditions que vous avez utilisées). Cette approche présente plusieurs avantages :

1. Vous ne dupliquez jamais une partie de vos données.
2. Les index déjà utilisés pour la table de base (votre table originale, "réelle") seront utilisés (comme le jugera bon l'optimiseur de requêtes) lorsque vous interrogerez chaque vue/scénario. Il n'est pas nécessaire de les redéfinir ou de les copier.
3. Étant donné qu'une vue est une "fenêtre" (PAS un shapshot) sur les données "réelles" de la table de base, vous pouvez ajouter/mettre à jour/supprimer dans votre table de base et simplement réinterroger les scénarios de la vue sans avoir à recréer quoi que ce soit au fur et à mesure que les données changent au fil du temps.

Il y a bien sûr un compromis à faire. Étant donné qu'une vue est une table virtuelle et non une table "réelle" (de base), vous exécutez en fait une requête (peut-être complexe) chaque fois que vous y accédez. Cela peut ralentir un peu les choses. Mais ce n'est pas forcément le cas. Cela dépend de nombreux facteurs (taille et nature des données, qualité des statistiques du catalogue des systèmes, vitesse du matériel, charge d'utilisation, etc.) Vous ne le saurez pas tant que vous n'aurez pas essayé. Si (et seulement si) vous constatez que les performances sont inacceptablement lentes, vous pouvez envisager d'autres options. (Vues matérialisées, copies de tables, ... tout ce qui échange de l'espace contre du temps).

Answer 5

Un moyen simple est d'inclure tout le monde :

CREATE TABLE new_table (LIKE original_table INCLUDING ALL);