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.

Elwandaelwee

It should be noted that you're doing the conversion the wrong way. Since Oracle bought Sun they've been slowly killing off MySQL so unless you despise your client (in which case you should simply quit) you should stick with PostgreSQL as no corporation (pro-monopoly of not) can come along, gobble up PostgreSQL and eventually replace it with their own database. – Mcnully

@Mcnully I'd say that there is a billion and one other reasons to stick with postgres, and a billion more to never touch mysql, but yes - your point is still very valid :) – Heinie

@Mcnully at the time (2009) we need a simpler database to deal with - and mysql was better coupled to php – Elwandaelwee

Answer 1

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;

Shf

The ${table}_id_seq hint was useful – Submit

${table}_${column}_seq for auto-created sequences – Straightway

Answer 2

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.

Declass

Protip: sort the answers by "active". Posterity gets more and more relevant as questions are becoming increasingly old.. – Declass

Cool. And it looks like if I pick the "active" sorting method, the site just remembers the setting right away (here I was digging around in preferences to find a place to set it as default to no avail). Hm, now if only we had a "asker-accepted answer doesn't automatically trump everything else"-option, that would be a truly great victory for posterity. – Perloff

Note sure this table was introduced in PG 8.4, I'd rather say PG 8.2 after the official documentation : postgresql.org/docs/8.2/infoschema-sequences.html – Mammary

That "all information" doesn't include the current value. – Interpose

true, you still have to use SELECT last_value FROM <sequence_name> to get that. This answer shows the schema of the sequence which is what I needed to filter on and something that is not available with the pg_class view. – Spermous

Answer 3

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

Pentapody

i don't need only the list of sequences, i need the table in which it's used, the next value etc.. And i have to do that in SQL – Elwandaelwee

Then, on every sequence do \d <name> (being still in psql -E) – Pentapody

again, this is not in SQL and doesn't show at which table the sequence is attached – Elwandaelwee

@avastreg: did you run it the way i told you to? and why not? – Pentapody

@avastreg: It seems you did not try the suggestion, because it will show you exactly the SQL you need. – Arillode

i need to do that directly with SQL statements! i can't use command line – Elwandaelwee

@avastreg: JUST DO IT ONCE. And it will show you the queries! – Pentapody

sorry only now i understand what you meant! – Elwandaelwee

Answer 4

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>

Elwandaelwee

Answer 5

sequence info : max value

SELECT * FROM information_schema.sequences;

sequence info : last value

SELECT * FROM <sequence_name>

Worthwhile

Answer 6

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.

Margalit

Answer 7

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

Terra

Useful explanation of dependencies between tables & sequences. But your query found not all sequences for me. It seems that some sequences are exists without any dependencies. – Straightway

yes, this query is explicitly only demonstrating sequences that are defined by database serial column definitions. This is explained in the answer. – Terra

Answer 8

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!

Firetrap

a few years on, I notice your update, and pop by to give an upvote :-) – Terra

Thanks @cms. I still use this today. – Firetrap

Answer 9

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

Entail

Answer 10

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

Sanitary

Answer 11

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'

Complicacy

Please don't just put your code without any explanation. Also, since you've stated that your code is an "Improvement of the previous answer", you should also tell us WHY it is an improvement. Oh, don't give up, and welcome to SO! – Wolcott

Should I write a page of senseless text instead of precise code (couple of lines)? – Complicacy

Never said that. I like simple, precise code. But when stating that your code is an improvement, one or two lines explaining WHY it is an improvement (better readability, improved performance, etc) would not hurt. And you'd probably get a +1 from me as well. – Wolcott

Answer 12

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;

Fiji

Answer 13

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.

Tarsia

Answer 14

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

Calctufa

Answer 15

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

Dam

Answer 16

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

Thermion

Answer 17

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.

Straightway

Answer 18

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

Brynhild

Answer 19

Assuming exec() function declared in this post https://mcmap.net/q/28801/-dynamic-sql-query-in-postgres , 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

Agglomerate

Answer 20

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

Teresitateressa

Answer 21

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

Cornute