We have a number of options when it comes to returning a list of sequences in PostgreSQL. By “sequences”, I mean “sequence objects”, or “sequence generators”. These are objects we can create in order to generate a series of sequential numbers.

Here are three ways to get a list of sequences in Postgres.

Enter \ds

When using psql, we can enter \ds to return a list of sequences in the database. This is the \d meta command with the s argument. The s argument narrows it down to just sequences.

Example:

\ds

Result:

                  List of relations
 Schema |         Name         |   Type   |  Owner   
--------+----------------------+----------+----------
 public | albums_albumid_seq   | sequence | barney
 public | artists_artistid_seq | sequence | barney
 public | cats_catid_seq       | sequence | postgres
 public | genres_genreid_seq   | sequence | barney
 public | idiots_idiotid_seq   | sequence | postgres
 public | sequence1            | sequence | barney
 public | t1_c1_seq            | sequence | postgres

We can append it with a + sign to include extra information about each sequence:

\ds+

Result:

                               List of relations
 Schema |         Name         |   Type   |  Owner   |    Size    | Description 
--------+----------------------+----------+----------+------------+-------------
 public | albums_albumid_seq   | sequence | barney   | 8192 bytes | 
 public | artists_artistid_seq | sequence | barney   | 8192 bytes | 
 public | cats_catid_seq       | sequence | postgres | 8192 bytes | 
 public | genres_genreid_seq   | sequence | barney   | 8192 bytes | 
 public | idiots_idiotid_seq   | sequence | postgres | 8192 bytes | 
 public | sequence1            | sequence | barney   | 8192 bytes | 
 public | t1_c1_seq            | sequence | postgres | 8192 bytes | 

The pg_sequence View

Another way to get a list of sequences is by querying the pg_sequence view. We can join this view with the pg_class view to get more meaningful information (such as the sequence name).

Example:

SELECT
    pc.relname,
    ps.seqstart,
    ps.seqincrement,
    ps.seqmax,
    ps.seqmin,
    ps.seqcache,
    ps.seqcycle
FROM pg_class pc
JOIN pg_sequence ps 
ON pc.oid = ps.seqrelid;

Result:

       relname        | seqstart | seqincrement |   seqmax   | seqmin | seqcache | seqcycle 
----------------------+----------+--------------+------------+--------+----------+----------
 artists_artistid_seq |        1 |            1 | 2147483647 |      1 |        1 | f
 genres_genreid_seq   |        1 |            1 | 2147483647 |      1 |        1 | f
 albums_albumid_seq   |        1 |            1 | 2147483647 |      1 |        1 | f
 t1_c1_seq            |        1 |            1 | 2147483647 |      1 |        1 | f
 cats_catid_seq       |        1 |            1 | 2147483647 |      1 |        1 | f
 idiots_idiotid_seq   |      101 |           10 |     250000 |    100 |       10 | f
 sequence1            |       -1 |           -1 |         -1 |     -3 |        1 | f

The information_schema.sequences View

Another view to consider is the information_schema.sequences view:

SELECT * FROM information_schema.sequences;

Result:

 sequence_catalog | sequence_schema | sequence_name  | data_type | numeric_precision | numeric_precision_radix | numeric_scale | start_value | minimum_value | maximum_value | increment | cycle_option 
------------------+-----------------+----------------+-----------+-------------------+-------------------------+---------------+-------------+---------------+---------------+-----------+--------------
 barney           | public          | cats_catid_seq | integer   |                32 |                       2 |             0 | 1           | 1             | 2147483647    | 1         | NO
 barney           | public          | sequence1      | bigint    |                64 |                       2 |             0 | -1          | -3            | -1            | -1        | NO
(2 rows

This view returns only those views that the user has access to (by way of being the owner or having some privilege).