Lister toutes les séquences dans une base de données Postgres 8.1 avec SQL

Je convertit une base de données de postgres en mysql.

Comme je ne peux pas trouver un outil qui fait l'affaire lui-même, je vais convertir toutes les séquences postgres en Id autoincrement dans mysql avec une valeur autoincrement.

Alors, comment puis-je liste toutes les séquences dans un Postgres DB (8.1 version) avec des informations sur le tableau dans lequel il est utilisé, la valeur suivante etc avec une requête SQL?

Sachez que je ne peux pas utiliser la vue information_schema.sequences dans la version 8.4.

96
demandé sur apelliciari 2009-09-29 19:19:56

14 réponses

La requête suivante donne les noms de toutes les séquences.

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

Généralement une séquence est nommé ${table}_id_seq. Simple regex pattern matching vous donnera le nom de la table.

Pour obtenir la dernière valeur d'une séquence d'utiliser la requête suivante:

SELECT last_value FROM test_id_seq;
168
répondu Anand Chitipothu 2011-03-11 10:48:36

Exécution: psql -E, puis \ds

42
répondu 2009-09-29 16:04:23

Notez qu'à partir de PostgreSQL 8.4 vous pouvez obtenir Toutes les Informations sur les séquences utilisées dans la base de données via:

SELECT * FROM information_schema.sequences;

Puisque j'utilise une version supérieure de PostgreSQL (9.1), et que je cherchais la même réponse haut et bas, j'ai ajouté cette réponse pour la postérité et pour les futurs chercheurs.

40
répondu raveren 2014-11-06 11:31:01

Après un peu de douleur, je l'ai eu.

La meilleure façon d'y parvenir est de lister toutes les tables

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

Puis, pour chaque table, lister toutes les colonnes avec des attributs

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

Ensuite, pour chaque colonne, testez si elle a une séquence

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

Et puis, obtenez les informations sur cette séquence

select * from <sequence_name>
21
répondu apelliciari 2009-10-01 08:08:15

La relation entre les séquences générées automatiquement (telles que celles créées pour les colonnes série) et la table parente est modélisée par l'attribut sequence owner.

Vous pouvez modifier cette relation en utilisant la clause OWNED BY de la commande alter SEQUENCE

Par exemple. Modifier la séquence foo_id appartenant à foo_schema.foo_table

Pour qu'il soit lié à la table foo_table

Ou Modifier la séquence foo_id appartenant à NONE

Pour casser la connexion entre la séquence et n'importe quelle table

Les informations sur cette relation sont stockées dans la table de catalogue pg_depend .

La relation de jointure est le lien entre pg_depend.id d'objet -> pg_class.oid où relkind = ' S ' - qui relie la séquence à l'enregistrement de jointure, puis pg_depend.refobjid - > pg_class.oid où relkind = 'r', qui lie l'enregistrement de jointure à la relation propriétaire (table)

Cette requête renvoie toute la séquence - > table dépendances dans une base de données. La clause where la filtre pour inclure uniquement les relations générées automatiquement, ce qui la limite à afficher uniquement les séquences créées par des colonnes typées en série.

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' ;
8
répondu cms 2012-07-26 06:49:15

Informations sur la séquence: valeur maximale

SELECT * FROM information_schema.sequences;

Informations sur la séquence: dernière valeur

SELECT * FROM <sequence_name>

7
répondu bbh 2016-10-20 12:28:21

Partiellement testé mais semble presque complet.

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;

Crédit lorsque le crédit est dû... il est partiellement reverse engineered à partir du SQL enregistré à partir d'un \d sur une table connue qui avait une séquence. Je suis sûr que ça pourrait être plus propre aussi, mais bon, la performance n'était pas une préoccupation.

2
répondu 2009-10-02 19:16:53

Je sais que ce post est assez vieux, mais j'ai trouvé la solution par CMS pour être très utile car je cherchais un moyen automatisé de lier une séquence à la table et à la colonne, et je voulais partager. L'utilisation de pg_depend catalog table était la clé. J'ai élargi ce qui a été fait à:

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' ;

Cette version ajoute une colonne à la liste des champs renvoyés. Avec le nom de la table et le nom de la colonne en main, un appel à pg_set_serial_sequence permet de s'assurer facilement que tous les séquences dans la base de données sont définies correctement. Par exemple:

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$;

J'espère que cela aidera quelqu'un à réinitialiser les séquences!

2
répondu DBAYoder 2017-05-23 12:02:50

Amélioration de la réponse précédente:

select string_agg('select sequence_name, last_value from ' || relname, chr(13) || 'union' || chr(13) order by relname) 
from pg_class where relkind ='S'
1
répondu Alexander Ryabov 2014-03-31 11:18:22

Une sorte de hack, mais essayez ceci:

Sélectionnez " sélectionner "' || relname || "' en tant que séquence de, last_value de '|| relname | | "union" De pg_catalog. pg_class c Où C. relkind dans ('S',");

Supprime la dernière UNION et exécute le résultat

0
répondu jimbob 2013-01-04 00:25:09

Cette instruction répertorie la table et la colonne associées à chaque séquence:

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'

Plus voir ici lien pour répondre

0
répondu 2016-08-30 16:44:59

Merci pour votre aide.

Voici la fonction pl/pgsql qui met à jour chaque séquence d'une base de données.

---------------------------------------------------------------------------------------------------------
--- 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();
0
répondu Tom Milon 2016-09-07 17:21:29

En voici un autre qui a le nom du schéma à côté du nom de la séquence

select nspname,relname from pg_class c join pg_namespace n on c.relnamespace=n.oid where relkind = 'S' order by nspname
0
répondu Robin 2017-12-29 14:55:46

Récupère les séquences par chaque colonne de chaque table via l'analyse de la clause DEFAULT. Cette méthode fournit des informations sur les séquences de colonnes liées et n'utilise pas les dépendances qui peuvent ne pas exister pour certaines séquences. Même la fonction pg_get_serial_sequence(sch.nspname||'.'||tbl.relname, col.attname) trouvée pas toutes les séquences pour moi!

Solution:

SELECT
    seq_sch.nspname  AS sequence_schema
  , seq.relname      AS sequence_name
  , seq_use."schema" AS used_in_schema
  , seq_use."table"  AS used_in_table
  , seq_use."column" AS used_in_column
FROM pg_class seq
  INNER JOIN pg_namespace seq_sch ON seq_sch.oid = seq.relnamespace
  LEFT JOIN (
              SELECT
                  sch.nspname AS "schema"
                , tbl.relname AS "table"
                , col.attname AS "column"
                , regexp_split_to_array(
                      TRIM(LEADING 'nextval(''' FROM
                           TRIM(TRAILING '''::regclass)' FROM
                                pg_get_expr(def.adbin, tbl.oid, TRUE)
                           )
                      )
                      , '\.'
                  )           AS column_sequence
              FROM pg_class tbl --the table
                INNER JOIN pg_namespace sch ON sch.oid = tbl.relnamespace
                --schema
                INNER JOIN pg_attribute col ON col.attrelid = tbl.oid
                --columns
                INNER JOIN pg_attrdef def ON (def.adrelid = tbl.oid AND def.adnum = col.attnum) --default values for columns
              WHERE tbl.relkind = 'r' --regular relations (tables) only
                    AND col.attnum > 0 --regular columns only
                    AND def.adsrc LIKE 'nextval(%)' --sequences only
            ) seq_use ON (seq_use.column_sequence [1] = seq_sch.nspname AND seq_use.column_sequence [2] = seq.relname)
WHERE seq.relkind = 'S' --sequences only
ORDER BY sequence_schema, sequence_name;

Notez que 1 séquence peut être utilisée dans plusieurs tables, donc elle peut être listée dans plusieurs lignes ici.

0
répondu Evgeny Nozdrev 2018-08-16 14:12:04