DESCARGO de responsabilidad: Esta pregunta es similar a la de desbordamiento de pila pregunta aquí, pero ninguna de esas respuestas para mi problema, como explicaré más adelante.

Estoy tratando de copiar una tabla grande (~40M filas, 100+ columnas) en postgres, donde muchas de las columnas indizadas. Actualmente yo uso este bits de SQL:

CREATE TABLE <tablename>_copy (LIKE <tablename> INCLUDING ALL);
INSERT INTO <tablename>_copy SELECT * FROM <tablename>;

Este método tiene dos problemas:

  1. Añade los índices antes de que los datos de ingesta, por lo que tomará mucho más tiempo que la creación de la tabla sin índices y, a continuación, la indexación después de copiar todos los datos.
  2. Esto no copia `de SERIE’ columnas de estilo correctamente. En lugar de configurar una nueva ‘contador’ en la nueva tabla, se establece el valor predeterminado de la columna en la nueva tabla para el contador de los últimos de la tabla, lo que significa que no incremento como se agregan filas.

El tamaño de la tabla hace que la indexación de un real problema de tiempo. También hace que sea imposible de volcar a un archivo, a continuación, volver a ingerir. No tengo también la ventaja de una línea de comandos. Necesito hacer esto en SQL.

De lo que me gustaría hacer es recto a hacer una copia exacta con algún milagro de comandos, o si no es posible, para copiar la tabla con todos los límites impuestos pero sin índices, y asegúrese de que sean las limitaciones ‘en espíritu’ (también conocido como un nuevo contador para una SERIE de columnas). Luego copiar todos los datos con un SELECT * y, a continuación, copie todos los índices.

Fuentes

  1. Desbordamiento de pila pregunta acerca de la copia de la base de datos: Esto no es lo que estoy pidiendo por tres razones

    • Utiliza la opción de línea de comandos pg_dump -t x2 | sed 's/x2/x3/g' | psql y en este contexto no tengo acceso a la línea de comandos
    • Crea los índices de pre datos de ingesta, que es lento
    • No actualización de la serie de columnas correctamente, como se evidencia por default nextval('x1_id_seq'::regclass)
  2. Método para restablecer el valor de secuencia para una tabla de postgres: Esto es genial, pero por desgracia es muy manual.

  • Su pregunta es probable que un duplicado de stackoverflow.com/questions/198141/…
  • Vi a esa pregunta, no hubo respuestas satisfactorias que puede hacer realmente lo que estoy pidiendo, pero esto me impulsa a hacer otra edición de mi post.
  • No hay una mejor respuesta.
  • Hay tres problemas principales con el más votado de la solución en la página. Uno, que el uso de la línea de comandos funciones en pg_dump -t x2 | sed 's/x2/x3/g' | psql que no tengo acceso. Dos, crea los índices antes de agregar los datos que va a ser muy lento! Tres, el de SERIE por defecto del parámetro hace referencia al primer lugar de la tabla de default nextval('x1_id_seq'::regclass). Estos son los tres errores ya he señalado en mi pregunta. Usted me está diciendo que no hay solución a alguno de estos? @pedro
  • Es sólo una Pequeña Cuestión de Programación.
InformationsquelleAutor Erik | 2011-07-06

7 Comentarios

  1. 56

    Bien, vas a tener que hacer algunas de estas cosas a mano, por desgracia. Pero todo se puede hacer de algo como psql. El primer comando es bastante simple:

    select * into newtable from oldtable

    Esto creará tablanueva con oldtable de datos, pero no los índices. A continuación, tienes que crear los índices y las secuencias etc en su propio. Usted puede obtener una lista de todos los índices de una tabla con el comando:

    select indexdef from pg_indexes where tablename='oldtable';

    A continuación, ejecute psql -E para acceder a su base de datos y el uso de \d a mirar a la mesa de edad. Luego puede destrozar estas dos consultas para obtener la información sobre las secuencias:

    SELECT c.oid,
      n.nspname,
      c.relname
    FROM pg_catalog.pg_class c
         LEFT JOIN pg_catalog.pg_namespace n ON n.oid = c.relnamespace
    WHERE c.relname ~ '^(oldtable)$'
      AND pg_catalog.pg_table_is_visible(c.oid)
    ORDER BY 2, 3;
    
    SELECT a.attname,
      pg_catalog.format_type(a.atttypid, a.atttypmod),
      (SELECT substring(pg_catalog.pg_get_expr(d.adbin, d.adrelid) for 128)
       FROM pg_catalog.pg_attrdef d
       WHERE d.adrelid = a.attrelid AND d.adnum = a.attnum AND a.atthasdef),
      a.attnotnull, a.attnum
    FROM pg_catalog.pg_attribute a
    WHERE a.attrelid = '74359' AND a.attnum > 0 AND NOT a.attisdropped
    ORDER BY a.attnum;

    Reemplazar 74359 arriba con el oid que se obtiene de la consulta anterior.

    • Tenga en cuenta que si desea las secuencias de ser dependiente en el nuevo padre de la tabla que usted tendrá que usar «alterar la secuencia de seqname propiedad de tablanueva.columna;»
    • Gracias. Con algunas modificaciones de este código, yo soy capaz de hacer lo que yo necesitaba.
  2. 52

    La create table as función en PostgreSQL, ahora puede ser la respuesta que el OP estaba buscando.

    https://www.postgresql.org/docs/9.5/static/sql-createtableas.html

    create table my_table_copy as
      select * from my_table

    Esto creará una idéntica tabla con los datos.

    La adición de with no data va a copiar el esquema sin los datos.

    create table my_table_copy as
      select * from my_table
    with no data

    Esto va a crear la tabla con todos los datos, pero sin índices y factores desencadenantes etc.


    create table my_table_copy (like my_table including all)

    El crear la tabla de sintaxis se incluyen todos los triggers, índices, restricciones, etc. Pero no se incluyen los datos.

    • He publicado esta pregunta hace mucho tiempo que no tengo manera fácil de verificar esto fácilmente. Sin embargo, no parece que el «crear tabla como» copias de otros objetos asociados con la tabla, como los índices y las secuencias.
    • Ah tienes razón @Erik. Lo que es una vergüenza. Yo voy a dejar mi respuesta en caso de que alguien más lo encuentra útil. Pero añadió una nota para decir que no copia el resto de la información. Gracias.
    • De hecho, ha sido muy útil en la que indica las limitaciones. Ahora sabemos qué mirar hacia fuera para cuando se utiliza este método. Gracias!
    • (like my_table including all) no puede satisfacer el OP, que no quería índices, pero es perfecto para mí, tratando de obtener una tabla con todas las restricciones.
  3. 14

    El más cercano «milagro comando» es algo así como

    pg_dump -t tablename | sed -r 's/\btablename\b/tablename_copy/' | psql -f -

    En particular, se ocupa de la creación de los índices después de cargar los datos de la tabla.

    Pero que no restablece las secuencias; tendrá a la secuencia de comandos que usted mismo.

  4. 6

    Para copiar una tabla completamente, incluyendo tanto la estructura de la tabla y los datos, se utiliza la siguiente instrucción:

    CREATE TABLE new_table AS 
    TABLE existing_table;

    Para copiar una estructura de tabla sin datos, agregar el que NO se tienen DATOS de la cláusula de la instrucción CREATE TABLE de la siguiente manera:

    CREATE TABLE new_table AS 
    TABLE existing_table 
    WITH NO DATA;

    Para copiar una tabla con los datos parciales de una tabla existente, utilice la siguiente instrucción:

    CREATE TABLE new_table AS 
    SELECT
    *
    FROM
        existing_table
    WHERE
        condition;
    • Puede vincular una referencia? No pude encontrar ninguna información sobre la sintaxis en postgresql.org. Algunas notables preguntas son: a) son los índices de conserva? b) ¿qué versiones de postgres es válido en?
  5. 2

    ADVERTENCIA:

    Todas las respuestas que el uso de pg_dump y cualquier tipo de expresión regular para reemplazar el nombre de la tabla de origen son realmente peligrosos. ¿Qué pasa si los datos contienen la subcadena que usted está tratando de reemplazar? Usted va a terminar cambiando sus datos!

    Propongo un pase de dos solución:

    1. eliminar líneas de datos de la volcado el uso de algunos datos específicos de la regexp
    2. realizar operaciones de búsqueda y reemplazo en el resto de las líneas

    He aquí un ejemplo escrito en Ruby:

    ruby -pe 'gsub(/(members?)/, "\1_copy_20130320") unless $_ =~ /^\d+\t.*(?:t|f)$/' < members-production-20130320.sql > copy_members_table-20130320.sql

    En el anterior estoy tratando de copiar «los miembros de la» tabla a «members_copy_20130320». Mis datos específicos de la regexp es /^\d+\t.*(?:t|f)$/

    El anterior tipo de solución que funciona para mí. Caveat emptor…

    edición:

    Bien, esta es otra manera en la pseudo-shell sintaxis para la regexp-aversión a la gente:

    1. pg_dump -s -t mytable mydb > mytable_schema.sql
    2. de búsqueda y reemplazo nombre de la tabla en mytable_schema.sql > mytable_copy_schema.sql
    3. psql -f mytable_copy_schema.sql mydb

    4. pg_dump -a-t mytable mydb > mytable_data.sql

    5. reemplazar «mytable» en los pocos instrucción SQL anteriores a la sección de datos
    6. psql -f mytable_data.sql mydb
  6. 0

    Al parecer quiere «reconstruir» una tabla. Si sólo desea reconstruir una tabla, no copia, entonces usted debe utilizar CLUSTER lugar.

    SELECT count(*) FROM table; -- make a seq scan to make sure the table is at least
                                -- decently cached
    CLUSTER someindex ON table;

    Que puedes elegir el índice, trate de elegir uno que se adapte a sus consultas. Siempre se puede usar la clave primaria si no hay otro índice es adecuado.

    Si la tabla es demasiado grande para almacenarse en caché, el CLÚSTER puede ser lento, aunque.

    • Yo realmente desea copiar, he quitado el código adicional que no estaba realmente respectivo a la pregunta. Por lo que puedo ver, en el grupo sólo reordena las filas basadas en el índice, que no es realmente lo que estoy buscando. Lo siento por la desinformación.
  7. -1

    crear tabla newTableName (como oldTableName incluidos los índices);
    insert into newTableName select * from oldTableName

    Esto funcionó para mí 9.3

Dejar respuesta

Please enter your comment!
Please enter your name here