Copier un tableau (y compris les index) dans postgres

Nouveau PostgreSQL (depuis 8.3 selon docs ) peut utiliser "y compris les index":

# 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 sur 8.3.

maintenant, créons la 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 voir à quoi il ressemble:

# \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)

maintenant nous pouvons 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érifier 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 pre-8.3, vous pouvez simplement utiliser pg_dump avec l'option"- t "pour spécifier 1 table, changer le nom de la table dans 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 le tableau est:

# \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)

[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

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 des

    CREATE TABLE films_recent (LIKE films INCLUDING INDEXES);  

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

notez que Postgresql a un patch pour corriger les problèmes de tablespace si la deuxième méthode est utilisée

j'ai une table postgres. J'ai besoin de supprimer des données à partir d'elle.

je présume ...

delete from yourtable
where <condition(s)>

... ne fonctionne pas pour une raison quelconque. (Vous voulez partager cette raison?)

j'allais créer un temporaire tableau, copier les données dans le, de recréer la les index et les supprimer les lignes que j' besoin.

regardez pg_dump et pg_restore. Utiliser pg_dump avec quelques options intelligentes et peut-être éditer la sortie avant pg_restoring pourrait faire l'affaire.

---

puisque vous faites une analyse de type "what if" sur les données, je me demande si vous ne seriez pas plus à l'aise en utilisant des vues.

, Vous pouvez définir une vue pour chaque scénario de test basée sur la négation de ce que vous souhaitez exclure. I. e., définir une vision fondée sur les éléments que vous souhaitez Inclure. Par exemple, si vous voulez une" fenêtre " sur les données où vous avez "supprimé" les lignes où X=Y, alors vous créeriez une vue comme des lignes où (X != Y).

vues sont stockées dans la base de données (dans le catalogue système) comme leur requête de définition. 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 exécute cela (avec toutes les autres conditions que vous avez utilisées). Cette approche comporte plusieurs avantages:

1. vous ne dupliquez jamais aucune partie de votre données.
2. les index déjà utilisés pour la table de base (votre table originale," réelle") seront utilisés (comme indiqué par l'optimiseur de requêtes) lorsque vous interrogez chaque vue/scénario. Il n'est pas nécessaire de redéfinir ou de les copier.
3. Puisqu'une vue est une" fenêtre "(pas un shapshot) sur les données" réelles " dans la table de base, vous pouvez ajouter/mettre à jour/supprimer sur votre table de base et simplement re-requête les scénarios de vue sans besoin de recréer quoi que ce soit que les données changent avec le temps.

il y a un compromis, bien sûr. Comme 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 pourrait ralentir un peu les choses. Mais il ne peut pas. Cela dépend de nombreuses questions (taille et nature des données, Qualité des statistiques dans le catalogue système, Vitesse du matériel, charge d'utilisation, et bien plus encore). Tu ne le sauras pas avant d'avoir essayé. Si (et seulement si) vous trouvez que la performance est d'une lenteur inacceptable, alors vous pourriez regarder d'autres options. (Vues matérialisées, copies de tableaux, ... tout ce qui échange l'Espace contre le temps.)

créer une nouvelle table en utilisant un select pour saisir les données que vous voulez. Ensuite, changez l'ancienne table avec la nouvelle.

create table mynewone as select * from myoldone where ...
mess (re-create) with indexes after the table swap.