PostgreSQL schemas are a way to organize and namespace database objects (like tables, views, functions) within a single database. Think of them as folders or namespaces that help organize your database structure.

Default schema

Every PostgreSQL database starts with a public schema by default. When you create tables without specifying a schema, they go into public.

Multiple schemas in one database

-- Create schemas
CREATE SCHEMA sales;
CREATE SCHEMA inventory;
CREATE SCHEMA hr;

-- Create tables in different schemas
CREATE TABLE sales.customers (id int, name text);
CREATE TABLE sales.orders (id int, customer_id int);
CREATE TABLE inventory.products (id int, name text, stock int);
CREATE TABLE hr.employees (id int, name text, department text);

Accessing tables

-- Full qualification (schema.table)
SELECT * FROM sales.customers;
SELECT * FROM inventory.products;

-- If you're in the public schema, you can omit it
SELECT * FROM customers;  -- assumes public.customers

-- List tables in a specific schema
SELECT table_name
FROM information_schema.tables
WHERE table_schema = 'public'
AND table_type = 'BASE TABLE';

Why use schemas?

Organization: Group related tables together Security: Control access per schema Multi-tenancy: Different schemas for different clients Avoid naming conflicts: You can have sales.products and inventory.products Development stages: dev, staging, prod schemas in one database

End of day 5

Today we've learned about schemas and why they are useful. This knowledge will become handy when we start working with postgrest. Stay tuned!

Tomorrow we will start exploring roles in postgres.