Lister toutes les séquences dans un db Postgres 8.1 avec SQL

Question

Je suis en train de convertir une base de données de postgres à mysql.

Puisque je ne peux pas trouver un outil qui fait le truc lui-même, je vais convertir toutes les séquences postgres en ids autoincrémentés dans mysql avec la valeur autoincrémentée.

Donc, comment puis-je lister toutes les séquences dans une Postgres DB ( 8.1 ) avec des informations sur la table dans laquelle il est utilisé, la prochaine valeur etc. avec une requête SQL ?

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

Answer 1

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

Vous pouvez modifier cette relation à l'aide de la clause OWNED BY de la commande Commande ALTER SEQUENCE

par exemple ALTER SEQUENCE foo_id OWNED by foo_schema.foo_table

pour qu'il soit lié à la table foo_table

ou ALTER SEQUENCE foo_id OWNED by NONE

pour rompre la connexion entre la séquence et toute table

Les informations relatives à cette relation sont stockées dans le pg_depend catalogue table .

la relation de jointure est le lien entre pg_depend.objid -> pg_class.oid WHERE relkind = 'S' - qui relie la séquence à l'enregistrement de jointure et ensuite pg_depend.refobjid -> pg_class.oid WHERE relkind = 'r' , qui relie l'enregistrement de jointure à la relation propriétaire ( table )

Cette requête renvoie toutes les dépendances séquence -> table dans une base de données. La clause where la filtre pour n'inclure que les relations générées automatiquement, ce qui la restreint pour n'afficher que les séquences créées par des colonnes de type SERIAL.

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

Answer 2

Je sais que ce post est assez vieux, mais j'ai trouvé la solution en CMS s'est avéré 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 La table catalogue était la clé. J'ai développé ce qui a été fait pour :

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 la colonne à la liste des champs retourné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 toutes les séquences de 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 des séquences !

Answer 3

Je sais que la question portait sur la version 8 de postgresql mais j'ai écrit cette méthode simple ici pour les personnes qui veulent obtenir des séquences dans la version 10 et supérieure.

vous pouvez utiliser la requête suivante

select * from pg_sequences

view-pg-sequences

Answer 4

Cette déclaration énumère la table et la colonne qui sont 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 vers la réponse

Answer 5

select sequence_name, (xpath('/row/last_value/text()', xml_count))[1]::text::int as last_value
from (
    select sequence_schema,
            sequence_name,         
            query_to_xml(format('select last_value from %I.%I', sequence_schema, sequence_name), false, true, '') as xml_count
    from information_schema.sequences
    where sequence_schema = 'public'
) new_table order by last_value desc;