PostgreSQL Information_Schema Guide for Database Management

Introduction

If you are involved in database management tasks such as creating tables, viewing relationships, or even generating reports, understanding the PostgreSQL information_schema can be very useful. In this tutorial, we will explore the fundamentals of the PostgreSQL information_schema with some code examples.

This tutorial is written primarily for PostgresSQL, though some concepts may be applicable to other SQL relational databases.

Before you keep reading, I just wanted to let you know about our modern and fast PostgreSQL database manager, Beekeeper Studio. It makes exploring schemas a breeze, and it’s free to use for PostgreSQL, download it here.

What is the Information_Schema?

The information_schema is a critical feature in PostgreSQL databases that provides access to the ‘current database’ metadata. It is essentially a collection of views that contain information on various objects in the ‘current database”. From tables to columns to constraints, PostgreSQL’s information_schema provides the detaiils of a database’s architecture.

SELECT *
FROM information_schema.tables;

Running the above command in the SQL editor of Beekeeper Studio, for example, should present you with a list of tables in your current database.

Digging Deeper

Viewing public tables

To list all the available public tables in your current database, simply run:

SELECT table_name 
FROM information_schema.tables 
WHERE table_schema='public';

This command will return a list of all the table names in your ‘current database’ in the ‘public’ schema.

Output using the ‘chinook’ sample database

Inspecting Columns

Use the information_schema.columns view to extract detailed information about the columns of a particular table:

SELECT column_name, data_type 
FROM information_schema.columns 
WHERE table_name ='album';

This code will return a list of all column names and their data types for your selected table.

Output using the ‘chinook’ sample database

Displaying Constraints

The information_schema.table_constraints view provides information about the constraints defined in the table.
You can use the following query to fetch constraints for a specific table:

SELECT constraint_name, constraint_type 
FROM information_schema.table_constraints 
WHERE table_name='album';

Running the command will output constraint names and types for ‘your_table’, giving you insights about the rules enforced on this table.

Output using the ‘chinook’ sample database

Conclusion

The PostgreSQL information_schema is an important tool for exploring and understanding the structure and details of your database. How you utilize it depends on your specific needs and database configuration. Remember to replace the sample table and column names with those specific to your database for the sample commands to execute correctly.

Note: All the examples provided in this tutorial are best suited for PostgreSQL (9.4 to 13.3 versions) and may not be compatible with other database engines. Always ensure to tailor your queries to align with the database engine you’re using. Binary nature of the PostgreSQL information_schema can make your database exploration tasks easier, more effective and much less time-consuming.