List all sequences in a Postgres db 8.1 with SQL

Solution 1:

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;

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

Solution 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