A Deep Dive Into the PostgreSQL Information_Schema

In this article, we are going to delve into one of the in-built systems within PostgreSQL known as the information_schema.

What is Information_Schema?

Information_schema is a system catalog that forms a standard set of views which contains information about the PostgreSQL database objects, essentially, it’s a meta-database that holds information about your current database. It’s present in all standards-compliant SQL database engines which makes it quite useful when migrating.

It must be noted that the information_schema is read-only and does not support the insertion of new information or alteration of existing data. Let’s start exploring different views within the information_schema that can be instrumental to users for various practices.

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

The code is compatible with all PostgreSQL versions.
Upon execution, this code will yield a list of all views available to users within the information_schema.

Why Use Information_Schema?

The information_schema is significantly crucial when you are trying to work more effectively with database structures, primarily, when relating to:

• Tables
• Columns
• Indexes
• Triggers
• Constraints
• Schemas

Information_Schema.Tables

The information_schema.tables view contains details about all tables in the connected database.

SELECT table_name 
FROM information_schema.tables 
WHERE table_type = 'BASE TABLE' 
AND table_schema NOT IN ('pg_catalog', 'information_schema');

The code is compatible with all PostgreSQL versions. The output here will be a list of all base tables within your database.

Information_Schema.Columns

The information_schema.columns view is where you can fetch details about column specifics in your database.

SELECT table_name, column_name, data_type 
FROM information_schema.columns 
WHERE table_schema NOT IN ('pg_catalog', 'information_schema')
AND table_name   = 'your_table';

The code is compatible with any PostgreSQL version. Replace ‘your_table’ with your table name to get a list of all columns with their data types within the specified table.

Information_Schema.Triggers

Information about triggers can be helpful when debugging or in understanding dependencies. The information_schema.triggers view is used to fetch details about triggers linked to the database.

SELECT event_object_table, trigger_name, action_statement, action_timing 
FROM information_schema.triggers;

This code configuration, compatible with all PostgreSQL versions, will yield the table name, trigger name, the trigger action, and its timing.

Information_Schema.Key_Column_Usage

The information_schema.key_column_usage view provides information making it easier to understand foreign-key relationships in your database.

SELECT constraint_name, table_name, column_name, ordinal_position
FROM information_schema.key_column_usage
WHERE table_name = 'your_table';

Replace ‘your_table’ with your table name and execute the code compatible with all PostgreSQL versions, to get the constraint_name, table_name, column_name, and ordinal_position for every key in the specified table.

Conclusion

In this tutorial, we unravelled the details about PostgreSQL’s information_schema. By leveraging the views in the information_schema, you can attain enhanced control and derive sharper insights about your database’s structure and elements. If you are moving to PostgreSQL from another SQL database, you will find information_schema views particularly handy since they are a standard part of SQL.

Your adventure with PostgreSQL does not end here. Stay tuned for upcoming guides delving deeper into the vast universe of PostgreSQL, where we would cover more advanced topics, tips and tricks.

---