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.
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.