Table of Contents
Item 5 – Use ENUM Types for Fixed Sets of Values
Table of Contents1 The Problem: String Columns Without Constraints1.1 Problem 1: Typos Create Invalid Data1.2 Problem 2: No Discoverability1.3 Problem 3: Difficult to Refactor1.4 Problem 4: Index Inefficiency1.5 Problem 5: No Type Safety2 The Naive Solution: CHECK Constraints3 The Effective Solution: ENUM Types4 Benefits of ENUM Types4.1 Benefit 1: Self-Documentation4.2 Benefit 2: Type Safety in
Item 4 – Prefer DOMAIN Types for Reusable Validation
Table of Contents1 The Problem: Repeating Validation Logic Everywhere1.1 Problem 1: Copy-Paste Error Prone1.2 Problem 2: Difficult to Update Validation Rules1.3 Problem 3: No Self-Documentation1.4 Problem 4: Can’t Validate Array Elements or Function Parameters2 The Effective Solution: DOMAIN Types3 Real-World Examples3.1 Example 1: Email Addresses (Complete Implementation)3.2 Example 2: Postal Codes (Country-Specific)3.3 Example 3: Monetary
Item 3 – Use EXCLUSION Constraints for Temporal Data
Table of Contents1 The Problem: Overlapping Reservations Are Hard to Prevent2 The Application-Level Solution (Race Conditions Ahead!)2.1 Attempted Fix: Serializable Transactions2.2 Attempted Fix: Locking3 The Effective Solution: EXCLUSION Constraints4 Understanding Range Types4.1 tstzrange (Timestamp with Timezone Range)4.2 Range Operators4.3 Other Range Types5 Real-World Examples5.1 Example 1: Conference Room Booking (Complete System)5.2 Example 2: Hotel Room
Item 2 – Use citext for Case-Insensitive Identifiers
Table of Contents1 The Problem: Case Sensitivity Creates User Experience Nightmares2 The Naive Solution: LOWER() Everywhere2.1 Problem 1: You Must Remember LOWER() Everywhere2.2 Problem 2: Can’t Use Standard Indexes2.3 Problem 3: Unique Constraints Don’t Work as Expected2.4 Problem 4: Code Complexity and Maintenance Burden3 The Effective Solution: citext Type4 How citext Works Under the Hood4.1
Item 1: Favor CHECK constraints over application-level validation
Table of Contents1 The Problem: Application-Level Validation is Fragile1.1 Scenario 1: The Midnight Migration1.2 Scenario 2: The Background Job1.3 Scenario 3: The Database Admin1.4 Scenario 4: The Bug2 The Solution: CHECK Constraints as the Final Line of Defense3 Real-World Examples3.1 Example 1: Dates and Time Ranges3.2 Example 2: Percentage Values3.3 Example 3: Inventory Quantities3.4 Example