PostgreSQL: How to list all sequences of a database

Overview

Sequences in PostgreSQL are often used to generate unique identifiers for rows in a table. Understanding how to manage and list them is key for database administration. This tutorial walks you through several methods to list all sequences in a PostgreSQL database, starting from basic approaches to more advanced queries with valuable information.

Using psql Meta-Commands

Let’s start with the simplest way to list sequences in a PostgreSQL database using psql interactive commands. The ‘\ds’ meta-command lists all sequences in the current database.

\ds

If you want to see detailed information, you may use ‘\ds+’. This command will list additional attributes such as type and owner alongside each sequence.

\ds+

Basic SQL Query

If you prefer SQL over meta-commands, or you’re working with SQL scripts, you can obtain a list of sequences by querying the PostgreSQL system catalog ‘pg_class’ with a filter on ‘relkind’ for sequences (‘S’).

SELECT relname FROM pg_class WHERE relkind = 'S';

Schema-specific Sequences

In case you want to list sequences for a specific schema, join the ‘pg_class’ with the ‘pg_namespace’ table to filter sequences by schema name.

SELECT relname FROM pg_class C JOIN pg_namespace N ON (N.oid = C.relnamespace) WHERE C.relkind = 'S' AND N.nspname = 'your_schema';

Inspecting Sequence Ownership

Sequences are often associated with specific tables. To list sequences along with the tables they are tied to, you can join ‘pg_class’, ‘pg_namespace’, and ‘pg_depend’ together.

SELECT S.relname, T.relname AS table_name
FROM pg_class S
LEFT JOIN pg_depend D ON (S.oid = D.objid)
LEFT JOIN pg_class T ON (D.refobjid = T.oid)
LEFT JOIN pg_namespace N ON (N.oid = S.relnamespace)
WHERE S.relkind = 'S' AND D.deptype = 'a';

Advanced Sequence Information

The ‘pg_sequences’ view provides an easy way to get detailed information such as the current value, minimum and maximum values, as well as the increment of all sequences in a database.

SELECT * FROM pg_sequences;

To filter this information by specific schema, add a WHERE clause referencing ‘schemaname’.

SELECT * FROM pg_sequences WHERE schemaname = 'your_schema';

Using Information Schema

Information schema provides a standardized way across different types of databases to fetch metadata. To list sequences using the information schema view in PostgreSQL, query ‘information_schema.sequences’.

SELECT * FROM information_schema.sequences WHERE sequence_schema = 'your_schema';

Exporting Sequence List for Documentation

When documenting your database, you might want to export the list of sequences. Combine various elements from previous examples into a single query to obtain a more comprehensive report which you can export as a CSV file.

COPY (SELECT S.relname, N.nspname, D.refobjid, T.relname AS table_name ...)
TO '/path_to_your_file/sequences_list.csv' WITH CSV HEADER;

Identifying Unused Sequences

Identifying sequences that aren’t associated with any tables can be crucial for database cleanup. Here’s a way to list sequences that are not depended upon by any other database object.

SELECT S.relname FROM pg_class S
LEFT JOIN pg_depend D ON (S.oid = D.objid AND D.deptype = 'a')
WHERE S.relkind = 'S' AND D.objid IS NULL;

Integrating with Application Logic

For developers integrating PostgreSQL sequences within application logic, obtaining sequences via SQL queries might be necessary. Query execution may vary based on the language and database driver, but the SQL logic remains the same as discussed throughout this tutorial.

Python Example:

import psycopg2

conn = psycopg2.connect('connection_string')
cursor = conn.cursor()

cursor.execute("SELECT relname FROM pg_class WHERE relkind = 'S'")
sequences = cursor.fetchall()
print(sequences)

Security Considerations

Be aware of the PostgreSQL role privileges required to perform these operations. Ensure proper database role management to grant only the necessary rights for listing or managing sequences.

Conclusion

Mastering the art of listing sequences in PostgreSQL is a valuable skill for any database professional. Whether it’s for maintenance, troubleshooting, or scripting purposes, the methods highlighted in this tutorial provide a comprehensive approach to understanding and listing database sequences. Adopt these techniques to enhance your database management tasks and ensure you’re utilizing PostgreSQL capabilities to their fullest potential.