sql - sintaxis - Copie una tabla(incluidos índices) en postgres

manejo de indices en sql

Question

Tengo una tabla de postgres. Necesito borrar algunos datos de él. Iba a crear una tabla temporal, copiar los datos, recrear los índices y eliminar las filas que necesito. No puedo eliminar datos de la tabla original, porque esta tabla original es la fuente de datos. En un caso, necesito obtener algunos resultados que dependen de eliminar X, en otro caso, necesitaré eliminar Y. Por lo tanto, necesito que todos los datos originales estén siempre disponibles.

Sin embargo, parece un poco tonto para recrear la tabla y copiarla de nuevo y recrear los índices. ¿Hay alguna forma en postgres que diga "Quiero una copia completa de esta tabla, incluida la estructura, los datos y los índices"?

Desafortunadamente, PostgreSQL no tiene una "CREATE TABLE .. LIKE X INCLUDING INDEXES"

Answer 1

Tengo una tabla de postgres. Necesito borrar algunos datos de él.

Supongo que ...

delete from yourtable
where <condition(s)>

... no funcionará por alguna razón. (¿Te importa compartir esa razón?)

Iba a crear una tabla temporal, copiar los datos, recrear los índices y eliminar las filas que necesito.

Mire en pg_dump y pg_restore. Usar pg_dump con algunas opciones inteligentes y tal vez editar la salida antes de pg_restoring podría hacer el truco.

Dado que está haciendo un análisis de tipo "qué pasaría si" en los datos, me pregunto si sería mejor utilizar las vistas.

Puede definir una vista para cada escenario que desee probar en función de la negación de lo que desea excluir. Es decir, defina una vista basada en lo que desea incluir. Por ejemplo, si quiere una "ventana" en los datos donde "eliminó" las filas donde X = Y, entonces crearía una vista como filas donde (X! = Y).

Las vistas se almacenan en la base de datos (en el Catálogo del sistema) como su consulta de definición. Cada vez que consulta la vista, el servidor de la base de datos busca la consulta subyacente que la define y la ejecuta (AND con cualquier otra condición que haya utilizado). Hay varios beneficios para este enfoque:

1. Nunca duplica ninguna porción de sus datos.
2. Los índices que ya están en uso para la tabla base (su tabla original, "real") se usarán (según lo considere el optimizador de consultas) cuando consulte cada vista / escenario. No es necesario redefinirlos ni copiarlos.
3. Como una vista es una "ventana" (NO una instantánea) de los datos "reales" en la tabla base, puede agregar / actualizar / eliminar en su tabla base y simplemente volver a consultar los escenarios de vista sin necesidad de recrear nada como los datos cambian con el tiempo

Hay una compensación, por supuesto. Como una vista es una tabla virtual y no una tabla "real" (base), en realidad está ejecutando una consulta (tal vez compleja) cada vez que accede a ella. Esto puede desacelerar un poco las cosas. Pero puede que no. Depende de muchos problemas (tamaño y naturaleza de los datos, calidad de las estadísticas en el catálogo del sistema, velocidad del hardware, carga de uso y mucho más). No lo sabrás hasta que lo pruebes. Si (y solo si) realmente encuentra que el rendimiento es inaceptablemente lento, entonces puede ver otras opciones. (Vistas materializadas, copias de tablas, ... cualquier cosa que intercambie espacio por tiempo).

Answer 2

Cree una nueva tabla usando una selección para captar los datos que desee. Luego cambie la tabla anterior por la nueva.

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

Answer 3

Nuevo PostgreSQL (desde 8.3 según docs) puede usar "INCLUYENDO ÍNDICES":

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

Como pueden ver estoy probando en 8.3.

Ahora, creemos la tabla:

# 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

Y mira cómo se ve:

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

Ahora podemos copiar la estructura:

# 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

Y revisa la estructura:

# /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 está utilizando PostgreSQL pre-8.3, puede simplemente usar pg_dump con la opción "-t" para especificar 1 tabla, cambiar el nombre de la tabla en el volcado y cargarlo de nuevo:

=> 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

Y ahora la mesa es:

# /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 4

[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]  

Aquí hay un example

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

La otra forma de crear una nueva tabla a partir de la primera es usar

    CREATE TABLE films_recent (LIKE films INCLUDING INDEXES);  

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

Tenga en cuenta que Postgresql tiene un patch para solucionar los problemas del tablespace si se utiliza el segundo método