Me encontré con el problema de que mi clave principal de la secuencia no está en sintonía con mi filas de la tabla.

Que es, al insertar una nueva fila obtengo un error de clave duplicada debido a que la secuencia implícita en la serie de tipo de datos devuelve un número que ya existe.

Parece ser causado por la importación/restaura no mantener la secuencia correctamente.

  • Tengo curiosidad.. se te caiga la db antes de hacer una restauración? Tengo un leve recuerdo de que esto suceda, pero podría estar equivocado 😛
  • El PostgreSQL wiki tiene una página en Fijación de Secuencias.
  • Sólo para ayudar a googleability, el mensaje de error arrojado aquí es: «el valor de clave duplicada viola la restricción unique …»
  • Esta es la forma en sqlsequencereset en Django hace : SELECCIONE setval aparecerán(pg_get_serial_sequence(«<table_name>»,’id’), se unen(max(«id»), 1), max(«id») ES NO nulo) DE «<table_name>»;
  • La primera instancia de la <nombre de la tabla> debe ser envuelto en comillas simples para el pg_get_serioal_sequence función: SELECCIONE setval aparecerán(pg_get_serial_sequence(‘<table_name>’,’id’), se unen(max(«id»), 1), max(«id») ES NO nulo) DE «<table_name>»
InformationsquelleAutor meleyal | 2008-10-28

27 Comentarios

  1. 655
    -- Login to psql and run the following
    
    -- What is the result?
    SELECT MAX(id) FROM your_table;
    
    -- Then run...
    -- This should be higher than the last result.
    SELECT nextval('your_table_id_seq');
    
    -- If it's not higher... run this set the sequence last to your highest id. 
    -- (wise to run a quick pg_dump first...)
    
    BEGIN;
    -- protect against concurrent inserts while you update the counter
    LOCK TABLE your_table IN EXCLUSIVE MODE;
    -- Update the sequence
    SELECT setval('your_table_id_seq', COALESCE((SELECT MAX(id)+1 FROM your_table), 1), false);
    COMMIT;

    Fuente – Ruby En El Foro De

    • Gracias, phpPgAdmin tiene bajo una pestaña llamada ‘Índices’ así que pensé que este era el derecho a la denominación. Así que supongo que ‘secuencia’ es propiedad de el ‘índice’ que se actualiza?
    • La secuencia es sólo un valor especial que se incrementa/conjunto con nextval() y setval aparecerán funciones (). Se utiliza principalmente para crear columnas que se incrementa automáticamente, usted tiene una clave primaria que tiene como valor por defecto ‘nextval(‘primary_key_name_seq’) y por tanto, cada vez que se inserta …
    • … una nueva fila, que es el valor obtenido de la secuencia. Los índices son totalmente diferentes de la bestia, que también se crean automáticamente para las claves primarias, pero no tienen nada que ver con que es el siguiente valor va a ser. No he visto phpPgAdmin en las edades, pero tiendo a pensar que los que se muestran en la pestaña …
    • … real de los índices, así como las secuencias, por ejemplo.
    • En cualquier caso, la adición de 1 a MAX(id) dejará un único número de brecha en sus IDs, ya que lo que setval aparecerán conjuntos es el último valor de la secuencia, no el siguiente.
    • Su ejemplo no funcionará si no hay ninguna fila en la tabla. Así que ahí SQL dada en la parte inferior es más seguro: SELECCIONE setval aparecerán(‘your_table_id_seq’, se unen((select max(id)+1 de your_table), 1), true);
    • Pero con el fin de evitar lagunas mencionado por @mikl dos comentarios de arriba, usted necesita SELECT setval('your_table_id_seq', coalesce((select max(id)+1 from your_table), 1), false);
    • no funciona 🙁
    • Hacer huecos en realidad alguna diferencia?
    • Todos los problemas resueltos y se combinan en una sola consulta: SELECT setval('your_seq',(SELECT GREATEST(MAX(your_id)+1,nextval('your_seq'))-1 FROM your_table))
    • su consulta no funciona para mí desde el nextval ya era demasiado grande. El uno publicado por Antonio Hatchkins funcionado mejor para mí
    • No tienes idea de lo mucho que esto me ayudó ! Santa madre de DIOS !
    • Si su aplicación se preocupa por deficiencias en las secuencias, su aplicación está roto. Las brechas en las secuencias son normales, y puede ocurrir debido a un imprevisto de la base de datos apagados, reversiones de transacción después de los errores, etc.
    • La consulta enviada por @Frunsi funciona con un par de ajustes: SELECT setval(pg_get_serial_sequence('table_name', 'id'), COALESCE(MAX(id), 1), MAX(id) IS NOT null) FROM table_name (nota uso de comillas)
    • Por favor alguien puede proporcionar un poco de color sobre cómo obtener «your_table_id_seq» en Postgres?
    • Seguimiento sobre cómo obtener el nombre de la secuencia de la tabla: postgresql.org/message-id/[email protected] SELECT table_name, column_name, column_default from information_schema.columns where table_name='your_table';
    • me salvó el día. gracias.
    • depende del argumento booleano (deafults a true). Si false, a continuación, se establece el siguiente valor, si true a continuación establece la actual.

  2. 186

    pg_get_serial_sequence puede ser utilizado para evitar cualquier suposiciones incorrectas sobre el nombre de la secuencia. De esta manera se restablece la secuencia en una sola toma:

    SELECT pg_catalog.setval(pg_get_serial_sequence('table_name', 'id'), (SELECT MAX(id) FROM table_name)+1);

    O más concisamente:

    SELECT pg_catalog.setval(pg_get_serial_sequence('table_name', 'id'), MAX(id)) FROM table_name;

    Sin embargo esta forma no puede manejar las mesas vacías correctamente, ya que max(id) es nulo, y tampoco puede setval aparecerán 0 porque estaría fuera del rango de la secuencia. Una solución es recurrir a la ALTER SEQUENCE sintaxis es decir,

    ALTER SEQUENCE table_name_id_seq RESTART WITH 1;
    ALTER SEQUENCE table_name_id_seq RESTART; -- 8.4 or higher

    Pero ALTER SEQUENCE es de uso limitado debido a que el nombre de la secuencia de reinicio y de valor no pueden ser expresiones.

    Parece la mejor solución a todos los problemas es llamar setval con falsas como el 3er parámetro, lo que nos permite especificar el «siguiente valor de uso»:

    SELECT setval(pg_get_serial_sequence('t1', 'id'), coalesce(max(id),0) + 1, false) FROM t1;

    Este cumple todos mis cuadros:

    1. evita codificar la secuencia real nombre
    2. manijas de las mesas vacías correctamente
    3. maneja las tablas con los datos existentes, y no deja
      agujero en la secuencia

    Por último, tenga en cuenta que pg_get_serial_sequence sólo funciona si la secuencia es de titularidad de la columna. Este será el caso si el incremento de la columna se define como un serial tipo, sin embargo, si la secuencia se ha agregado manualmente es necesario garantizar ALTER SEQUENCE .. OWNED BY también se realiza.

    es decir, si serial tipo fue utilizado para la creación de la tabla, esto debe todo el trabajo:

    CREATE TABLE t1 (
      id serial,
      name varchar(20)
    );
    
    SELECT pg_get_serial_sequence('t1', 'id'); -- returns 't1_id_seq'
    
    -- reset the sequence, regardless whether table has rows or not:
    SELECT setval(pg_get_serial_sequence('t1', 'id'), coalesce(max(id),0) + 1, false) FROM t1;

    Pero si las secuencias se agregaron manualmente:

    CREATE TABLE t2 (
      id integer NOT NULL,
      name varchar(20)
    );
    
    CREATE SEQUENCE t2_custom_id_seq
        START WITH 1
        INCREMENT BY 1
        NO MINVALUE
        NO MAXVALUE
        CACHE 1;
    
    ALTER TABLE t2 ALTER COLUMN id SET DEFAULT nextval('t2_custom_id_seq'::regclass);
    
    ALTER SEQUENCE t2_custom_id_seq OWNED BY t2.id; -- required for pg_get_serial_sequence
    
    SELECT pg_get_serial_sequence('t2', 'id'); -- returns 't2_custom_id_seq'
    
    -- reset the sequence, regardless whether table has rows or not:
    SELECT setval(pg_get_serial_sequence('t2', 'id'), coalesce(max(id),0) + 1, false) FROM t1;
    • No hay ninguna necesidad en el ‘+1’ en la consulta, setval() conjuntos de valor actual, y nextval() ya de retorno valor actual +1.
    • La función de envolver este método que toma un parámetro – table_name – está en mi respuesta a continuación: stackoverflow.com/a/13308052/237105
    • saludos. Acabo de ver otra repetición de la +1 error, para finalmente aplastado que para el bien espero
  3. 78

    La más corta y más rápida manera:

    SELECT setval('tbl_tbl_id_seq', max(tbl_id)) FROM tbl;

    tbl_id ser el serial columna de la tabla tbl, a partir de la secuencia de tbl_tbl_id_seq (que es el valor predeterminado de nombre automático).

    Si usted no sabe el nombre de la secuencia (que no tiene que ser en forma predeterminada), el uso de pg_get_serial_sequence():

    SELECT setval(pg_get_serial_sequence('tbl', 'tbl_id'), max(tbl_id)) FROM tbl;

    Hay no fuera por un error aquí. Por la documentación:

    Dos parámetros formulario, se establece la secuencia del last_value campo a la
    valor especificado y establece su is_called campo a true, lo que significa que el
    siguiente nextval avance en la secuencia de antes de devolver un valor.

    Negrita el énfasis es mío.

    Si la tabla puede estar vacía y para empezar a partir del 1 de en este caso:

    SELECT setval(pg_get_serial_sequence('tbl', 'tbl_id')
                , COALESCE(max(tbl_id) + 1, 1)
                , false)
    FROM tbl;

    No podemos usar las 2-paremater formulario y comenzar con 0 debido a que el límite inferior de las secuencias es 1 por defecto (a menos personalizado).

    Concurrencia

    No hay defensa contra concurrente de la secuencia de la actividad o escribe a la mesa en las consultas anteriores, sin embargo. Si eso es relevante, puede de bloqueo de la mesa en modo exclusivo. Mantiene transacciones simultáneas de la escritura de un número más alto, mientras que usted está tratando de conseguir en la sincronización. (También bloquea temporalmente inofensivo escribe no meterse con el máximo número).

    Pero no llevan a los clientes en la cuenta de que puede haber traído la secuencia de números por adelantado sin ningún bloqueo en la mesa principal, sin embargo (lo que puede suceder). Para permitir que, también, sólo que aumentar el valor actual de la secuencia, nunca disminuyen. Puede parecer paranoico, pero que en acuerdo con la naturaleza de las secuencias y la defensa contra los problemas de concurrencia.

    BEGIN;
    
    LOCK TABLE tbl IN EXCLUSIVE MODE;
    
    SELECT setval('tbl_tbl_id_seq', max(tbl_id))
    FROM   tbl
    HAVING max(tbl_id) > (SELECT last_value FROM tbl_tbl_id_seq);
    
    COMMIT;
    • La segunda consulta trabajó como un encanto para mí!
    • Donde el «ESTÁNDAR de la comunidad de la biblioteca de funciones esenciales»? La segunda cláusula select de esta respuesta en un EXECUTE format() (como @EB.’s) es una función esencial! ¿Cómo solucionar este la falta de la biblioteca estándar en PostgreSQL????
    • No asunto si hay un off-by-one. Las brechas en las secuencias son normales. Si la aplicación no puede hacer frente, su aplicación está roto, debido a diferencias pueden deberse también a reversiones de transacción, no planificado paradas de servidor, etc.
    • El off-by-one error me dirigí a (y no hay) sería relevante ya que había riesgo de un error de clave duplicada de otra manera. La dirección opuesta a la de sus consideraciones; parece que un malentendido.
    • ah, tiene sentido.
  4. 50

    Esto restablecerá todas las secuencias de público sin hacer suposiciones acerca de los nombres de tabla o columna. Probado en la versión 8.4

    CREATE OR REPLACE FUNCTION "reset_sequence" (tablename text, columnname text, sequence_name text) RETURNS "pg_catalog"."void" AS 
    
        $body$  
          DECLARE 
          BEGIN 
    
          EXECUTE 'SELECT setval( ''' || sequence_name  || ''', ' || '(SELECT MAX(' || columnname || ') FROM ' || tablename || ')' || '+1)';
    
    
    
          END;  
    
        $body$  LANGUAGE 'plpgsql';
    
    
        select table_name || '_' || column_name || '_seq', reset_sequence(table_name, column_name, table_name || '_' || column_name || '_seq') from information_schema.columns where column_default like 'nextval%';
    • +1 función muy útil! Nuestra secuencia de nombres no coinciden con los nombres de la tabla, exactamente, lo que he usado substring(column_default, '''(.*)''') en lugar de table_name || '_' || column_name || '_seq'. Funciona a la perfección.
    • +1 exactamente lo que estaba buscando. Gracias.
    • Tenga en cuenta que esto se producirá con la secuencia de nombres que contiene comillas simples o los nombres de tabla con mayúsculas, espacios, etc) en su nombre. El quote_literal y quote_ident funciones, o, preferentemente, de la format función, realmente debe ser utilizado aquí.
    • Ojalá pudiera dar más de un voto…muy bien hecho señor. Funciona muy bien en Postgres 9.1 así, al menos para mí.
    • Esto es genial. He utilizado substring(column_default from 'nextval\(''(.+)''::regclass\)') explícitamente agarrar el nombre de la secuencia. Trabajó como un encanto.
  5. 38

    ALTERAR la SECUENCIA de sequence_name REINICIAR CON (SELECT max(id) FROM nombre_tabla);
    No funciona.

    Copiado de @tardate respuesta:

    SELECT setval(pg_get_serial_sequence('table_name', 'id'), MAX(id)) FROM table_name;
    • eso es un error de sintaxis para mí en 8.4 (a ^(SELECT… ). REINICIAR parece que sólo acepta un valor ordinal. Esto funciona a pesar de que: SELECCIONE setval aparecerán(pg_get_serial_sequence(‘nombre_de_tabla’, ‘id’), (SELECT MAX(id) FROM nombre_tabla)+1);
    • perfecto, gracias!
    • Muruges la solución no funciona en 9.4 bien. No entiendo por qué tanto upvotes en esta respuesta. ALTERAR la SECUENCIA de no permitir que las subconsultas. Solución por @tardate funciona a la perfección. Editado respuesta para eliminar datos incorrectos.
    • ALTERAR la SECUENCIA funcionaba perfecto para mí. Tuve utiliza COPIA a traer algunos datos y había huecos en las claves principales y del INSERTO estaban tirando de clave duplicada excepciones. Configuración de la secuencia hizo el truco. 9.4
  6. 19

    Este comando sólo para el cambio automático de la clave generada secuencia de valor en postgresql

    ALTER SEQUENCE "your_sequence_name" RESTART WITH 0;

    En lugar de cero se puede poner cualquier número desde el que desea reiniciar la secuencia.

    secuencia predeterminada nombre "TableName_FieldName_seq". Por ejemplo, si su nombre de la tabla es "MyTable" y su nombre de campo es "MyID", entonces su nombre de la secuencia será "MyTable_MyID_seq".

    Esta es la respuesta es la misma que @murugesanponappan la respuesta, pero hay un error de sintaxis en su solución. no se puede utilizar una consulta de sub (select max()...) en alter comando. Así que usted tiene que usar fija el valor numérico o es necesario utilizar una variable en lugar de la sub consulta.

    • Esta es la solución perfecta muchas gracias señor. Pero en mi caso he tenido un error, así que he tenido que cambiar para ALTERAR la SECUENCIA de «your_sequence_name» REINICIAR CON 1;
  7. 16

    Restablecer todas las secuencias, no suposiciones acerca de los nombres, excepto que la clave principal de cada tabla es «id»:

    CREATE OR REPLACE FUNCTION "reset_sequence" (tablename text, columnname text)
    RETURNS "pg_catalog"."void" AS
    $body$
    DECLARE
    BEGIN
        EXECUTE 'SELECT setval( pg_get_serial_sequence(''' || tablename || ''', ''' || columnname || '''),
        (SELECT COALESCE(MAX(id)+1,1) FROM ' || tablename || '), false)';
    END;
    $body$  LANGUAGE 'plpgsql';
    
    select table_name || '_' || column_name || '_seq', reset_sequence(table_name, column_name) from information_schema.columns where column_default like 'nextval%';
    • Funcionó a la perfección en mi versión 9.1
    • Usted necesita agregar cotización si la tabla contiene mayúsculas: pg_get_serial_sequence(''"' || tablename || '"''
    • Esta es la mejor función! Usted puede evitar la cita de los problemas (y realzar la elegancia) con formato, algo así como EXECUTE format( 'SELECT setval(pg_get_serial_sequence(%L, %L), coalesce(max(id),0) + 1, false) FROM %I;', $1,$2,$1 );
  8. 12

    Estas funciones son plagada de peligros cuando la secuencia de nombres, los nombres de columna, los nombres de tabla o un esquema de nombres divertidos personajes, tales como espacios, signos de puntuación, y similares. He escrito esta:

    CREATE OR REPLACE FUNCTION sequence_max_value(oid) RETURNS bigint
    VOLATILE STRICT LANGUAGE plpgsql AS  $$
    DECLARE
     tabrelid oid;
     colname name;
     r record;
     newmax bigint;
    BEGIN
     FOR tabrelid, colname IN SELECT attrelid, attname
                   FROM pg_attribute
                  WHERE (attrelid, attnum) IN (
                          SELECT adrelid::regclass,adnum
                            FROM pg_attrdef
                           WHERE oid IN (SELECT objid
                                           FROM pg_depend
                                          WHERE refobjid = $1
                                                AND classid = 'pg_attrdef'::regclass
                                        )
              ) LOOP
          FOR r IN EXECUTE 'SELECT max(' || quote_ident(colname) || ') FROM ' || tabrelid::regclass LOOP
              IF newmax IS NULL OR r.max > newmax THEN
                  newmax := r.max;
              END IF;
          END LOOP;
      END LOOP;
      RETURN newmax;
    END; $$ ;

    Usted puede llamar para una sola secuencia por la que pasa el OID y se le devolverá el mayor número utilizado por cualquier tabla que tiene la secuencia como predeterminado; o se puede ejecutar con una consulta como esta, para restablecer todas las secuencias en la base de datos:

     select relname, setval(oid, sequence_max_value(oid))
       from pg_class
      where relkind = 'S';

    Usando un tipo diferente de lo qual puede restablecer sólo la secuencia de un determinado esquema, y así sucesivamente. Por ejemplo, si desea ajustar las secuencias en el «público» esquema:

    select relname, setval(pg_class.oid, sequence_max_value(pg_class.oid))
      from pg_class, pg_namespace
     where pg_class.relnamespace = pg_namespace.oid and
           nspname = 'public' and
           relkind = 'S';

    Tenga en cuenta que debido a la forma de setval aparecerán() funciona, usted no necesita sumar 1 al resultado.

    Como nota final, tengo que advertir que algunas bases de datos parecen tener los valores predeterminados de la vinculación a las secuencias de forma que no se permita que el sistema de catálogos de tener una información completa de ellos. Esto sucede cuando se ven cosas como esta en psql s \d:

    alvherre=# \d baz
                         Tabla «public.baz»
     Columna |  Tipo   |                 Modificadores                  
    ---------+---------+------------------------------------------------
     a       | integer | default nextval(('foo_a_seq'::text)::regclass)

    Tenga en cuenta que el nextval() la llamada en que la cláusula por defecto tiene un: texto:cast además de la ::regclass emitidos. Yo pensar esto es debido a que las bases de datos pg_dump ed de viejas versiones de PostgreSQL. Lo que sucederá es que la función sequence_max_value() anterior se caso omiso de dicha tabla. Para solucionar el problema, puede volver a definir la cláusula por DEFECTO para referirse a la secuencia directamente sin el yeso:

    alvherre=# alter table baz alter a set default nextval('foo_a_seq');
    ALTER TABLE

    Luego psql muestra correctamente:

    alvherre=# \d baz
                         Tabla «public.baz»
     Columna |  Tipo   |             Modificadores              
    ---------+---------+----------------------------------------
     a       | integer | default nextval('foo_a_seq'::regclass)

    Tan pronto como usted ha fijado que, la función funciona correctamente en esta tabla, así como todos los demás que puedan utilizar la misma secuencia.

    • Esto es sorprendente, gracias! Cabe señalar que necesitaba para añadir un fundido en la asignación (línea 21 en el código de la función) como este: newmax := r.max::bigint; para hacer que funcione correctamente para mí.
    • Tenía que cambiar esta así: 'SELECT max(' || quote_ident(colname) || ') FROM ' => 'SELECT max(' || quote_ident(colname) || '::bigint) FROM ' aviso el añadido ::bigint lanzado dentro de la dinámica de construir consulta.
  9. 7

    Restablecer toda la secuencia de público

    CREATE OR REPLACE FUNCTION "reset_sequence" (tablename text) RETURNS "pg_catalog"."void" AS 
    $body$  
      DECLARE 
      BEGIN 
      EXECUTE 'SELECT setval( ''' 
      || tablename  
      || '_id_seq'', ' 
      || '(SELECT id + 1 FROM "' 
      || tablename  
      || '" ORDER BY id DESC LIMIT 1), false)';  
      END;  
    $body$  LANGUAGE 'plpgsql';
    
    select sequence_name, reset_sequence(split_part(sequence_name, '_id_seq',1)) from information_schema.sequences
            where sequence_schema='public';
    • Parece que este enfoque de hacer suposiciones acerca de la columna y nombres de tablas, así que no trabajo para mí
    • No que el daño de los datos en la base de datos?
  10. 6

    Mi versión de utilizar la primera, con algunos de comprobación de error…

    BEGIN;
    CREATE OR REPLACE FUNCTION reset_sequence(_table_schema text, _tablename text, _columnname text, _sequence_name text)
    RETURNS pg_catalog.void AS
    $BODY$
    DECLARE
    BEGIN
     PERFORM 1
     FROM information_schema.sequences
     WHERE
      sequence_schema = _table_schema AND
      sequence_name = _sequence_name;
     IF FOUND THEN
      EXECUTE 'SELECT setval( ''' || _table_schema || '.' || _sequence_name  || ''', ' || '(SELECT MAX(' || _columnname || ') FROM ' || _table_schema || '.' || _tablename || ')' || '+1)';
     ELSE
      RAISE WARNING 'SEQUENCE NOT UPDATED ON %.%', _tablename, _columnname;
     END IF;
    END; 
    $BODY$
     LANGUAGE 'plpgsql';
    
    SELECT reset_sequence(table_schema, table_name, column_name, table_name || '_' || column_name || '_seq')
    FROM information_schema.columns
    WHERE column_default LIKE 'nextval%';
    
    DROP FUNCTION reset_sequence(_table_schema text, _tablename text, _columnname text, _sequence_name text) ;
    COMMIT;
    • Gracias por la comprobación de errores! Muy apreciado de la tabla/columna de nombres de obtener truncar si son demasiado largas, lo que a su RAISE WARNING identificados para mí.
  11. 6

    Algunos realmente hardcore respuestas aquí, yo estoy suponiendo que solía ser muy mala en todo el tiempo cuando esto se ha hecho, ya que una gran cantidad de respuestas a partir de aquí no funciona para la versión 9.3. El documentación desde la versión 8.0 proporciona una respuesta a esta pregunta:

    SELECT setval('serial', max(id)) FROM distributors;

    También, si usted necesita tomar el cuidado de las mayúsculas de las minúsculas secuencia de nombres, que es la manera de hacerlo:

    SELECT setval('"Serial"', max(id)) FROM distributors;
    • Muy concisa y precisa solución. gracias
  12. 6

    Sugiero esta solución se encuentra en postgres wiki. Actualiza todas las secuencias de las tablas.

    SELECT 'SELECT SETVAL(' ||
           quote_literal(quote_ident(PGT.schemaname) || '.' || quote_ident(S.relname)) ||
           ', COALESCE(MAX(' ||quote_ident(C.attname)|| '), 1) ) FROM ' ||
           quote_ident(PGT.schemaname)|| '.'||quote_ident(T.relname)|| ';'
    FROM pg_class AS S,
         pg_depend AS D,
         pg_class AS T,
         pg_attribute AS C,
         pg_tables AS PGT
    WHERE S.relkind = 'S'
        AND S.oid = D.objid
        AND D.refobjid = T.oid
        AND D.refobjid = C.attrelid
        AND D.refobjsubid = C.attnum
        AND T.relname = PGT.tablename
    ORDER BY S.relname;

    Cómo utilizar(de postgres wiki):

    • Guardar esto en un archivo, decir ‘reset.sql’
    • Ejecutar el archivo y guardar los resultados en una forma que no incluya el encabezado usual, a continuación, ejecute esa salida. Ejemplo:

    Ejemplo:

    psql -Atq -f reset.sql -o temp
    psql -f temp
    rm temp

    Artículo Original(también con el fix para la secuencia de la propiedad) aquí

  13. 6

    Otro plpgsql – reinicia solo si max(att) > then lastval

    do --check seq not in sync
    $$
    declare
     _r record;
     _i bigint;
     _m bigint;
    begin
      for _r in (
        SELECT relname,nspname,d.refobjid::regclass, a.attname, refobjid
        FROM   pg_depend    d
        JOIN   pg_attribute a ON a.attrelid = d.refobjid AND a.attnum = d.refobjsubid
        JOIN pg_class r on r.oid = objid
        JOIN pg_namespace n on n.oid = relnamespace
        WHERE  d.refobjsubid > 0 and  relkind = 'S'
       ) loop
        execute format('select last_value from %I.%I',_r.nspname,_r.relname) into _i;
        execute format('select max(%I) from %s',_r.attname,_r.refobjid) into _m;
        if coalesce(_m,0) > _i then
          raise info '%',concat('changed: ',_r.nspname,'.',_r.relname,' from:',_i,' to:',_m);
          execute format('alter sequence %I.%I restart with %s',_r.nspname,_r.relname,_m+1);
        end if;
      end loop;
    
    end;
    $$
    ;

    también comentar la línea --execute format('alter sequence dará la lista, en realidad no restablecer el valor

  14. 6

    Este problema pasa conmigo cuando el uso de entity framework para crear la base de datos y, a continuación, la semilla de la base de datos con los datos iniciales, esto hace que la secuencia de desajuste.

    Lo Resuelto por la Creación de un script para que se ejecute después de la siembra de la base de datos:

    DO
    $do$
    DECLARE tablename text;
    BEGIN
        -- change the where statments to include or exclude whatever tables you need
        FOR tablename IN SELECT table_name FROM information_schema.tables WHERE table_schema='public' AND table_type='BASE TABLE' AND table_name != '__EFMigrationsHistory'
            LOOP
                EXECUTE format('SELECT setval(pg_get_serial_sequence(''"%s"'', ''Id''), (SELECT MAX("Id") + 1 from "%s"))', tablename, tablename);
        END LOOP;
    END
    $do$
    • ¿por qué el MAX("Id") + 1 funciona mejor para mí cuando la secuencia es = a la de max.
  15. 5

    Poniendo todo junto

    CREATE OR REPLACE FUNCTION "reset_sequence" (tablename text) 
    RETURNS "pg_catalog"."void" AS
    $body$
    DECLARE
    BEGIN
      EXECUTE 'SELECT setval( pg_get_serial_sequence(''' || tablename || ''', ''id''),
      (SELECT COALESCE(MAX(id)+1,1) FROM ' || tablename || '), false)';
    END;
    $body$  LANGUAGE 'plpgsql';

    va a arreglar » id' secuencia de la tabla de la base (como suele ser necesario con django, por ejemplo).

  16. 4

    antes de que yo no había probado aún el código : en el siguiente post me
    la versión de sql-código para Klaus y user457226 soluciones
    que trabajó en mi pc [Postgres 8.3], con sólo unos pequeños ajustes
    para el Klaus uno de ellos y de mi versión de la user457226 uno.

    Klaus solución :

    drop function IF EXISTS rebuilt_sequences() RESTRICT;
    CREATE OR REPLACE FUNCTION  rebuilt_sequences() RETURNS integer as
    $body$
      DECLARE sequencedefs RECORD; c integer ;
      BEGIN
        FOR sequencedefs IN Select
          constraint_column_usage.table_name as tablename,
          constraint_column_usage.table_name as tablename, 
          constraint_column_usage.column_name as columnname,
          replace(replace(columns.column_default,'''::regclass)',''),'nextval(''','') as sequencename
          from information_schema.constraint_column_usage, information_schema.columns
          where constraint_column_usage.table_schema ='public' AND 
          columns.table_schema = 'public' AND columns.table_name=constraint_column_usage.table_name
          AND constraint_column_usage.column_name = columns.column_name
          AND columns.column_default is not null
       LOOP    
          EXECUTE 'select max('||sequencedefs.columnname||') from ' || sequencedefs.tablename INTO c;
          IF c is null THEN c = 0; END IF;
          IF c is not null THEN c = c+ 1; END IF;
          EXECUTE 'alter sequence ' || sequencedefs.sequencename ||' restart  with ' || c;
       END LOOP;
    
       RETURN 1; END;
    $body$ LANGUAGE plpgsql;
    
    select rebuilt_sequences();

    user457226 solución :

    --drop function IF EXISTS reset_sequence (text,text) RESTRICT;
    CREATE OR REPLACE FUNCTION "reset_sequence" (tablename text,columnname text) RETURNS bigint --"pg_catalog"."void"
    AS
    $body$
      DECLARE seqname character varying;
              c integer;
      BEGIN
        select tablename || '_' || columnname || '_seq' into seqname;
        EXECUTE 'SELECT max("' || columnname || '") FROM "' || tablename || '"' into c;
        if c is null then c = 0; end if;
        c = c+1; --because of substitution of setval with "alter sequence"
        --EXECUTE 'SELECT setval( "' || seqname || '", ' || cast(c as character varying) || ', false)'; DOES NOT WORK!!!
        EXECUTE 'alter sequence ' || seqname ||' restart with ' || cast(c as character varying);
        RETURN nextval(seqname)-1;
      END;
    $body$ LANGUAGE 'plpgsql';
    
    select sequence_name, PG_CLASS.relname, PG_ATTRIBUTE.attname,
           reset_sequence(PG_CLASS.relname,PG_ATTRIBUTE.attname)
    from PG_CLASS
    join PG_ATTRIBUTE on PG_ATTRIBUTE.attrelid = PG_CLASS.oid
    join information_schema.sequences
         on information_schema.sequences.sequence_name = PG_CLASS.relname || '_' || PG_ATTRIBUTE.attname || '_seq'
    where sequence_schema='public';
  17. 4

    Revisar de nuevo toda la secuencia en el esquema público de la función

    CREATE OR REPLACE FUNCTION public.recheck_sequence (
    )
    RETURNS void AS
    $body$
    DECLARE
      _table_name VARCHAR;
      _column_name VARCHAR;  
      _sequence_name VARCHAR;
    BEGIN
      FOR _table_name IN SELECT tablename FROM pg_catalog.pg_tables WHERE schemaname = 'public' LOOP
        FOR _column_name IN SELECT column_name FROM information_schema.columns WHERE table_name = _table_name LOOP
            SELECT pg_get_serial_sequence(_table_name, _column_name) INTO _sequence_name;
            IF _sequence_name IS NOT NULL THEN 
                EXECUTE 'SELECT setval('''||_sequence_name||''', COALESCE((SELECT MAX('||quote_ident(_column_name)||')+1 FROM '||quote_ident(_table_name)||'), 1), FALSE);';
            END IF;
        END LOOP;   
      END LOOP;
    END;
    $body$
    LANGUAGE 'plpgsql'
    VOLATILE
    CALLED ON NULL INPUT
    SECURITY INVOKER
    COST 100;
  18. 3

    Para reiniciar todos secuencia 1 uso:

    -- Create Function
    CREATE OR REPLACE FUNCTION "sy_restart_seq_to_1" (
        relname TEXT
    )
    RETURNS "pg_catalog"."void" AS
    $BODY$
    
    DECLARE
    
    BEGIN
        EXECUTE 'ALTER SEQUENCE '||relname||' RESTART WITH 1;';
    END;
    $BODY$
    
    LANGUAGE 'plpgsql';
    
    -- Use Function
    SELECT 
        relname
        ,sy_restart_seq_to_1(relname)
    FROM pg_class
    WHERE relkind = 'S';
  19. 2

    Si ve este mensaje de error cuando se carga SQL personalizada de los datos para la inicialización, otra manera de evitar esto es:

    Lugar de la escritura:

    INSERT INTO book (id, name, price) VALUES (1 , 'Alchemist' , 10),

    Quitar el id (clave principal) a partir de datos iniciales

    INSERT INTO book (name, price) VALUES ('Alchemist' , 10),

    Esto evita que el usuario Postgres de la secuencia de sincronización !

  20. 2

    Esta respuesta es una copia de mauro.

    drop function IF EXISTS rebuilt_sequences() RESTRICT;
    CREATE OR REPLACE FUNCTION  rebuilt_sequences() RETURNS integer as
    $body$
      DECLARE sequencedefs RECORD; c integer ;
      BEGIN
        FOR sequencedefs IN Select
          DISTINCT(constraint_column_usage.table_name) as tablename,
          constraint_column_usage.column_name as columnname,
          replace(replace(columns.column_default,'''::regclass)',''),'nextval(''','') as sequencename
          from information_schema.constraint_column_usage, information_schema.columns
          where constraint_column_usage.table_schema ='public' AND 
          columns.table_schema = 'public' AND columns.table_name=constraint_column_usage.table_name
          AND constraint_column_usage.column_name = columns.column_name
          AND columns.column_default is not null 
          ORDER BY sequencename
       LOOP    
          EXECUTE 'select max('||sequencedefs.columnname||') from ' || sequencedefs.tablename INTO c;
          IF c is null THEN c = 0; END IF;
          IF c is not null THEN c = c+ 1; END IF;
          EXECUTE 'alter sequence ' || sequencedefs.sequencename ||' minvalue '||c ||' start ' || c ||' restart  with ' || c;
       END LOOP;
    
       RETURN 1; END;
    $body$ LANGUAGE plpgsql;
    
    select rebuilt_sequences();
  21. 2

    Me pasé una hora tratando de conseguir djsnowsill la respuesta a trabajar con una base de datos con una mezcla de Caso de tablas y columnas, a continuación, finalmente tropezó con la solución gracias a un comentario de Manuel Darveau, pero pensé que podría hacer un poco más claro para todo el mundo:

    CREATE OR REPLACE FUNCTION "reset_sequence" (tablename text, columnname text)
    RETURNS "pg_catalog"."void" AS
    $body$
    DECLARE
    BEGIN
    EXECUTE format('SELECT setval(pg_get_serial_sequence(''%1$I'', %2$L),
            (SELECT COALESCE(MAX(%2$I)+1,1) FROM %1$I), false)',tablename,columnname);
    END;
    $body$  LANGUAGE 'plpgsql';
    
    SELECT format('%s_%s_seq',table_name,column_name), reset_sequence(table_name,column_name) 
    FROM information_schema.columns WHERE column_default like 'nextval%';

    Esto tiene la ventaja de:

    • no asumiendo la columna de ID está escrito de una manera particular.
    • no asumiendo todas las tablas tienen una secuencia.
    • de trabajo Mixto en el Caso de la tabla/columna de nombres.
    • utilizando el formato para ser más conciso.

    A explicar, el problema era que pg_get_serial_sequence lleva cadenas a trabajar de lo que usted se refiere, así que si usted:

    "TableName" --it thinks it's a table or column
    'TableName' --it thinks it's a string, but makes it lower case
    '"TableName"' --it works!

    Esto se logra mediante ''%1$I'' en la cadena de formato, '' hace un apóstrofo 1$ significa, en primer arg, y I significa comillas

  22. 1

    El Klaus respuesta es la más útil, execpt para una pequeña miss : te
    agregar DISTINTOS en la instrucción select.

    Sin embargo, si usted está seguro de que ninguna mesa+nombres de columna puede ser equivalente
    para dos tablas diferentes, también se puede utilizar :

    select sequence_name, --PG_CLASS.relname, PG_ATTRIBUTE.attname
           reset_sequence(split_part(sequence_name, '_id_seq',1))
    from PG_CLASS
    join PG_ATTRIBUTE on PG_ATTRIBUTE.attrelid = PG_CLASS.oid
    join information_schema.sequences
         on information_schema.sequences.sequence_name = PG_CLASS.relname || '_' || PG_ATTRIBUTE.attname
    where sequence_schema='public';

    que es una extensión de user457226 solución para el caso cuando
    el interés de algunos nombre de la columna no es ‘ID’.

    • …por supuesto, también un cambio en la «reset_sequence» es necesaria, que es la adición de un «columnname» parámetro, para usar en lugar de «id».
  23. 1

    Feo hack para solucionarlo utilizando algunos de shell de la magia, no es una gran solución, pero podría inspirar a otros con problemas similares 🙂

    pg_dump -s <DATABASE> | grep 'CREATE TABLE' | awk '{print "SELECT setval(#" $3 "_id_seq#, (SELECT MAX(id) FROM " $3 "));"}' | sed "s/#/'/g" | psql <DATABASE> -f -
  24. 1
    select 'SELECT SETVAL(' || seq [ 1] || ', COALESCE(MAX('||column_name||')+1, 1) ) FROM '||table_name||';'
    from (
           SELECT table_name, column_name, column_default, regexp_match(column_default, '''.*''') as seq
           from information_schema.columns
           where column_default ilike 'nextval%'
         ) as sequense_query
    • Mientras que este código puede responder a la pregunta, proporcionar contexto adicional acerca de por qué y/o cómo este código responde a la pregunta que mejora su valor de largo plazo.
  25. 0

    Intentar reindex.

    ACTUALIZACIÓN: Como se señaló en los comentarios, esto fue en respuesta a la pregunta original.

    • reindex no trabajo, sólo parece incremento en el índice 1
    • reindex no funcionaba porque estaba respondiendo a tu pregunta original, sobre la base de datos de índices, secuencias no
  26. 0

    SELECT setval... hace JDBC bork, así que aquí un Java compatible con la forma de hacer esto:

    -- work around JDBC 'A result was returned when none was expected.'
    -- fix broken nextval due to poorly written 20140320100000_CreateAdminUserRoleTables.sql
    DO 'BEGIN PERFORM setval(pg_get_serial_sequence(''admin_user_role_groups'', ''id''), 1 + COALESCE(MAX(id), 0), FALSE) FROM admin_user_role_groups; END;';
  27. 0

    Un método para la actualización de todas las secuencias en el esquema que se utiliza como un IDENTIFICADOR:

    DO $$ DECLARE
      r RECORD;
    BEGIN
    FOR r IN (SELECT tablename, pg_get_serial_sequence(tablename, 'id') as sequencename
              FROM pg_catalog.pg_tables
              WHERE schemaname='YOUR_SCHEMA'
              AND tablename IN (SELECT table_name 
                                FROM information_schema.columns 
                                WHERE table_name=tablename and column_name='id')
              order by tablename)
    LOOP
    EXECUTE
            'SELECT setval(''' || r.sequencename || ''', COALESCE(MAX(id), 1), MAX(id) IS NOT null)
             FROM ' || r.tablename || ';';
    END LOOP;
    END $$;

Dejar respuesta

Please enter your comment!
Please enter your name here