listn Sie alle Sequenzen in einer Postgres db 8.1 mit SQL auf

Ich konvertiere eine database von Postgres nach MySQL.

Da ich kein Tool finden kann, das den Trick selbst ausführt, werde ich alle Postgres-Sequenzen in Autoinkrement-IDs in Mysql mit Autoinkrement-Wert konvertieren.

Also, wie kann ich alle Sequenzen in einer Postgres DB (Version 8.1 ) mit Informationen über die Tabelle, in der sie verwendet wird, den nächsten Wert usw. mit einer SQL-Abfrage auflisten?

Beachten Sie, dass ich die view " information_schema.sequences in Version 8.4 nicht verwenden kann.

Solutions Collecting From Web of "listn Sie alle Sequenzen in einer Postgres db 8.1 mit SQL auf"

Die folgende Abfrage gibt die Namen aller Sequenzen an.

 SELECT c.relname FROM pg_class c WHERE c.relkind = 'S'; 

Normalerweise wird eine Sequenz als ${table}_id_seq . Der einfache Regex-Mustervergleich gibt Ihnen den Tabellennamen.

Um den letzten Wert einer Sequenz zu erhalten, verwenden Sie die folgende Abfrage:

 SELECT last_value FROM test_id_seq; 

Führen Sie psql -E : psql -E und dann \ds

Beachten Sie, dass Sie ab PostgreSQL 8.4 alle Informationen über die in der database verwendeten Sequenzen erhalten können über:

 SELECT * FROM information_schema.sequences; 

Da ich eine höhere Version von PostgreSQL (9.1) benutze und nach derselben Antwort high und low suchte, fügte ich diese Antwort der Nachwelt und zukünftigen searchrn hinzu.

Nach ein bisschen Schmerz habe ich es verstanden.

Der beste path, dies zu erreichen, besteht darin, alle Tabellen aufzulisten

 select * from pg_tables where schemaname = '<schema_name>' 

und listet dann für jede Tabelle alle Spalten mit Attributen auf

 select * from information_schema.columns where table_name = '<table_name>' 

Testen Sie dann für jede Spalte, ob es eine Sequenz hat

 select pg_get_serial_sequence('<table_name>', '<column_name>') 

und dann, get die Informationen über diese Sequenz

 select * from <sequence_name> 

Die Beziehung zwischen automatisch generierten Sequenzen (z. B. solchen, die für SERIAL-Spalten erstellt wurden) und der übergeordneten Tabelle wird durch das Attribut sequence owner modelliert.

Sie können diese Beziehung mithilfe der OWNED BY-Klausel des ALTER SEQUENCE-Befehls ändern

zB ALTER SEQUENCE foo_id OWNED von foo_schema.foo_table

um festzulegen, dass es mit der Tabelle foo_table verknüpft werden soll

oder ALTER SEQUENCE foo_id BESITZT von NONE

um die Verbindung zwischen der Sequenz und einer beliebigen Tabelle zu unterbrechen

Die Informationen zu dieser Beziehung werden in der Katalogtabelle pg_depend gespeichert.

die Verbindungsbeziehung ist die Verbindung zwischen pg_depend.objid -> pg_class.oid WHERE relkind = 'S' – die die Sequenz mit dem Join-datasatz verknüpft und dann pg_depend.refobjid -> pg_class.oid WHERE relkind = 'r', das die Join Record zu der besitzenden Relation (Tabelle)

Diese Abfrage gibt alle Sequenz-> Tabellenabhängigkeiten in einer database zurück. Die where-Klausel filtert es so, dass nur automatisch generierte Beziehungen einbezogen werden. Dadurch wird nur die Anzeige von Sequenzen eingeschränkt, die durch Spalten vom Typ SERIAL erstellt wurden.

 WITH fq_objects AS (SELECT c.oid,n.nspname || '.' ||c.relname AS fqname , c.relkind, c.relname AS relation FROM pg_class c JOIN pg_namespace n ON n.oid = c.relnamespace ), sequences AS (SELECT oid,fqname FROM fq_objects WHERE relkind = 'S'), tables AS (SELECT oid, fqname FROM fq_objects WHERE relkind = 'r' ) SELECT s.fqname AS sequence, '->' as depends, t.fqname AS table FROM pg_depend d JOIN sequences s ON s.oid = d.objid JOIN tables t ON t.oid = d.refobjid WHERE d.deptype = 'a' ; 

Sequenzinfo: Maximalwert

SELECT * FROM information_schema.sequences;

Sequenzinfo: letzter Wert

SELECT * FROM <sequence_name>

Teilweise getestet, sieht aber größtenteils vollständig aus.

 select * from (select n.nspname,c.relname, (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) as def from pg_class c, pg_attribute a, pg_namespace n where c.relkind='r' and c.oid=a.attrelid and n.oid=c.relnamespace and a.atthasdef and a.atttypid=20) x where x.def ~ '^nextval' order by nspname,relname; 

Kredit, wo Kredit fällig ist … es ist teilweise reverse engineered von der SQL, die von a \ d auf einer bekannten Tabelle protokolliert wurde, die eine Folge hatte. Ich bin sicher, es könnte auch sauberer sein, aber hey, performance war kein Problem.

Ich weiß, dass dieser Beitrag ziemlich alt ist, aber ich fand die Lösung von CMS sehr nützlich, da ich nach einer automatisierten Möglichkeit suchte, eine Sequenz mit der Tabelle UND-Spalte zu verknüpfen und teilen wollte. Die Verwendung der Katalogtabelle pg_depend war der Schlüssel. Ich habe erweitert, was getan wurde:

 WITH fq_objects AS (SELECT c.oid,n.nspname || '.' ||c.relname AS fqname , c.relkind, c.relname AS relation FROM pg_class c JOIN pg_namespace n ON n.oid = c.relnamespace ), sequences AS (SELECT oid,fqname FROM fq_objects WHERE relkind = 'S'), tables AS (SELECT oid, fqname FROM fq_objects WHERE relkind = 'r' ) SELECT s.fqname AS sequence, '->' as depends, t.fqname AS table, a.attname AS column FROM pg_depend d JOIN sequences s ON s.oid = d.objid JOIN tables t ON t.oid = d.refobjid JOIN pg_attribute a ON a.attrelid = d.refobjid and a.attnum = d.refobjsubid WHERE d.deptype = 'a' ; 

Diese Version fügt der list der zurückgegebenen Felder eine Spalte hinzu. Durch den Aufruf von pg_set_serial_sequence kann sowohl mit dem Tabellennamen als auch mit dem Spaltennamen sichergestellt werden, dass alle Sequenzen in der database korrekt eingestellt sind. Beispielsweise:

 CREATE OR REPLACE FUNCTION public.reset_sequence(tablename text, columnname text) RETURNS void LANGUAGE plpgsql AS $function$ DECLARE _sql VARCHAR := ''; BEGIN _sql := $$SELECT setval( pg_get_serial_sequence('$$ || tablename || $$', '$$ || columnname || $$'), (SELECT COALESCE(MAX($$ || columnname || $$),1) FROM $$ || tablename || $$), true)$$; EXECUTE _sql; END; $function$; 

Hoffe das hilft jemandem beim Zurücksetzen von Sequenzen!

Eine Art Hack, aber probier das aus:

Wählen Sie 'Auswählen' '' || Name || '' 'als Sequenz, letzter_Wert von' || Name || 'union' VON pg_catalog.pg_class c WHERE c.relkind IN ('S', '');

Entferne die letzte UNION und führe das Ergebnis aus

Verbesserung der vorherigen Antwort:

 select string_agg('select sequence_name, last_value from ' || relname, chr(13) || 'union' || chr(13) order by relname) from pg_class where relkind ='S' 

Diese statement listet die Tabelle und Spalte auf, die jeder Sequenz zugeordnet ist:

Code:

  SELECT t.relname as related_table, a.attname as related_column, s.relname as sequence_name FROM pg_class s JOIN pg_depend d ON d.objid = s.oid JOIN pg_class t ON d.objid = s.oid AND d.refobjid = t.oid JOIN pg_attribute a ON (d.refobjid, d.refobjsubid) = (a.attrelid, a.attnum) JOIN pg_namespace n ON n.oid = s.relnamespace WHERE s.relkind = 'S' AND n.nspname = 'public' 

mehr siehe hier, um zu antworten

Danke für Ihre Hilfe.

Hier ist die pl / pgsql-function, die jede Sequenz einer database aktualisiert.

 --------------------------------------------------------------------------------------------------------- --- Nom : reset_sequence --- Description : Générique - met à jour les séquences au max de l'identifiant --------------------------------------------------------------------------------------------------------- CREATE OR REPLACE FUNCTION reset_sequence() RETURNS void AS $BODY$ DECLARE _sql VARCHAR := ''; DECLARE result threecol%rowtype; BEGIN FOR result IN WITH fq_objects AS (SELECT c.oid,n.nspname || '.' ||c.relname AS fqname ,c.relkind, c.relname AS relation FROM pg_class c JOIN pg_namespace n ON n.oid = c.relnamespace ), sequences AS (SELECT oid,fqname FROM fq_objects WHERE relkind = 'S'), tables AS (SELECT oid, fqname FROM fq_objects WHERE relkind = 'r' ) SELECT s.fqname AS sequence, t.fqname AS table, a.attname AS column FROM pg_depend d JOIN sequences s ON s.oid = d.objid JOIN tables t ON t.oid = d.refobjid JOIN pg_attribute a ON a.attrelid = d.refobjid and a.attnum = d.refobjsubid WHERE d.deptype = 'a' LOOP EXECUTE 'SELECT setval('''||result.col1||''', COALESCE((SELECT MAX('||result.col3||')+1 FROM '||result.col2||'), 1), false);'; END LOOP; END;$BODY$ LANGUAGE plpgsql; SELECT * FROM reset_sequence(); 

Hier ist ein weiterer, der neben dem Sequenznamen den Schemanamen hat

 select nspname,relname from pg_class c join pg_namespace n on c.relnamespace=n.oid where relkind = 'S' order by nspname