Comment réinitialiser la séquence de clés primaires de postgres quand elle tombe désynchronisée?
J'ai rencontré le problème que ma séquence de clés primaires n'est pas synchronisée avec mes lignes de table.
C'est-à-dire que lorsque j'insère une nouvelle ligne, j'obtiens une erreur de clé en double car la séquence impliquée dans le type de données série renvoie un nombre qui existe déjà.
Il semble être causé par import / restaure ne pas maintenir la séquence correctement.
25 réponses
-- 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;
pg_get_serial_sequence peut être utilisé pour éviter toute erreur d'hypothèses sur le nom de la séquence. Cela réinitialise la séquence en un seul coup:
SELECT pg_catalog.setval(pg_get_serial_sequence('table_name', 'id'), (SELECT MAX(id) FROM table_name)+1);
Ou plus concise:
SELECT pg_catalog.setval(pg_get_serial_sequence('table_name', 'id'), MAX(id)) FROM table_name;
Cependant, ce formulaire ne peut pas gérer correctement les tables vides, puisque max (id) est null, et vous ne pouvez pas non plus définir Val 0 car il serait hors de portée de la séquence. Une solution de contournement pour cela est de recourir à la syntaxe ALTER SEQUENCE, c'est-à-dire
ALTER SEQUENCE table_name_id_seq RESTART WITH 1;
ALTER SEQUENCE table_name_id_seq RESTART; -- 8.4 or higher
Mais ALTER SEQUENCE est d'une utilité limitée car le nom la valeur de redémarrage ne peut pas être des expressions.
Il semble que la meilleure solution tout usage est d'appeler setval avec false comme 3ème paramètre, nous permettant de spécifier la "valeur suivante à utiliser":
SELECT setval(pg_get_serial_sequence('t1', 'id'), coalesce(max(id),0) + 1, false) FROM t1;
Cela coche toutes mes cases:
- évite le codage en dur du nom de séquence réel
- gère correctement les tables vides
- gère les tables avec des données existantes et ne laisse pas trou dans la séquence
Enfin, notez que pg_get_serial_sequence ne fonctionne que si le la séquence appartient à la colonne. Ce sera le cas si la colonne incrémentée a été définie comme un type serial, mais si la séquence a été ajoutée manuellement, il est nécessaire de s'assurer que ALTER SEQUENCE .. OWNED BY est également effectuée.
C'est-à-dire si le type serial a été utilisé pour la création de la table, tout devrait fonctionner:
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;
Mais si les séquences ont été ajoutées manuellement:
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;
Cela réinitialisera toutes les séquences du public en ne faisant aucune hypothèse sur les noms de table ou de colonne. Testé sur la version 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%';
Le la plus courte et la plus rapide façon:
SELECT setval('tbl_tbl_id_seq', max(tbl_id)) FROM tbl;
tbl_id être le serial colonne de la table tbl, dessin de la séquence tbl_tbl_id_seq (qui est le nom automatique par défaut).
Si vous ne connaissez pas le nom de la séquence (qui n'ont pas à être en forme par défaut), utilisez pg_get_serial_sequence():
SELECT setval(pg_get_serial_sequence('tbl', 'tbl_id'), max(tbl_id)) FROM tbl;
Il n'y a pas de hors-par-une erreur ici. par documentation:
La forme à deux paramètres définit le champ last_value de la séquence valeur spécifiée et définit son champ
is_calledsur true, ce qui suivantnextvalavancera la séquence avant de renvoyer une valeur.
Gras emphase mine.
Concurrence
Il n'y a pas encore de défense contre l'activité de séquence simultanée ou les Écritures dans la table dans les requêtes ci-dessus. Si c'est pertinent, vous pourriez verrouillage de la table en mode exclusif. Il garde les transactions simultanées de écrire un nombre plus élevé pendant que vous essayez de vous synchroniser. (Il bloque également temporairement les Écritures inoffensives ne pas jouer avec le nombre maximum.)
Mais il ne prend pas en compte les clients qui peuvent avoir récupéré des numéros de séquence à l'avance sans aucun verrou sur la table principale, encore (ce qui peut arriver). Pour permettre cela aussi, seulement augmente la valeur actuelle de la séquence, ne la diminue jamais. Cela peut sembler paranoïaque, mais c'est en accord avec la nature des séquences et de la défense contre les problèmes de concurrence.
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;
Modifier la séquence sequence_name redémarrer avec (SELECT max (id) FROM table_name);
Ne fonctionne pas.
Copié à partir de la réponse @ tardate:
SELECT setval(pg_get_serial_sequence('table_name', 'id'), MAX(id)) FROM table_name;
Cette commande ne modifie que la valeur de la séquence de clés générée automatiquement dans postgresql
ALTER SEQUENCE "your_sequence_name" RESTART WITH 0;
À la place de zéro, vous pouvez mettre n'importe quel numéro à partir duquel vous souhaitez redémarrer la séquence.
Le nom de séquence par défaut sera "TableName_FieldName_seq". Par exemple, si votre nom de la table est "MyTable" et votre nom de domaine est "MyID", puis votre nom de la séquence sera "MyTable_MyID_seq".
Cette réponse est la même que la réponse de @murugesanponappan, mais il y a une erreur de syntaxe dans sa solution. vous ne pouvez pas utiliser la sous-requête (select max()...) dans alter commande. De sorte que vous devez utiliser une valeur numérique fixe ou vous devez utiliser une variable à la place de la sous-requête.
Réinitialise toutes les séquences, pas d'hypothèses sur les noms sauf que la clé primaire de chaque table est "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%';
Ces fonctions sont dangereuses lorsque les noms de séquence, de colonne, de table ou de schéma ont des caractères amusants tels que des espaces, des signes de ponctuation, etc. J'ai écrit ceci:
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; $$ ;
Vous pouvez l'appeler pour une seule séquence en lui passant L'OID et il retournera le nombre le plus élevé utilisé par toute table qui a la séquence par défaut; ou vous pouvez l'exécuter avec une requête comme celle - ci, pour réinitialiser toutes les séquences dans votre base de données:
select relname, setval(oid, sequence_max_value(oid))
from pg_class
where relkind = 'S';
En utilisant un qual différent vous pouvez réinitialiser uniquement la séquence dans un certain schéma, et ainsi de suite. Par exemple, si vous voulez ajuster les séquences dans le schéma "public":
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';
Notez qu'en raison du fonctionnement de setval (), vous n'avez pas besoin d'ajouter 1 au résultat.
En guise de conclusion, je dois avertir que certaines bases de données semblent avoir des liens par défaut vers des séquences de manière à ne pas laisser les catalogues système en avoir toutes les informations. Cela arrive quand vous voyez des choses comme ça dans \ d de psql:
alvherre=# \d baz
Tabla «public.baz»
Columna | Tipo | Modificadores
---------+---------+------------------------------------------------
a | integer | default nextval(('foo_a_seq'::text)::regclass)
Notez que l'appel nextval() dans cette clause par défaut a un:: text cast en plus du:: regclass cast. Je pense que cela est dû au fait que les bases de données sont pg_dump'ed à partir d'anciennes versions de PostgreSQL. Ce qui se passera, c'est que la fonction sequence_max_value() ci-dessus ignorera une telle table. Pour résoudre le problème, vous pouvez redéfinir la clause DEFAULT pour faire référence à la séquence directement sans la distribution:
alvherre=# alter table baz alter a set default nextval('foo_a_seq');
ALTER TABLE
Alors psql l'affiche correctement:
alvherre=# \d baz
Tabla «public.baz»
Columna | Tipo | Modificadores
---------+---------+----------------------------------------
a | integer | default nextval('foo_a_seq'::regclass)
Dès que vous avez corrigé cela, le fonction fonctionne correctement pour cette table ainsi que tous les autres qui pourraient utiliser la même séquence.
Réinitialiser toutes les séquences du public
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';
MA version utilise la première, avec une vérification d'erreur...
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;
Mettre tout cela ensemble
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';
Corrigera la séquence ' id' de la table donnée (comme généralement nécessaire avec django par exemple).
Quelques réponses vraiment hardcore ici, je suppose que c'était vraiment mauvais au moment où cela a été demandé, car beaucoup de réponses d'ici ne fonctionnent pas pour la version 9.3. La documentation depuis la version 8.0 fournit une réponse à cette question:
SELECT setval('serial', max(id)) FROM distributors;
Aussi, si vous devez vous occuper des noms de séquence sensibles à la casse, c'est comme ça que vous le faites:
SELECT setval('"Serial"', max(id)) FROM distributors;
Je suggère cette solution trouvée sur le wiki postgres. Il met à jour toutes les séquences de vos tables.
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;
Comment utiliser (à partir du wiki postgres):
- enregistrez ceci dans un fichier, dites ' reset.sql '
- exécutez le fichier et enregistrez sa sortie d'une manière qui n'inclut pas les en-têtes habituels, puis exécutez cette sortie. Exemple:
Exemple:
psql -Atq -f reset.sql -o temp
psql -f temp
rm temp
Article Original (également avec correctif pour la propriété de la séquence) ici
Avant je n'avais pas encore essayé le code: dans ce qui suit je poste la version pour le code sql pour les solutions Klaus et user457226 qui a fonctionné sur mon pc [Postgres 8.3], avec juste quelques petits ajustements pour celui de Klaus et de ma version pour celui de l'utilisateur457226.
Solution Klaus:
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 solution:
--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';
Revérifier toute la séquence dans la fonction de schéma public
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;
Encore un autre plpgsql - réinitialise uniquement 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;
$$
;
Commentant également la ligne {[2] } donnera la liste, ne réinitialisant pas réellement la valeur
Pour redémarrer toute la séquence à 1 Utilisez:
-- 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';
Ce problème se produit avec moi lorsque vous utilisez entity framework pour créer la base de données, puis semez la base de données avec des données initiales, ce qui rend la séquence non appariée.
Je L'ai résolu en créant un script à exécuter après l'ensemencement de la base de données:
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$
Si vous voyez cette erreur lorsque vous chargez des données SQL personnalisées pour l'initialisation, une autre façon d'éviter cela est:
" au Lieu d'écrire:
INSERT INTO book (id, name, price) VALUES (1 , 'Alchemist' , 10),
Supprime le id (clé primaire) des données initiales
INSERT INTO book (name, price) VALUES ('Alchemist' , 10),
Cela permet de synchroniser la séquence Postgres !
Cette réponse est une copie 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();
J'ai passé une heure à essayer d'obtenir la réponse de djsnowsill pour travailler avec une base de données en utilisant des tables et des colonnes de cas mixtes, puis j'ai finalement trébuché sur la solution grâce à un commentaire de Manuel Darveau, mais je pensais que je pourrais le rendre un peu plus clair pour tout le monde:
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%';
Cela a l'avantage de:
- ne pas supposer que la colonne ID est orthographiée d'une manière particulière.
- ne pas supposer que toutes les tables ont une séquence.
- Fonctionne pour les noms de table/colonne à casse mixte.
- en utilisant format pour être plus concis.
À expliquer, le problème était que pg_get_serial_sequence prend chaînes à quoi vous faites allusion, donc si vous ne:
"TableName" --it thinks it's a table or column
'TableName' --it thinks it's a string, but makes it lower case
'"TableName"' --it works!
Ceci est réalisé en utilisant ''%1$I'' dans la chaîne de format, '' fait une apostrophe 1$ signifie premier arg, et I signifie entre guillemets
La réponse Klaus est la plus utile, execpt pour un peu manquer: vous avoir à ajouter DISTINCT dans l'instruction select.
Cependant, si vous êtes sûr qu'aucun nom de table + colonne ne peut être équivalent pour deux tables différentes, vous pouvez également utiliser:
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';
Qui est une extension de la solution user457226 pour le cas où un nom de colonne intéressé n'est pas 'ID'.
Hack laid pour le réparer en utilisant de la magie shell, pas une excellente solution mais pourrait inspirer les autres avec des problèmes similaires:)
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 -
SELECT setval... fait JDBC bork, alors voici une façon compatible Java de le faire:
-- 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;';