Tengo una tabla con más de millones de filas. Necesito restablecer la secuencia y volver a asignar el id de columna con nuevos valores (1, 2, 3, 4… etc…). Es de ninguna manera fácil de hacerlo?

  • La verdadera pregunta: ¿por qué quieres hacer eso? Es de suponer que el ID es la clave primaria, por lo que no hay ningún beneficio en el cambio de la clave principal. Una clave principal es un sin sentido (en su caso artificial) de valor. «Renumeración» no sirve a ningún sensato propósito en una base de datos relacional.
  • Inicialmente tuve la aplicación se ejecuta localmente, luego he copiado los datos en producción. Pero ids no empezar a partir de 1. Así que el orden que resultó de la siguiente manera: 150, 151…, 300, 1, 2… para hacer duplicados de errores de identificación, finalmente, supongo que, si no hubiera una renumeración de los identificadores. Además, la orden por id es generalmente mejor que el orden de created_at. Y aquí está lo que funcionó para mí.
  • El punto de hacer esto es que usted puede seguir para el uso regular de la int en lugar de bigint para una clave principal en una base de datos que sigue el incremento de la secuencia de la clave, pero recibe constantemente nuevos datos. Usted podrá ejecutar rápidamente en el entero con signo de límite, y si de retención existentes id no es importante, este proceso va a traer de vuelta a manejables los números de identificación.
InformationsquelleAutor sennin | 2011-01-13

13 Comentarios

  1. 163

    Si usted no desea conservar el orden de los identificadores, entonces usted puede

    ALTER SEQUENCE seq RESTART WITH 1;
    UPDATE t SET idcolumn=nextval('seq');

    Me cabe duda de que hay una manera fácil de hacer que en el orden de su elección sin necesidad de recrear toda la tabla.

    • No debería ser ALTER SEQUENCE seq RESTART WITH 1;?
    • Creo que tienes razón.
    • Esto podría hacer que los identificadores duplicados. Para evitar esto, puede que el primer conjunto de todos los valores muy elevados: ACTUALIZACIÓN de t SET idcolumn=1000000+ nextval(‘seq’); a continuación, ejecute la secuencia de comandos anterior.
    • Buen punto. O ejecutar en la transacción con diferido restricciones.
    • SELECT setval('seq', 1, FALSE) debe hacer lo mismo (aquí, el tercer argumento, FALSO, ¿la magia, como muestra de que nextval debe ser de 1 en lugar de 2)
    • absolutamente.

  2. 35

    Restablecer la secuencia:

    SELECT setval('sequence_name', 0);

    La actualización de los registros actuales:

    UPDATE foo SET id = DEFAULT;
    • La secuencia podría tener un valor mínimo mayor que 0. (Y el valor mínimo predeterminado utilizado por el tipo de serial y CREATE SEQUENCE es de 1!)
  3. 31

    Con PostgreSQL 8.4 o más reciente no es necesario especificar el WITH 1 más. El valor de inicio que fue grabado por CREATE SEQUENCE o el último set por ALTER SEQUENCE START WITH serán utilizados (probablemente este será 1).

    Restablecer la secuencia:

    ALTER SEQUENCE seq RESTART;

    A continuación, actualizar la tabla de la columna ID:

    UPDATE foo SET id = DEFAULT;

    Fuente: PostgreSQL Docs

    • Esto parece la mejor respuesta ya que evita hacer suposiciones sobre el valor de inicio de la secuencia.
  4. 16

    Ambas soluciones no me funciona;

    > SELECT setval('seq', 0);
    ERROR:  setval: value 0 is out of bounds for sequence "seq" (1..9223372036854775807)

    setval('seq', 1) se inicia la numeración con 2, y ALTER SEQUENCE seq START 1 se inicia la numeración con 2 así, porque ss.is_called es cierto (Postgres versión 9.0.4)

    La solución que funcionó para mí es:

    > ALTER SEQUENCE seq RESTART WITH 1;
    > UPDATE foo SET id = DEFAULT;
    • Mismo problema aquí. Su solución funciona para PostgreSQL 8.3.10 así.
  5. 12

    Sólo para simplificar y clarificar el uso adecuado de los ALTERAR la SECUENCIA de y SELECCIONE setval aparecerán para el restablecimiento de la secuencia:

    ALTER SEQUENCE sequence_name RESTART WITH 1;

    es equivalente a

    SELECT setval('sequence_name', 1, FALSE);

    Cualquiera de las afirmaciones pueden ser utilizados para restaurar la secuencia y usted puede obtener el siguiente valor por nextval(‘sequence_name’) como se indica aquí también:

    nextval('sequence_name')
    • Gracias Ali. Acabo de notar es crucial para establecer que el 3er parámetro a false con el setval aparecerán función
  6. 8

    La mejor manera de restablecer una secuencia a empezar de nuevo con el número 1 es ejecutar el siguiente:

    ALTER SEQUENCE <tablename>_<id>_seq RESTART WITH 1

    Así, por ejemplo, para la tabla de usuarios sería:

    ALTER SEQUENCE users_id_seq RESTART WITH 1
  7. 4

    FYI: Si necesita especificar un nuevo startvalue entre un rango de IDs (256 – 10000000 por ejemplo):

    SELECT setval('"Sequence_Name"', 
           (SELECT coalesce(MAX("ID"),255) 
               FROM "Table_Name" 
               WHERE "ID" < 10000000 and "ID" >= 256)+1
           ); 
  8. 3

    Para conservar el orden de las filas:

    UPDATE thetable SET rowid=col_serial FROM 
    (SELECT rowid, row_number() OVER ( ORDER BY lngid) AS col_serial FROM thetable ORDER BY lngid) AS t1 
    WHERE thetable.rowid=t1.rowid;
  9. 1

    Sólo el restablecimiento de la secuencia y la actualización de todas las filas pueden provocar duplicado de errores de identificación. En muchos casos, usted tendrá que actualizar todas las filas dos veces. Primero con mayor identificadores para evitar los duplicados, a continuación, con los identificadores de que realmente quieres.

    Por favor, evite agregar una cantidad fija para todos los identificadores (como se recomienda en otros comentarios). ¿Qué sucede si usted tiene más filas que esta cantidad fija? Suponiendo que el siguiente valor de la secuencia es mayor que todos los identificadores de las filas existentes (sólo desea llenar las lagunas), yo lo haría así:

    UPDATE table SET id = DEFAULT;
    ALTER SEQUENCE seq RESTART;
    UPDATE table SET id = DEFAULT;
  10. 1

    En mi caso, he conseguido con esto:

    ALTER SEQUENCE table_tabl_id_seq RESTART WITH 6;

    Donde mi tabla se llama tabla

    • Gracias por incluir un ejemplo concreto con el nombre de la tabla. Las otras respuestas fueron un poco demasiado ambiguo.
  11. 0

    Inspirado por las otras respuestas aquí, he creado una función SQL para hacer una secuencia de migración. La función se mueve una clave principal de la secuencia a una nueva secuencia contigua a partir de cualquier valor (>= 1), ya sea dentro o fuera de la secuencia existente gama.

    Explico aquí cómo puedo utilizar esta función en una migración de dos bases de datos con el mismo esquema pero con diferentes valores en una base de datos.

    Primer lugar, la función que imprime el SQL generado comandos, de forma que es
    claro que lo que realmente está sucediendo):

    CREATE OR REPLACE FUNCTION migrate_pkey_sequence
      ( arg_table      text
      , arg_column     text
      , arg_sequence   text
      , arg_next_value bigint  -- Must be >= 1
      )
    RETURNS int AS $$
    DECLARE
      result int;
      curr_value bigint = arg_next_value - 1;
      update_column1 text := format
        ( 'UPDATE %I SET %I = nextval(%L) + %s'
        , arg_table
        , arg_column
        , arg_sequence
        , curr_value
        );
      alter_sequence text := format
        ( 'ALTER SEQUENCE %I RESTART WITH %s'
        , arg_sequence
        , arg_next_value
        );
      update_column2 text := format
        ( 'UPDATE %I SET %I = DEFAULT'
        , arg_table
        , arg_column
        );
      select_max_column text := format
        ( 'SELECT coalesce(max(%I), %s) + 1 AS nextval FROM %I'
        , arg_column
        , curr_value
        , arg_table
        );
    BEGIN
      -- Print the SQL command before executing it.
      RAISE INFO '%', update_column1;
      EXECUTE update_column1;
      RAISE INFO '%', alter_sequence;
      EXECUTE alter_sequence;
      RAISE INFO '%', update_column2;
      EXECUTE update_column2;
      EXECUTE select_max_column INTO result;
      RETURN result;
    END $$ LANGUAGE plpgsql;

    La función migrate_pkey_sequence toma los siguientes argumentos:

    1. arg_table: nombre de la tabla (por ejemplo,'example')
    2. arg_column: clave primaria nombre de la columna (por ejemplo,'id')
    3. arg_sequence: nombre de la secuencia (por ejemplo,'example_id_seq')
    4. arg_next_value: siguiente valor para la columna después de la migración

    Realiza las siguientes operaciones:

    1. Mover los valores de clave principal para un intervalo libre. Supongo que
      nextval('example_id_seq') sigue max(id) y que se inicia la secuencia
      con 1. Esto también se encarga del caso donde arg_next_value > max(id).
    2. Mover los valores de clave principal para el rango contiguo de partida con
      arg_next_value. El orden de los valores de la clave se conservan, pero los agujeros en la
      rango no se conservan.
    3. Imprimir el siguiente valor que le sigue en la secuencia. Esto es útil si
      desea migrar las columnas de otra tabla y en combinación con éste.

    Para demostrar, utilizamos una secuencia y una tabla definida de la siguiente manera (por ejemplo, el uso de psql):

    # CREATE SEQUENCE example_id_seq
      START WITH 1
      INCREMENT BY 1
      NO MINVALUE
      NO MAXVALUE
      CACHE 1;
    # CREATE TABLE example
      ( id bigint NOT NULL DEFAULT nextval('example_id_seq'::regclass)
      );

    A continuación, insertamos algunos valores (a partir de, por ejemplo, a las 3):

    # ALTER SEQUENCE example_id_seq RESTART WITH 3;
    # INSERT INTO example VALUES (DEFAULT), (DEFAULT), (DEFAULT);
    -- id: 3, 4, 5

    Finalmente, nos migrar el example.id valores empezar con 1.

    # SELECT migrate_pkey_sequence('example', 'id', 'example_id_seq', 1);
    INFO:  00000: UPDATE example SET id = nextval('example_id_seq') + 0
    INFO:  00000: ALTER SEQUENCE example_id_seq RESTART WITH 1
    INFO:  00000: UPDATE example SET id = DEFAULT
     migrate_pkey_sequence
    -----------------------
                         4
    (1 row)

    El resultado:

    # SELECT * FROM example;
     id
    ----
      1
      2
      3
    (3 rows)
  12. 0

    Incluso el incremento automático de la columna no es PK ( en este ejemplo se llama seq – aka secuencia ) podría lograrlo con un desencadenador :

    DROP TABLE IF EXISTS devops_guide en CASCADA;

    SELECT 'create the "devops_guide" table'
    ;
       CREATE TABLE devops_guide (
          guid           UUID NOT NULL DEFAULT gen_random_uuid()
        , level          integer NULL
        , seq            integer NOT NULL DEFAULT 1
        , name           varchar (200) NOT NULL DEFAULT 'name ...'
        , description    text NULL
        , CONSTRAINT pk_devops_guide_guid PRIMARY KEY (guid)
        ) WITH (
          OIDS=FALSE
        );
    
    -- START trg_devops_guide_set_all_seq
    CREATE OR REPLACE FUNCTION fnc_devops_guide_set_all_seq()
        RETURNS TRIGGER
        AS $$
           BEGIN
             UPDATE devops_guide SET seq=col_serial FROM
             (SELECT guid, row_number() OVER ( ORDER BY seq) AS col_serial FROM devops_guide ORDER BY seq) AS tmp_devops_guide
             WHERE devops_guide.guid=tmp_devops_guide.guid;
    
             RETURN NEW;
           END;
        $$ LANGUAGE plpgsql;
    
     CREATE TRIGGER trg_devops_guide_set_all_seq
      AFTER UPDATE OR DELETE ON devops_guide
      FOR EACH ROW
      WHEN (pg_trigger_depth() < 1)
      EXECUTE PROCEDURE fnc_devops_guide_set_all_seq();
  13. -1

    Si usted está usando pgAdmin3, expanda ‘Secuencias’ clic derecho en una secuencia, ir a «Propiedades» y en la «Definición» ficha de cambio «valor Actual» para cualquier valor que desee. No hay necesidad de una consulta.

    • Su respuesta no agrega ningún valor si no, al menos, nos dicen qué herramienta que está utilizando.
    • Esta es la manera más fácil, obviamente, yo creo que él está diciendo pg admin 3

Dejar respuesta

Please enter your comment!
Please enter your name here