PostgreSQL, like many relational database management systems (RDBMS), provides a dedicated schema called information_schema. This schema isn’t one you create or modify directly; it’s a standardized way to access metadata about the database itself. Think of it as a dictionary containing information about tables, columns, data types, constraints, and more. This tutorial will guide you through accessing and understanding the information_schema in PostgreSQL.
What is Metadata?
Before diving into the specifics, let’s clarify what metadata is. In the context of a database, metadata is “data about data.” It doesn’t contain the actual information stored in your tables, but rather descriptive information about the structure of those tables and the database as a whole.
Accessing the Information Schema
The information_schema is a schema like any other in PostgreSQL. You access its contents using standard SQL SELECT statements. The most fundamental table within information_schema is tables.
The information_schema.tables Table
The information_schema.tables table contains information about all tables (and other relational objects like views) in the database. Let’s look at some common columns:
table_catalog: The name of the database.table_schema: The schema the table belongs to (e.g.,public,information_schema,pg_catalog).table_name: The name of the table.table_type: Indicates the type of object (e.g.,BASE TABLE,VIEW).
Listing All Tables and Views
To list all tables and views in the database, you can use the following query:
SELECT table_name
FROM information_schema.tables;
This will return a list of all table and view names across all schemas.
Filtering by Schema
Often, you’ll want to limit the results to a specific schema. For example, to list only the tables in the public schema:
SELECT table_name
FROM information_schema.tables
WHERE table_schema = 'public';
To see just the tables within the information_schema itself:
SELECT table_name
FROM information_schema.tables
WHERE table_schema = 'information_schema';
Distinguishing Tables from Views
To get only base tables (actual data storage) and exclude views (virtual tables based on queries), use the table_type column:
SELECT table_name
FROM information_schema.tables
WHERE table_type = 'BASE TABLE';
You can combine these filters to get specific results. For example, to list all base tables in the public schema:
SELECT table_name
FROM information_schema.tables
WHERE table_schema = 'public' AND table_type = 'BASE TABLE';
Other Useful Tables in information_schema
Besides tables, information_schema contains many other useful tables:
columns: Information about the columns within each table.constraints: Details about constraints (primary keys, foreign keys, etc.).key_column_usage: Information about how keys are used.routines: Information about stored procedures and functions.
These tables can be joined and queried to gather complex metadata about your database.
Example: Finding Columns in a Specific Table
Let’s say you want to find the names and data types of all columns in a table named customers:
SELECT column_name, data_type
FROM information_schema.columns
WHERE table_name = 'customers';
Using psql Meta-Commands
The psql command-line interface provides convenient meta-commands for exploring the database.
\dt: Lists tables in the current schema.\dt *.*: Lists tables in all schemas.\z: Lists tables and their access privileges.
These commands provide a quick way to get an overview of the database structure without writing SQL queries.
By understanding and utilizing the information_schema, you gain powerful insights into the structure and metadata of your PostgreSQL database, enabling you to write more dynamic and informative applications and perform more effective database administration.