Database_objects
Chapter 4: Database Objects & Concepts
Section titled “Chapter 4: Database Objects & Concepts”Understanding PostgreSQL Database Objects
Section titled “Understanding PostgreSQL Database Objects”4.1 Overview of Database Objects
Section titled “4.1 Overview of Database Objects”PostgreSQL organizes data into a hierarchical structure of database objects. Understanding these objects is fundamental to effective database design and usage.
PostgreSQL Database Object Hierarchy ========================================================================
┌───────────────┐ │ Database │ │ Cluster │ │ (PGDATA) │ └───────┬───────┘ │ ▼ ┌─────────────────────────────────────────────────────────────────────┐ │ DATABASE │ │ │ │ ┌───────────────────────────────────────────────────────────────┐ │ │ │ SCHEMA (public) │ │ │ │ │ │ │ │ ┌─────────┐ ┌─────────┐ ┌─────────┐ ┌─────────┐ │ │ │ │ │ TABLE │ │ VIEW │ │ INDEX │ │ SEQUENCE│ │ │ │ │ │ │ │ │ │ │ │ │ │ │ │ │ │-users │ │user_sum │ │users_ │ │ users_ │ │ │ │ │ │-orders│ │order_det│ │ pkey │ │ id_seq │ │ │ │ │ │-products│ │ │ │ │ │ │ │ │ │ └─────────┘ └─────────┘ └─────────┘ └─────────┘ │ │ │ │ │ │ │ │ ┌─────────┐ ┌─────────┐ ┌─────────┐ ┌─────────┐ │ │ │ │ │FUNCTION│ │PROCEDURE│ │ TRIGGER │ │ TYPE │ │ │ │ │ │ │ │ │ │ │ │ │ │ │ │ │ │get_user│ │process_ │ │audit_ │ │address │ │ │ │ │ │ │ │order │ │trigger │ │ │ │ │ │ │ └─────────┘ └─────────┘ └─────────┘ └─────────┘ │ │ │ │ │ │ │ │ ┌──────────────────────────────────────────────────────┐ │ │ │ │ │ CONSTRAINTS │ │ │ │ │ │ │ │ │ │ │ │ • PRIMARY KEY (users_pkey) │ │ │ │ │ │ • FOREIGN KEY (orders_user_fk) │ │ │ │ │ │ • UNIQUE (users_email_key) │ │ │ │ │ │ • NOT NULL (users.name) │ │ │ │ │ │ • CHECK (products.price > 0) │ │ │ │ │ │ • EXCLUDE (booking_conflict) │ │ │ │ │ └──────────────────────────────────────────────────────┘ │ │ │ │ │ │ │ └───────────────────────────────────────────────────────────────┘ │ │ │ └─────────────────────────────────────────────────────────────────────┘ ========================================================================4.2 Database
Section titled “4.2 Database”A database is a container that holds related data. Each PostgreSQL cluster can contain multiple databases.
Working with Databases ========================================================================
Creating a Database: =====================
-- Basic database creation CREATE DATABASE myapp;
-- With specific parameters CREATE DATABASE myapp OWNER = postgres TEMPLATE = template0 ENCODING = 'UTF8' LC_COLLATE = 'en_US.UTF-8' LC_CTYPE = 'en_US.UTF-8' TABLESPACE = pg_default CONNECTION LIMIT = 100;
Database Attributes: ====================
┌─────────────────────────────────────────────────────────────────────┐ │ Attribute │ Description │ ├─────────────────────┼─────────────────────────────────────────────┤ │ owner │ User who owns the database │ ├─────────────────────┼─────────────────────────────────────────────┤ │ template │ Template used for creation │ ├─────────────────────┼─────────────────────────────────────────────┤ │ encoding │ Character encoding (UTF8, SQL_ASCII) │ ├─────────────────────┼─────────────────────────────────────────────┤ │ lc_collate │ Sort order │ ├─────────────────────┼─────────────────────────────────────────────┤ │ lc_ctype │ Character classification │ ├─────────────────────┼─────────────────────────────────────────────┤ │ tablespace │ Default tablespace │ ├─────────────────────┼─────────────────────────────────────────────┤ │ connection_limit │ Max concurrent connections (-1 = unlimited) │ └─────────────────────┴─────────────────────────────────────────────┘
Managing Databases: ===================
-- List all databases \l SELECT datname, pg_size_pretty(pg_database_size(datname)) AS size FROM pg_database;
-- Connect to database \c database_name
-- Rename database ALTER DATABASE myapp RENAME TO myapp_prod;
-- Change owner ALTER DATABASE myapp OWNER TO new_owner;
-- Drop database (must be disconnected) DROP DATABASE myapp; ```
---
## 4.3 Schema
Schemas are logical containers within a database that hold database objects. Schema Operations========================================================================
Default Schemas:================
┌─────────────────────────────────────────────────────────────────────┐│ Schema Name │ Description │├──────────────────┼──────────────────────────────────────────────────┤│ pg_catalog │ System catalog (tables, indexes, etc.) │├──────────────────┼──────────────────────────────────────────────────┤│ pg_toast │ TOAST storage for large objects │├──────────────────┼──────────────────────────────────────────────────┤│ information_schema│ Standard SQL information schema │├──────────────────┼──────────────────────────────────────────────────┤│ public │ Default user schema │└──────────────────┴──────────────────────────────────────────────────┘
Creating and Managing Schemas:==============================
-- Create a new schemaCREATE SCHEMA sales;
-- Create schema with specific ownerCREATE SCHEMA analytics AUTHORIZATION analytics_user;
-- Create schema with multiple objectsCREATE SCHEMA inventory CREATE TABLE products (...) CREATE TABLE stock (...);
-- List schemas\dnSELECT schema_name FROM information_schema.schemata;
-- Set search path (order of schema lookup)SHOW search_path;SET search_path TO sales, public;
-- Permanent setting in postgresql.conf-- search_path = '"$user", public'
-- Rename schemaALTER SCHEMA sales RENAME TO sales_data;
-- Change ownerALTER SCHEMA sales OWNER TO new_owner;
-- Drop schema (CASCADE removes all objects)DROP SCHEMA sales CASCADE;
Schema Usage Pattern:=====================
┌─────────────────────────────────────────────────────────────────────┐│ Multi-Tenant Application Schema Design ││ ││ ┌─────────────────────────────────────────────────────────────┐ ││ │ tenant_a schema tenant_b schema │ ││ │ ┌──────────────────┐ ┌──────────────────┐ │ ││ │ │ - users │ │ - users │ │ ││ │ │ - orders │ │ - orders │ │ ││ │ │ - products │ │ - products │ │ ││ │ └──────────────────┘ └──────────────────┘ │ ││ └─────────────────────────────────────────────────────────────┘ ││ ││ Benefits: ││ - Logical separation of tenant data ││ - Easy backup/restore per tenant ││ - Different permissions per schema │└─────────────────────────────────────────────────────────────────────┘```4.4 Tables
Section titled “4.4 Tables”Tables are the fundamental storage units in PostgreSQL. They contain rows of data organized into columns.
Table Operations ========================================================================
Creating a Basic Table: ======================
CREATE TABLE users ( id SERIAL PRIMARY KEY, username VARCHAR(50) NOT NULL, email VARCHAR(255) UNIQUE NOT NULL, password_hash VARCHAR(255) NOT NULL, created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP, updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP, is_active BOOLEAN DEFAULT TRUE );
Table with Foreign Key: ======================
CREATE TABLE orders ( id SERIAL PRIMARY KEY, user_id INTEGER NOT NULL REFERENCES users(id), total_amount DECIMAL(10,2) NOT NULL, status VARCHAR(20) DEFAULT 'pending', created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP, CONSTRAINT valid_amount CHECK (total_amount >= 0) );
Temporary Table: ===============
-- Session temporary table CREATE TEMP TABLE temp_results ( id INTEGER, value TEXT );
-- Transaction-scoped temporary table CREATE TEMP TABLE temp_results (...) ON COMMIT DELETE ROWS;
-- Options: PRESERVE ROWS (default), DELETE ROWS, DROP
Unlogged Table (for caching): =============================
CREATE UNLOGGED TABLE session_cache ( session_id VARCHAR(100) PRIMARY KEY, data JSONB, expires_at TIMESTAMP );
Table Constraints: =================
┌─────────────────────────────────────────────────────────────────────┐ │ Constraint Type │ Purpose │ ├─────────────────────┼─────────────────────────────────────────────┤ │ PRIMARY KEY │ Unique identifier, NOT NULL │ ├─────────────────────┼─────────────────────────────────────────────┤ │ FOREIGN KEY │ Reference to another table │ ├─────────────────────┼─────────────────────────────────────────────┤ │ UNIQUE │ No duplicate values │ ├─────────────────────┼─────────────────────────────────────────────┤ │ NOT NULL │ Required value │ ├─────────────────────┼─────────────────────────────────────────────┤ │ CHECK │ Custom validation rule │ ├─────────────────────┼─────────────────────────────────────────────┤ │ EXCLUDE │ Prevent overlapping values/ranges │ └─────────────────────┴─────────────────────────────────────────────┘
Managing Tables: ===============
-- List tables \dt SELECT * FROM pg_tables WHERE schemaname = 'public';
-- Describe table structure \d users SELECT column_name, data_type, is_nullable, column_default FROM information_schema.columns WHERE table_name = 'users';
-- Add column ALTER TABLE users ADD COLUMN phone VARCHAR(20);
-- Remove column ALTER TABLE users DROP COLUMN phone;
-- Rename table ALTER TABLE users RENAME TO app_users;
-- Add constraint ALTER TABLE orders ADD CONSTRAINT fk_user FOREIGN KEY (user_id) REFERENCES users(id);
-- Remove constraint ALTER TABLE orders DROP CONSTRAINT fk_user;
-- Change owner ALTER TABLE users OWNER TO new_owner; ```
---
## 4.5 Indexes
Indexes are database objects that improve query performance by providing quick data access paths. Index Operations========================================================================
Creating Indexes:================
-- Basic B-tree index (default)CREATE INDEX idx_users_email ON users(email);
-- Composite indexCREATE INDEX idx_orders_user_status ON orders(user_id, status);
-- Unique indexCREATE UNIQUE INDEX idx_users_username ON users(username);
-- Index with WHERE clause (partial index)CREATE INDEX idx_orders_pending ON orders(user_id) WHERE status = 'pending';
-- Index on expressionCREATE INDEX idx_users_lower_email ON users(LOWER(email));
-- Index using specific index typeCREATE INDEX idx_users_email_hash ON users USING HASH (email);
Index Types:============
┌─────────────────────────────────────────────────────────────────────┐│ Index Type │ Best For │├─────────────────┼──────────────────────────────────────────────────┤│ B-tree │ Equality and range queries (=, <, >, <=, >=) │├─────────────────┼──────────────────────────────────────────────────┤│ Hash │ Simple equality (=) only │├─────────────────┼──────────────────────────────────────────────────┤│ GiST │ Geometric, full-text search, GIS data │├─────────────────┼──────────────────────────────────────────────────┤│ GIN │ Array, JSONB, full-text search │├─────────────────┼──────────────────────────────────────────────────┤│ BRIN │ Large tables with sequential data │├─────────────────┼──────────────────────────────────────────────────┤│ SP-GiST │ Large datasets with non-balanced trees │└─────────────────┴──────────────────────────────────────────────────┘
Managing Indexes:=================
-- List indexes on a table\d usersSELECT indexname, indexdef FROM pg_indexes WHERE tablename = 'users';
-- Reindex (rebuild index)REINDEX INDEX idx_users_email;REINDEX TABLE users;
-- Rename indexALTER INDEX idx_users_email RENAME TO idx_users_email_unique;
-- Drop indexDROP INDEX IF EXISTS idx_users_email;
-- Concurrent index creation (for production)CREATE INDEX CONCURRENTLY idx_users_phone ON users(phone);```4.6 Views
Section titled “4.6 Views”Views are virtual tables based on the result of a query. They don’t store data physically.
View Operations ========================================================================
Creating Views: ===============
-- Simple view CREATE VIEW active_users AS SELECT id, username, email FROM users WHERE is_active = TRUE;
-- Complex view with joins CREATE VIEW order_summary AS SELECT u.id AS user_id, u.username, COUNT(o.id) AS order_count, COALESCE(SUM(o.total_amount), 0) AS total_spent FROM users u LEFT JOIN orders o ON u.id = o.user_id GROUP BY u.id, u.username;
-- View with check option CREATE VIEW pending_orders AS SELECT * FROM orders WHERE status = 'pending' WITH CHECK OPTION;
-- Updatable view CREATE VIEW user_orders AS SELECT u.id, u.username, o.id AS order_id, o.total_amount FROM users u JOIN orders o ON u.id = o.user_id;
Managing Views: ==============
-- List views \dv SELECT viewname FROM pg_views WHERE schemaname = 'public';
-- View definition SELECT pg_get_viewdef('view_name', true);
-- Update view CREATE OR REPLACE VIEW active_users AS SELECT id, username, email, created_at FROM users WHERE is_active = TRUE AND created_at > '2024-01-01';
-- Drop view DROP VIEW IF EXISTS active_users;
Security with Views: ====================
┌─────────────────────────────────────────────────────────────────────┐ │ Using Views for Security │ │ │ │ -- Grant access through view, not underlying table │ │ CREATE VIEW user_contact_info AS │ │ SELECT username, email, phone FROM users; │ │ │ │ GRANT SELECT ON user_contact_info TO readonly_user; │ │ │ │ -- This prevents access to sensitive columns │ │ -- like password_hash, is_active, etc. │ └─────────────────────────────────────────────────────────────────────┘ ```
---
## 4.7 Sequences
Sequences are database objects that generate auto-incrementing numbers, commonly used for primary keys. Sequence Operations========================================================================
Creating Sequences:===================
-- Basic sequenceCREATE SEQUENCE user_id_seq;
-- With optionsCREATE SEQUENCE order_id_seq START WITH 1000 INCREMENT BY 1 MINVALUE 1000 MAXVALUE 999999999 CYCLE CACHE 10;
Using Sequences:===============
-- Get next valueSELECT nextval('order_id_seq');
-- Get current value (without advancing)SELECT currval('order_id_seq');
-- Set sequence valueSELECT setval('order_id_seq', 5000);
-- In INSERTINSERT INTO orders (id, user_id, total_amount)VALUES (nextval('order_id_seq'), 1, 100.00);
-- Using SERIAL (auto-creates sequence)CREATE TABLE users ( id SERIAL PRIMARY KEY, -- Creates: user_id_seq name TEXT);
-- Or use GENERATED ALWAYS AS IDENTITYCREATE TABLE users ( id BIGINT GENERATED ALWAYS AS IDENTITY PRIMARY KEY, name TEXT);
Managing Sequences:===================
-- Alter sequenceALTER SEQUENCE order_id_seq RESTART WITH 1000;ALTER SEQUENCE order_id_seq INCREMENT BY 10;ALTER SEQUENCE order_id_seq CACHE 100;
-- Get sequence informationSELECT * FROM sequence_name;SELECT last_value FROM order_id_seq;
-- Drop sequenceDROP SEQUENCE IF EXISTS order_id_seq;```4.8 Functions and Procedures
Section titled “4.8 Functions and Procedures”Functions and procedures are reusable code blocks that perform operations in the database.
Functions and Procedures ========================================================================
Creating Functions: ===================
-- Simple function returning scalar CREATE FUNCTION get_user_count() RETURNS INTEGER AS $$ BEGIN RETURN (SELECT COUNT(*) FROM users); END; $$ LANGUAGE plpgsql;
-- Function with parameters CREATE FUNCTION get_user_by_id(user_id INTEGER) RETURNS users AS $$ BEGIN RETURN (SELECT * FROM users WHERE id = user_id); END; $$ LANGUAGE plpgsql;
-- Function returning table CREATE FUNCTION get_active_users() RETURNS TABLE ( id INTEGER, username VARCHAR(50), email VARCHAR(255) ) AS $$ BEGIN RETURN QUERY SELECT u.id, u.username, u.email FROM users u WHERE u.is_active = TRUE; END; $$ LANGUAGE plpgsql;
Creating Procedures (PostgreSQL 11+): ======================================
CREATE PROCEDURE create_order( p_user_id INTEGER, p_amount DECIMAL(10,2) ) AS $$ BEGIN INSERT INTO orders (user_id, total_amount, status) VALUES (p_user_id, p_amount, 'pending');
UPDATE users SET order_count = order_count + 1 WHERE id = p_user_id; END; $$ LANGUAGE plpgsql;
Calling Procedures: ===================
CALL create_order(1, 150.00);
Invoking Functions: ===================
SELECT get_user_count(); SELECT * FROM get_user_by_id(1); SELECT * FROM get_active_users();
Managing Functions/Procedures: ==============================
-- List functions \df SELECT proname, prosrc FROM pg_proc WHERE pronamespace = 'public'::regnamespace;
-- Drop function DROP FUNCTION get_user_count(); DROP FUNCTION get_user_by_id(INTEGER); DROP PROCEDURE create_order(INTEGER, DECIMAL); ```
---
## 4.9 Triggers
Triggers are database objects that automatically execute specified actions when certain events occur. Trigger Operations========================================================================
Creating Triggers:==================
-- Function to be triggeredCREATE OR REPLACE FUNCTION update_timestamp()RETURNS TRIGGER AS $$BEGIN NEW.updated_at = CURRENT_TIMESTAMP; RETURN NEW;END;$$ LANGUAGE plpgsql;
-- Trigger on UPDATECREATE TRIGGER trigger_users_updated BEFORE UPDATE ON users FOR EACH ROW EXECUTE FUNCTION update_timestamp();
-- Trigger with specific timingCREATE TRIGGER trigger_orders_insert AFTER INSERT ON orders FOR EACH ROW EXECUTE FUNCTION log_order_insert();
Trigger Timing:===============
┌─────────────────────────────────────────────────────────────────────┐│ Timing │ When Trigger Fires │├─────────────────┼──────────────────────────────────────────────────┤│ BEFORE │ Before the operation is performed │├─────────────────┼──────────────────────────────────────────────────┤│ AFTER │ After the operation is performed │├─────────────────┼──────────────────────────────────────────────────┤│ INSTEAD OF │ Instead of the operation (for views) │└─────────────────┴──────────────────────────────────────────────────┘
Trigger Events:===============
┌─────────────────────────────────────────────────────────────────────┐│ Event │ Available For │├───────────────┼─────────────────────────────────────────────────────┤│ INSERT │ Tables, Views │├───────────────┼─────────────────────────────────────────────────────┤│ UPDATE │ Tables, Views │├───────────────┼─────────────────────────────────────────────────────┤│ DELETE │ Tables, Views │├───────────────┼─────────────────────────────────────────────────────┤│ TRUNCATE │ Tables only │└───────────────┴─────────────────────────────────────────────────────┘
Managing Triggers:==================
-- List triggers on table\d usersSELECT trigger_name, event_manipulation, action_timing, action_statementFROM information_schema.triggersWHERE event_object_table = 'users';
-- Disable triggerALTER TABLE users DISABLE TRIGGER trigger_users_updated;
-- Enable triggerALTER TABLE users ENABLE TRIGGER trigger_users_updated;
-- Drop triggerDROP TRIGGER IF EXISTS trigger_users_updated ON users;```4.10 Types
Section titled “4.10 Types”PostgreSQL allows creating custom data types for specialized data representation.
Custom Types ========================================================================
Creating Composite Types: ==========================
-- Create type for address CREATE TYPE address AS ( street VARCHAR(255), city VARCHAR(100), state VARCHAR(50), zip_code VARCHAR(20), country VARCHAR(50) );
-- Using composite type in table CREATE TABLE customers ( id SERIAL PRIMARY KEY, name VARCHAR(100), billing_address address, shipping_address address );
-- Inserting INSERT INTO customers (name, billing_address) VALUES ('Acme Corp', ('123 Main St', 'NYC', 'NY', '10001', 'USA')::address);
-- Querying SELECT name, (billing_address).city FROM customers;
Creating Enum Types: =====================
CREATE TYPE order_status AS ENUM ( 'pending', 'processing', 'shipped', 'delivered', 'cancelled' );
CREATE TABLE orders ( id SERIAL PRIMARY KEY, status order_status DEFAULT 'pending', ... );
Creating Range Types: =====================
CREATE TYPE int4range AS RANGE ( subtype = integer, subtype_diff = int4mi );
CREATE TABLE bookings ( id SERIAL PRIMARY KEY, room_number INTEGER, stay_range int4range, EXCLUDE USING GIST (room_number WITH =, stay_range WITH &&) );
Managing Types: ==============
-- List types \dT SELECT typname FROM pg_type WHERE typtype = 'e'; -- Enums SELECT typname FROM pg_type WHERE typtype = 'c'; -- Composites
-- Drop type (must not be in use) DROP TYPE IF EXISTS address CASCADE; ```
---
## 4.11 Best Practices Database Object Best Practices========================================================================
1. Schema Design ┌──────────────────────────────────────────────────────────────┐ │ • Use schemas for logical organization │ │ • Set search_path explicitly │ │ • Avoid using the public schema for application objects │ └──────────────────────────────────────────────────────────────┘
2. Table Design ┌──────────────────────────────────────────────────────────────┐ │ • Always use primary keys │ │ • Add created_at/updated_at timestamps │ │ • Use appropriate data types │ │ • Add useful indexes │ └──────────────────────────────────────────────────────────────┘
3. Indexing ┌──────────────────────────────────────────────────────────────┐ │ • Index foreign keys │ │ • Index columns used in WHERE clauses │ │ • Consider composite indexes for multi-column queries │ │ • Use partial indexes for specific query patterns │ └──────────────────────────────────────────────────────────────┘
4. Security ┌──────────────────────────────────────────────────────────────┐ │ • Use views to control data access │ │ • Implement row-level security for multi-tenant apps │ │ • Grant minimal necessary permissions │ └──────────────────────────────────────────────────────────────┘
5. Naming Conventions ┌──────────────────────────────────────────────────────────────┐ │ • Use snake_case for all identifiers │ │ • Use plural names for tables (users, orders) │ │ • Use singular names for columns │ │ • Prefix indexes (idx_, uk_, fk_) │ └──────────────────────────────────────────────────────────────┘========================================================================---
## Next Chapter
[Chapter 5: PostgreSQL Data Types](./03_data_types/05_data_types.md)
---
*Last Updated: February 2026*