Sling Academy
Home/PostgreSQL/PostgreSQL: How to list all sequences of a database

PostgreSQL: How to list all sequences of a database

Last updated: January 05, 2024

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.

Next Article: PostgreSQL: How to share a sequence between tables

Previous Article: PostgreSQL: How to attach a sequence to a table column

Series: PostgreSQL Tutorials: From Basic to Advanced

PostgreSQL

You May Also Like

  • PostgreSQL with TimescaleDB: Querying Time-Series Data with SQL
  • PostgreSQL Full-Text Search with Boolean Operators
  • Filtering Stop Words in PostgreSQL Full-Text Search
  • PostgreSQL command-line cheat sheet
  • How to Perform Efficient Rolling Aggregations with TimescaleDB
  • PostgreSQL with TimescaleDB: Migrating from Traditional Relational Models
  • Best Practices for Maintaining PostgreSQL and TimescaleDB Databases
  • PostgreSQL with TimescaleDB: Building a High-Performance Analytics Engine
  • Integrating PostgreSQL and TimescaleDB with Machine Learning Models
  • PostgreSQL with TimescaleDB: Implementing Temporal Data Analysis
  • Combining PostgreSQL, TimescaleDB, and Airflow for Data Workflows
  • PostgreSQL with TimescaleDB: Visualizing Real-Time Data with Superset
  • Using PostgreSQL with TimescaleDB for Energy Consumption Analysis
  • PostgreSQL with TimescaleDB: How to Query Massive Datasets Efficiently
  • Best Practices for Writing Time-Series Queries in PostgreSQL with TimescaleDB
  • PostgreSQL with TimescaleDB: Implementing Batch Data Processing
  • Using PostgreSQL with TimescaleDB for Network Traffic Analysis
  • PostgreSQL with TimescaleDB: Troubleshooting Common Performance Issues
  • Building an IoT Data Pipeline with PostgreSQL and TimescaleDB