DEV Community

Harry Douglas
Harry Douglas

Posted on

What is Information Schema in PostgreSQL?

#database #postgres

In PostgreSQL, information_schema is an ANSI/ISO SQL-standard, read-only set of views containing metadata about your database objects.

Think of it as the database's self-aware blueprint. Instead of remembering exact system table layouts, you can query information_schema using standard SQL SELECT statements to inspect tables, columns, data types, privileges, constraints, and more.

Key Benefits

  • SQL Standardized: Because information_schema follows ANSI standards, the queries you write against it in PostgreSQL will often work in other SQL databases like MySQL, SQL Server, and MariaDB with little to no modification.
  • Permission-Aware: It automatically filters results. You will only see metadata for database objects that your current database user account actually has permission to access.
  • Stability: Unlike internal system catalogs, the structure of information_schema views remains consistent across PostgreSQL versions.

Frequently Used Views

View Name Description
information_schema.tables Lists all tables and views in the database.
information_schema.columns Contains details on columns (names, data types, default values, nullability).
information_schema.table_constraints Lists primary keys, foreign keys, unique constraints, and check constraints.
information_schema.schemata Lists available schemas in the current database.
information_schema.routines Details functions and stored procedures.
information_schema.views Shows view definitions and properties.

Practical Examples

1. List all user tables in the public schema 

SELECT table_name 
FROM information_schema.tables 
WHERE table_schema = 'public' 
  AND table_type = 'BASE TABLE';
Enter fullscreen mode Exit fullscreen mode

2. View all columns and data types for a specific table 

SELECT column_name, data_type, is_nullable, column_default
FROM information_schema.columns
WHERE table_schema = 'public' 
  AND table_name = 'users'
ORDER BY ordinal_position;
Enter fullscreen mode Exit fullscreen mode

3. Find primary key constraints 

SELECT table_name, constraint_name
FROM information_schema.table_constraints
WHERE constraint_type = 'PRIMARY KEY'
  AND table_schema = 'public';
Enter fullscreen mode Exit fullscreen mode

information_schema vs. pg_catalog

PostgreSQL actually stores all of its system data in a native schema called pg_catalog (e.g., pg_class, pg_attribute). The information_schema views are built on top of pg_catalog.

  • Use information_schema when you want portable, standard SQL or straightforward details like column names and data types.
  • Use pg_catalog when you need deep, PostgreSQL-specific metadata (such as indexes, tablespaces, WAL details, or custom ENUM types) that the generic ANSI SQL standard doesn't cover.

Top comments (0)