marmelab·12w9 Advanced PostgreSQL Features I Wish I Knew Sooner
A practical tour of nine underused PostgreSQL features that can replace complex application-level logic with native database capabilities. Covered features include EXCLUDE constraints for preventing overlapping ranges, CHECK constraints for row-level validation, GENERATED columns for derived data, DISTINCT ON as a concise alternative to GROUP BY, the FILTER clause for conditional aggregates, PARTITION BY window functions, ON CONFLICT upserts, composite types for structured nested data, and recursive CTEs for traversing hierarchical data. Each feature is illustrated with concrete use cases and SQL examples, along with relevant limitations and links to official documentation.