By the end of this lesson you can query information_schema to list a table's columns, types and nullability, and find its primary and foreign keys.
Why it matters: On a new job you'll face databases with dozens or hundreds of tables and no one to explain them. Reading the schema (and querying it) is how you find where data lives and how tables connect before you write a single report.
Dataset: Starter shop. Examples run in the editor on the right.
A database's schema is its blueprint: the list of tables, their columns and data types, and the rules (constraints) that keep the data valid, including primary and foreign keys. The data changes every minute; the schema changes rarely.
You can read a schema in three ways:
- A schema panel or diagram in your tool (the panel beside the editor here).
- Documentation, if someone wrote it.
- The database itself. Every SQL database describes its own structure in special read-only views. In PostgreSQL (and in MySQL and SQL Server) the standard set is called
information_schema.
The third way always works and is always up to date, so it's worth learning.
Which tables exist?
information_schema.tables has one row per table. The dot in the name means the tables view inside the information_schema schema. It also lists PostgreSQL's own internal tables, so we keep only the ones in public, the schema where your data lives.
SELECT table_name, table_type
FROM information_schema.tables
WHERE table_schema = 'public';Three rows: customers, orders and products, each a BASE TABLE (a real stored table, as opposed to a view). The text value 'public' is in single quotes because it's a piece of text, not a column name.
Which columns does a table have?
information_schema.columns has one row per column of every table. The most useful of its columns are:
| column | meaning |
|---|---|
table_name | the table the column belongs to |
column_name | the column's name |
data_type | integer, numeric, text, date … |
is_nullable | YES if the column may hold NULL, NO if a value is required |
ordinal_position | the column's position in the table (1, 2, 3 …) |
The next example ends with ORDER BY ordinal_position. ORDER BY sorts the result; here it lists the columns in the same order as the table defines them. You'll study sorting properly in the next track.
SELECT column_name, data_type, is_nullable
FROM information_schema.columns
WHERE table_name = 'customers'
ORDER BY ordinal_position;Five rows, in table order thanks to ORDER BY ordinal_position. city and email say YES under is_nullable: they're allowed to be missing, which is why you saw NULLs in them. customer_id, customer_name and joined_on are required (NO).
is_nullable is a promise from the schema: a NO column can never contain NULL, so you never need to worry about missing values there. A YES column might contain NULL, so any report built on it has to decide what to do with blanks.
Where are the keys?
Keys are constraints: rules the database enforces. information_schema.table_constraints lists them, one row per constraint, with a constraint_type of PRIMARY KEY, FOREIGN KEY, UNIQUE or CHECK. PostgreSQL also lists every not null rule as a CHECK constraint, so expect a few of those.
SELECT constraint_name, constraint_type
FROM information_schema.table_constraints
WHERE table_name = 'orders';Among the CHECK rules (for example quantity must be above zero, status must be one of four words) you'll find orders_pkey, the PRIMARY KEY, and two FOREIGN KEYs: orders_customer_id_fkey and orders_product_id_fkey. PostgreSQL names constraints table_column_kind, which makes them easy to read.
A constraint name hints at its column, but to be certain which column a key uses, ask information_schema.key_column_usage: one row per (constraint, column) pair.
SELECT table_name, constraint_name, column_name
FROM information_schema.key_column_usage
WHERE table_schema = 'public';Five rows: one primary key column for each of the three tables, plus the two foreign key columns in orders (customer_id and product_id). That's the whole map of how the starter tables connect.
Drawing the relationships
Putting it together, the starter schema is:
customers 1 ──< * orders * >── 1 productsRead it as: one customer has many orders; one product appears in many orders; each order has exactly one customer and one product. In entity-relationship (ER) diagrams the "many" end is often drawn as a crow's foot. Whatever the notation, the arrow always runs along a foreign key.