When working with PostgreSQL, developers often want to introspect the structure of their database, including listing tables, understanding column types, checking constraints, discovering foreign keys, and more. While many rely on PostgreSQL’s native catalog views, such as pg_class, a more standardized and portable approach is to use the information_schema.
In this blog, we’ll explore 10 of the most important views in information_schema, with practical examples and explanations on when and why you’d use each.
What is information_schema?
The information_schema is a read-only schema in every SQL-compliant RDBMS (including PostgreSQL), containing metadata about the database. Unlike PostgreSQL’s pg_catalog, the information_schema follows SQL standards, making it portable across MySQL, MariaDB, SQL Server, and Oracle.
1. information_schema.tables
What it does:
This view lists all the tables and views in your current database. It provides their schema, name, and type (BASE TABLE or VIEW).
Why it’s useful:
* To list all user-defined tables (ignoring system tables)
* To dynamically build table dropdowns or dashboards
Example:
SELECT table_schema, table_name, table_type
FROM information_schema.tables
WHERE table_schema NOT IN ('information_schema', 'pg_catalog')
ORDER BY table_name;
Output insight:
* table_type = 'BASE TABLE': real tables
* table_type = 'VIEW': SQL views
* You’ll commonly see user tables under the public schema.
2. information_schema.columns
What it does:
Shows all column names, types, default values, nullability, and order in every table.
Why it’s useful:
* To get the structure of any table
* Useful for documentation, schema migrations, or dynamic model generation
Example:
SELECT table_name, column_name, ordinal_position, data_type, is_nullable, column_default
FROM information_schema.columns
WHERE table_schema = 'public' AND table_name = 'users';
Output insight:
* ordinal_position: the order of the column
* data_type: column type like integer, text, timestamp, etc.
* column_default: e.g., nextval('users_id_seq'::regclass) for auto-increment columns
3. information_schema.table_constraints
What it does:
Provides a list of all constraints on tables, such as PRIMARY KEY, UNIQUE, CHECK, and FOREIGN KEY.
Why it’s useful:
* To identify integrity rules applied to your data
* Great for debugging insert/update issues
Example:
SELECT constraint_name, table_name, constraint_type
FROM information_schema.table_constraints
WHERE table_schema = 'public';
Output insight:
* Helps you quickly identify all rules defined for each table
* Pairs well with key_column_usage and referential_constraints
4. information_schema.key_column_usage
What it does:
Lists which columns are involved in constraints like primary keys or foreign keys.
Why it’s useful:
* To map constraints to specific columns
* Required for generating ER diagrams or validating schema rules
Example:
SELECT table_name, column_name, constraint_name
FROM information_schema.key_column_usage
WHERE table_schema = 'public';
Output insight:
* You can trace which exact columns are governed by primary/foreign key constraints
5. information_schema.constraint_column_usage
What it does:
Tells you which columns are used by a constraint, whether they are key columns or referenced by other constraints.
Why it’s useful:
* Useful for reverse engineering relationships
* Complements key_column_usage
Example:
SELECT constraint_name, table_name, column_name
FROM information_schema.constraint_column_usage
WHERE table_schema = 'public';
6. information_schema.referential_constraints
What it does:
Focuses solely on FOREIGN KEY relationships, mapping child-parent table relationships.
Why it’s useful:
* To generate relationship diagrams or validate application-layer foreign keys
* Helps enforce and document relational integrity
Example:
SELECT constraint_name, unique_constraint_name
FROM information_schema.referential_constraints;
Output insight:
* constraint_name: FK constraint name on child table
* unique_constraint_name: corresponding PK/unique constraint on parent table
7. information_schema.views
What it does:
Lists all SQL views in your database.
Why it’s useful:
* Helps document derived tables (views)
* Useful for cleaning unused views
Example:
SELECT table_schema, table_name
FROM information_schema.views
WHERE table_schema = 'public';
8. information_schema.triggers
What it does:
Lists all table triggers, their associated event (INSERT, UPDATE, DELETE), and the function they call.
Why it’s useful:
* To debug unexpected behavior like automatic timestamp updates
* Audit and validate trigger logic in your DB
Example:
SELECT event_object_table, trigger_name, event_manipulation, action_statement
FROM information_schema.triggers
WHERE trigger_schema = 'public';
Output insight:
* Know which triggers are modifying your rows and why
9. information_schema.routines
What it does:
Shows all stored procedures and functions, along with return types and parameters.
Why it’s useful:
* Identify business logic embedded in the database
* Useful when migrating or documenting back-end functionality
Example:
SELECT routine_schema, routine_name, routine_type, data_type
FROM information_schema.routines
WHERE specific_schema = 'public';
10. information_schema.schemata
What it does:
Lists all defined schemas (namespaces) in the current database.
Why it’s useful:
* Schema separation is key to multi-tenant and modular DB design
* Good for access control and permission setup
Example:
SELECT schema_name
FROM information_schema.schemata;
Output insight:
* Identifies whether your database is organized beyond just the public schema
Why Use information_schema?
* SQL-standard: Works across multiple RDBMS systems
* Read-only: Safe to query without breaking anything
* Consistent: Easy to automate metadata exploration
* Beginner-friendly: Clear naming, great for learning
Conclusion
Mastering PostgreSQL’s information_schema opens the door to understanding your database at a deeper and more structured level. Whether you’re a database developer, a data engineer, or an application developer, this schema gives you powerful visibility into how your database is organized, without relying on vendor-specific system tables.
From discovering table names, columns, and data types to tracking relationships, constraints, views, triggers, and even stored routines, the information_schema provides a comprehensive, read-only, and SQL-standard interface to explore your database metadata. mprove your data analysis workflows. This article, "How to Use Table Sampling in PostgreSQL," will guide you through effectively leveraging this feature for quicker, more resource-friendly data exploration.