List all sequences in a Postgres db 8 1 with SQL

Question

I m converting a db from postgres to mysql    Since i cannot find a tool that does the trick itself  i m going to convert all postgres sequences to autoincrement ids in mysql with autoincrement value   So  how can i list all sequences in a Postgres DB  8 1 version  with information about the table in which it s used  the next value etc with a SQL query   Be aware that i can t use the information schema sequences view in the 8 4 release

User · Answer

The relationship between automatically generated sequences ( such as those created for SERIAL columns ) and the parent table is modelled by the sequence owner attribute.

You can modify this relationship using the OWNED BY clause of the ALTER SEQUENCE commmand

e.g. ALTER SEQUENCE foo_id OWNED by foo_schema.foo_table

to set it to be linked to the table foo_table

or ALTER SEQUENCE foo_id OWNED by NONE

to break the connection between the sequence and any table

The information about this relationship is stored in the pg_depend catalogue table.

the joining relationship is the link between pg_depend.objid -> pg_class.oid WHERE relkind = 'S' - which links the sequence to the join record and then pg_depend.refobjid -> pg_class.oid WHERE relkind = 'r' , which links the join record to the owning relation ( table )

This query returns all the sequence -> table dependencies in a database. The where clause filters it to only include auto generated relationships, which restricts it to only display sequences created by SERIAL typed columns.

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

User · Answer

Get sequences by each column of each table via parsing of DEFAULT clause  This method provides info about to which column sequences are linked and does not use dependencies which may not exist for some sequences  Even pg get serial sequence sch nspname       tbl relname  col attname  function found not all sequences for me   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  gt  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    Note that 1 sequence can be used in multiple tables  so it can be listed in multiple rows here

User · Answer

Kind of a hack  but try this      select  select        relname         as sequence  last value from       relname       union    FROM pg catalog pg class c   WHERE c relkind IN   S         Remove the last UNION and execute the result

User · Answer

sequence info   max value  SELECT   FROM information schema sequences    sequence info   last value  SELECT   FROM  lt sequence name gt

User · Answer

Improvement of the previous answer   select string agg  select sequence name  last value from      relname  chr 13      union     chr 13  order by relname   from pg class where relkind   S

User · Answer

I know this post is pretty old  but I found the solution by CMS to be very useful as I was looking for an automated way to link a sequence to the table AND column  and wanted to share   The use of pg depend catalog table was the key   I expanded what was done to   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          - gt   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      This version adds column to the list of fields returned   With both the table name and the column name in hand  a call to pg set serial sequence makes it easy to ensure that all sequences in the database are set correctly   For example   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     Hope this helps someone with resetting sequences

User · Answer

Run  psql -E  and then  ds

User · Answer

This function shows the last value of each sequence   It outputs a 2 columns table that says the sequence name plus it s last generated value   drop function if exists public show sequence stats    CREATE OR REPLACE FUNCTION public show sequence stats       RETURNS TABLE tablename text  last value bigint       LANGUAGE  plpgsql      COST 100     VOLATILE      ROWS 1000 AS  BODY  declare r refcursor  rec record  dynamic query varchar          BEGIN             dynamic query  select tablename last value from                 open r for execute  select nspname relname from pg class c join pg namespace n on c relnamespace n oid where relkind     S   order by nspname                fetch next from r into rec              while found              loop                 dynamic query dynamic query     select       rec nspname           rec relname        tablename  last value from      rec nspname           rec relname      union all                    fetch next from r into rec               end loop              close r               dynamic query rtrim dynamic query  union all         x order by last value desc                return query execute dynamic query          END   BODY    select   from show sequence stats

User · Answer

after a little bit of pain  i got it   the best way to achieve this is to list all tables  select   from pg tables where schemaname     lt schema name gt     and then  for each table  list all columns with attributes  select   from information schema columns where table name     lt table name gt     then  for each column  test if it has a sequence  select pg get serial sequence   lt table name gt      lt column name gt      and then  get the information about this sequence  select   from  lt sequence name gt

User · Answer

Partially tested but looks mostly complete   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    Credit where credit is due    it s partly reverse engineered from the SQL logged from a  d on a known table that had a sequence   I m sure it could be cleaner too  but hey  performance wasn t a concern

User · Answer

Thanks for your help    Here is the pl pgsql function which update each sequence of a database   --------------------------------------------------------------------------------------------------------- --- 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

User · Answer

Note  that starting from PostgreSQL 8 4 you can get all information about sequences used in the database via   SELECT   FROM information schema sequences      Since I m using a higher version of PostgreSQL  9 1   and was searching for same answer high and low  I added this answer for posterity s sake and for future searchers

User · Answer

I know the question was about postgresql version 8 but I wrote this simple way here for people who want to get sequences in version 10 and upper you can use the bellow query select   from pg sequences  view-pg-sequences

User · Answer

Here is another one which has the schema name beside the sequence name  select nspname relname from pg class c join pg namespace n on c relnamespace n oid where relkind    S  order by nspname

User · Answer

Assuming exec   function declared in this post https   stackoverflow com a 46721603 653539   sequences together with their last values can be fetched using single query   select s sequence schema  s sequence name     select   from exec  select last value from      s sequence schema           s sequence name  as e lv bigint   last value from information schema sequences s

User · Answer

This statement lists the table and column that is associated with each sequence   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    more see here link to answer

User · Answer

Here is an example how to use psql to get a list of all sequences with their last value   psql -U  lt username gt  -d  lt database gt  -t -c  SELECT  SELECT        c relname        as sequence name  last value FROM      c relname        FROM pg class c WHERE  c relkind    S      psql -U  lt username gt  -d  lt database gt  -t

User · Answer

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

User · Answer

The following query gives names of all sequences   SELECT c relname FROM pg class c WHERE c relkind    S     Typically a sequence is named as   table  id seq  Simple regex pattern matching will give you the table name   To get last value of a sequence use the following query   SELECT last value FROM test id seq

[sql] List all sequences in a Postgres db 8.1 with SQL

Examples related to sql

Examples related to database

Examples related to postgresql

Examples related to migration

Examples related to sequences