Connecting with your Database with the Information Schema

Table Of Contents

• Intro
• The Information Schema
• System Views
• System Catalog
• Putting It All Together

Intro

If you've worked with SQL before, you're familiar with writing queries to ask questions about the data in your database. Give me all the people that user A follows. Tell me how many sales we made in the last week.

Databases are good for asking questions about your data, but they also store metadata about the data in your database. PostgreSQL in particular stores data about Table names, schemas, indexes, views and much more. And it's just a few sql queries away from you.

Querying Metadata in PostgreSQL

PostgreSQL stores metadata for the database in 3 areas: The Information Schema, System Views, and System Catalogs.

The Information Schema

Postgres' Information Schema "consists of a set of views that contain information about the objects defined in the current database." A database schema is essentially a way to put tables into their own group. By default, any tables you create in Postgres are part of a database schema called "public".

Though you'll typically query your tables like:

SELECT * FROM my_table LIMIT 10; 

You can also include the schema as part of your query like so:

SELECT * FROM public.my_table LIMIT 10; 

The information schema, as its name suggests, is a schema in your Postgres Database with tables that store data about the data in your database. You can write queries against the tables in this schema to learn more about your database(s).

You can find the full list of tables in the information schema in the Postgres Docs, but I'm sharing a few that are handy.

-- Find all the schemas in your database select distinct table_schema from information_schema.tables; 

-- Find all the tables in your schema select table_name from information_schema.tables where table_schema = 'public'; 

-- Information about the columns in a table select column_name, data_type, column_default, is_nullable from information_schema.columns where table_name = 'sales'; 

-- Name of all the constraints for a given table select * from information_schema.constraint_column_usage where table_schema ='public' and table_name ='sales'; 

System Views

Postgres System Views are a collection of views that also have metadata. These tables are listed as system views. All of them are part of the pg_catalog schema.

-- Find indexes in a schema select tablename, indexname, indexdef FROM pg_catalog.pg_indexes where schemaname = 'public'; 

-- Find indexes for a table select tablename, indexname, indexdef FROM pg_catalog.pg_indexes where schemaname = 'public' AND tablename='mytable'; 

You can also list materialized views in your database with:

select schemaname, matviewname, ispopulated, definition FROM pg_matviews where schemaname='public'; 

System Catalogs

"The system catalogs are the place where a relational database management system stores schema metadata, such as information about tables and columns, and internal bookkeeping information."

Like Systems Views, the system catalog tables are also part of the pg_catalog schema. This schema, just like the information_schema exists by default in your Postgres database.

If I want some estimates about the size of a table, the System Catalog has a table, pg_class that lets me inspect the table size and other attributes:

select relname, relpages, reltuples FROM pg_catalog.pg_class WHERE relname='sales'; 

Output:

 relname | relpages | reltuples ---------------+----------+----------- sales | 8 | 568 

Or, with some extra functions, you can calculate the size of your tables and indexes*:

SELECT relname, pg_relation_size(oid) as bytes, pg_size_pretty(pg_relation_size(oid)) FROM pg_class WHERE relnamespace = 'public'::regnamespace ORDER BY relname; 

*Query from Designing high-performance time series data tables on Amazon RDS for PostgreSQL

 relname | bytes | pg_size_pretty --------------+---------+---------------- sales | 1638400 | 1600 kB sales_id_seq | 8192 | 8192 bytes 

Putting It All Together

Queries can be really powerful when you join tables across these schemas.
For example, you can list all of the constraints of a table. Here's a query
that will give you all of the constraints for a given table in your database.

select table_name, column_name, constraint_name, case when pgcc.contype = 'p' then 'Primary Key Constraint' when pgcc.contype = 'f' then 'Foreign Key Constraint' when pgcc.contype = 'c' then 'Check Constraint' end as constraint_type , pg_get_constraintdef(pgcc."oid" , true) as constraint_definition from information_schema.constraint_column_usage iccu inner join pg_catalog.pg_constraint pgcc on iccu.constraint_name = pgcc.conname where table_name = 'parent'; 

Output:

 table_name | column_name | constraint_name | constraint_type | constraint_definition ------------+-------------+------------------------------+------------------------+----------------------------------------------- parent | id | parent_pkey | Primary Key Constraint | PRIMARY KEY (id) parent | id | child_parent_id_fkey | Foreign Key Constraint | FOREIGN KEY (parent_id) REFERENCES parent(id) parent | parent_name | name_less_than_20_characters | Check Constraint | CHECK (length(parent_name::text) <= 20) 

If you want to find more information about the Information Schema or other Postgres System Tables you can find them here:
Information Schema
System Catalogs
System Views