Reputation: 8501

List all sequences in a Postgres db 8.1 with SQL

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.

Upvotes: 221

Answers (21)

Anand Chitipothu

Reputation: 4377

The following query gives names of all sequences.

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

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;

Upvotes: 358

Luiz Carlos Muniz

Reputation: 151

SELECT nextval(sequence_schema || '.' || sequence_name)
FROM information_schema.sequences

Upvotes: 0

jimbob

Reputation: 29

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

Upvotes: 1

jian

Reputation: 4867

Get all sequence:

select * from pg_sequences;

PSQL:

\ds
\ds+
\ds *actor*

\ds *actor* will get all the sequence that sequence name contains actor characters.

Upvotes: 19

user80168

Reputation:

Launch psql with the -E flag ("echo the actual queries generated by \d and other backslash commands"), then enter the \ds command to list all sequences. You should see something like this:

# \ds
********* QUERY **********
SELECT n.nspname as "Schema",
  c.relname as "Name",
  CASE c.relkind WHEN 'r' THEN 'table' WHEN 'v' THEN 'view' WHEN 'm' THEN 'materialized view' WHEN 'i' THEN 'index' WHEN 'S' THEN 'sequence' WHEN 's' THEN 'special' WHEN 'f' THEN 'foreign table' WHEN 'p' THEN 'partitioned table' WHEN 'I' THEN 'partitioned index' END as "Type",
  pg_catalog.pg_get_userbyid(c.relowner) as "Owner"
FROM pg_catalog.pg_class c
     LEFT JOIN pg_catalog.pg_namespace n ON n.oid = c.relnamespace
WHERE c.relkind IN ('S','')
      AND n.nspname <> 'pg_catalog'
      AND n.nspname <> 'information_schema'
      AND n.nspname !~ '^pg_toast'
  AND pg_catalog.pg_table_is_visible(c.oid)
ORDER BY 1,2;
**************************

                     List of relations
 Schema |              Name              |   Type   | Owner 
--------+--------------------------------+----------+-------
 public | assignments_id_seq             | sequence | root
 public | framework_users_id_seq         | sequence | root
 public | lending_items_id_seq           | sequence | root
 public | proxy_borrower_requests_id_seq | sequence | root
 public | roles_id_seq                   | sequence | root
 public | stack_requests_id_seq          | sequence | root
(6 rows)

To examine a particular sequence, you can then run \d <sequence name>:

# \d lending_items_id_seq
********* QUERY **********

(...about four queries...)

**************************

                    Sequence "public.lending_items_id_seq"
  Type  | Start | Minimum |       Maximum       | Increment | Cycles? | Cache 
--------+-------+---------+---------------------+-----------+---------+-------
 bigint |     1 |       1 | 9223372036854775807 |         1 | no      |     1
Owned by: public.lending_items.id

Upvotes: 73

vatandoost

Reputation: 3757

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

Upvotes: 9

splash

Reputation: 13327

Here is an example how to use psql to get a list of all sequences with their last_value:

psql -U <username> -d <database> -t -c "SELECT 'SELECT ''' || c.relname || ''' as sequence_name, last_value FROM ' || c.relname || ';' FROM pg_class c WHERE (c.relkind = 'S')" | psql -U <username> -d <database> -t

Upvotes: 0

Manuel

Reputation: 31

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;

Upvotes: 2

Tomáš Záluský

Reputation: 12050

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

Upvotes: 0

A_V

Reputation: 372

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();

Upvotes: 1

Evgeny Nozdrev

Reputation: 1566

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 > 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.

Upvotes: 1

buqing

Reputation: 1015

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

Upvotes: 1

bbh

Reputation: 361

sequence info : max value

SELECT * FROM information_schema.sequences;

sequence info : last value

SELECT * FROM <sequence_name>

Upvotes: 18

Tom Milon

Reputation: 11

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();

Upvotes: 1

user6606668

Reputation:

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

Upvotes: 4

DBAYoder

Reputation: 93

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,
       '->' 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!

Upvotes: 9

raveren

Reputation: 18541

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.

Upvotes: 137

Alexander Ryabov

Reputation: 59

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'

Upvotes: 2

cms

Reputation: 5982

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

Upvotes: 12

joatmon

Reputation:

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.

Upvotes: 1

apelliciari

Reputation: 8501

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 = '<schema_name>'

and then, for each table, list all columns with attributes

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

then, for each column, test if it has a sequence

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

and then, get the information about this sequence

select * from <sequence_name>

Upvotes: 36

List all sequences in a Postgres db 8.1 with SQL

Answers (21)

Related Questions