Understanding Advanced Datatypes in PostgreSQL
summary
Use bigserial or UUID for surrogate keys (create the uuid-ossp extension if needed); prefer text not varchar for strings; default timestamps to timestamp with time zone; store semi-structured data as JSONB not JSON. Create functional indexes (e g reverse(email)) and GIN indexes on arrays or tsvector for fast lookups. Toast moves values out of row at roughly 2KB, so avoid scanning large blobs; use range types plus exclusion constraints for time intervals and PostGIS or ip4r extensions for geo and network work.
AI-written summary · about
chapters (32)
- 0:00Intro
- 1:24Basic types surrogate keys
- 5:53Text: Use indexes for pattern- matching An index supports prefixes (LIKE 'Peters)
- 11:22Time travel: interval
- 12:07Boolean values
- 12:32Binary data
- 13:20Don't use
- 16:00Internals: What's a type?
- 17:12Internals: How does it work?
- 19:36Internals: TOAST is great
- 22:26Internals: Make TOAST faster
- 23:59Advanced types greatest hits
- 24:23Array: What?
- 29:18JSON(b): jsonb vs json
- 33:32Enums: How?
- 34:24Ranges: What?
- 35:18Ranges: How? (Basics)
- 36:21Ranges: How? (Window functions)
- 38:25Email Addresses
- 38:54emailaddr
- 40:07Skip the migration!
- 40:24Geometry: What?
- 40:42PostGIS: What?
- 41:54Network addresses
- 42:15Physical Units
- 45:05Domains: Examples
- 45:23Domains & Arrays
- 45:42Composite Types: How? (1/2)
- 46:19DIY functions
- 46:44DIY Operators
- 47:57Images
- 48:36Basic data types to use
