PostgreSQL Schema

Overview

A schema is a namespace within a database that contains database objects such as tables, views, indexes, and functions. Schemas allow you to organize database objects into logical groups and prevent naming conflicts.

Creating Schemas

-- Create a new schema
CREATE SCHEMA myapp;

-- Create schema with owner
CREATE SCHEMA myapp AUTHORIZATION app_user;

-- Create schema with specific objects
CREATE SCHEMA myapp
    CREATE TABLE products (...);

-- Create schema if not exists
CREATE SCHEMA IF NOT EXISTS myapp;

Dropping Schemas

-- Drop empty schema
DROP SCHEMA myapp;

-- Drop with contents
DROP SCHEMA myapp CASCADE;

-- Drop if exists
DROP SCHEMA IF EXISTS myapp;

Setting Search Path

-- Show current search path
SHOW search_path;

-- Set search path
SET search_path TO myapp, public;

-- Set default search path for user
ALTER ROLE postgres SET search_path = 'myapp, public';

Object Qualification

-- Access objects in specific schema
SELECT * FROM myapp.users;
SELECT * FROM myapp.products;

-- Create objects in specific schema
CREATE TABLE myapp.orders (...);
CREATE INDEX idx_users_name ON myapp.users(name);

Schema Information

-- List all schemas
\dn

-- List schemas with details
SELECT 
    schema_name,
    schema_owner,
    schema_acl
FROM information_schema.schemata
ORDER BY schema_name;

-- List tables in a schema
SELECT table_name 
FROM information_schema.tables 
WHERE table_schema = 'myapp';

Multi-tenant Application

-- Create schema for each tenant
CREATE SCHEMA tenant_alice;
CREATE SCHEMA tenant_bob;

-- Create tables in tenant schemas
CREATE TABLE tenant_alice.users (...);
CREATE TABLE tenant_bob.users (...);

Organization by Function

-- Create functional schemas
CREATE SCHEMA accounting;
CREATE SCHEMA inventory;
CREATE SCHEMA sales;

-- Organize tables
CREATE TABLE accounting.journal_entries (...);
CREATE TABLE inventory.warehouse_locations (...);
CREATE TABLE sales.orders (...);