April 9, 2024 By Matthew Rathbone *

Exploring PostgreSQL Information_Schema

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.

A Database Manager That Is Modern, Fast, & Easy To Use

Tried a few tools. Beekeeper was the only one that I found that felt right. Most had a very 1990's feel to them - Allan

I built Beekeeper Studio because, like Allan, I wanted something more intuitive and modern than all the existing clunky apps I could find. My customers agree - they love using Beekeeper and they tell me every day! Give it a try, I bet you'll like it too.

Beekeeper's Linux version is 100% full-featured, no cut corners, no feature compromises.

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

table_name
artist
album
employee
customer
invoice
invoice_line
track
playlist
playlist_track
genre
media_type

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

column_name data_type
album_id integer
artist_id integer
title character varying

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

constraint_name constraint_type
album_pkey PRIMARY KEY
album_artist_id_fkey FOREIGN KEY
2200_65375_1_not_null CHECK
2200_65375_2_not_null CHECK
2200_65375_3_not_null CHECK

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.