Copier un tableau (index compris) dans postgres

New PostgreSQL (depuis la version 8.3 selon docs) peut utiliser “INDEXES INCLUSES”:

# select version(); version ------------------------------------------------------------------------------------------------- PostgreSQL 8.3.7 on x86_64-pc-linux-gnu, comstackd 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 un tableau:

 # 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 à quoi ça 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é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 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 la table est la suivante:

 # \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 du premier 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 a un correctif pour résoudre les problèmes de tablespace si la seconde méthode est utilisée

J’ai une table de postgres. Je dois en supprimer certaines.

Je présume que …

 delete from yourtable where  

… ne fonctionnera pas pour une raison quelconque. (Attention à partager cette raison?)

J’allais créer une table temporaire, copier les données, recréer les index et supprimer les lignes dont j’ai besoin.

Regardez dans pg_dump et pg_restore. Utiliser pg_dump avec des options intelligentes et peut-être éditer la sortie avant pg_restoring peut faire l’affaire.

Étant donné que vous faites une parsing de type “what if” sur les données, je me demande si vous pourriez mieux vous servir des vues.

Vous pouvez définir une vue pour chaque scénario à tester en fonction de la négation de ce que vous souhaitez exclure. Par exemple, définissez une vue en fonction de ce que vous voulez inclure. Par exemple, si vous voulez une “fenêtre” sur les données où vous “supprimez” les lignes où X = Y, vous créerez alors une vue sous forme de lignes où (X! = Y).

Les vues sont stockées dans la firebase database (dans le catalogue système) en tant que requête de définition. Chaque fois que vous interrogez la vue, le serveur de firebase database recherche la requête sous-jacente qui le définit et l’exécute (avec toutes les autres conditions 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 “réelle” d’origine) seront utilisés (comme indiqué par l’optimiseur de requête) lorsque vous interrogez chaque vue / scénario. Il n’est pas nécessaire de les redéfinir ou de les copier.
3. Comme une vue est une “fenêtre” (PAS un shapshot) sur les “vraies” données de la table de base, vous pouvez append / mettre à jour / supprimer sur votre table de base et interroger simplement les scénarios d’affichage sans avoir à recréer quoi que ce soit 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 “vraie” table (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 peut-être pas. Cela dépend de nombreux problèmes (taille et nature des données, qualité des statistiques dans le catalogue système, vitesse du matériel, charge d’utilisation, etc.). Vous ne saurez pas jusqu’à ce que vous l’essayiez. Si (et seulement si) vous constatez que la performance est trop lente, vous pouvez envisager d’autres options. (Vues matérialisées, copies de tableaux,… tout ce qui traite de l’espace pour le temps.)

Créez une nouvelle table en utilisant une sélection pour récupérer les données souhaitées. Puis échangez l’ancienne table avec la nouvelle.

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