PostgreSQL vs The Standard
summary
Compares concrete SQL Standard rules with PostgreSQL behavior: Standard orders triggers by creation timestamp (immutable) and Postgres follows that; Standard timestamp with time zone uses offsets, not tz names; Postgres cannot implement the Standard ANY aggregate because an array-vs-subquery grammar ambiguity blocks it; DROP DOMAIN CASCADE per the Standard preserves column data by copying the domain constraint but Postgres instead drops the column (data loss); recent additions: SEARCH/CYCLE in PG14, configurable NULL behavior for UNIQUE in PG15, MERGE returning but JSON_TABLE postponed. This is about precise incompatibilities and real upgrade pain.
AI-written summary · about
chapters (13)
- 0:00Introduction to talk
- 1:31SQL standard misconceptions
- 5:49Standard features PostgreSQL gets wrong
- 9:07PostgreSQL’s incomplete schema creation
- 12:20Aggregate syntax limitations
- 14:28Case expression inconsistencies
- 17:25Drop domain CASCADE issue
- 19:28New SQL standard features in PostgreSQL
- 24:50Search and cycle in recursive CTEs
- 25:54Features PostgreSQL should adopt
- 34:31Pipe dreams for PostgreSQL future
- 39:07Periods and system versioning
- 43:03Q&A session
related talks
51mHow Postgres is Different (read BETTER than) your database
30mLesser known features of PostgreSQL
49mSELECT 'amazing features' FROM "postgresql"
41mWhat you should know about constraints in PostgreSQL 18
29mPostgres Demystified
31mSimplifying the Top Complexities While Migrating From Oracle to PostgreSQL
43mWhat You Should Know About Constraints in PostgreSQL (and What’s New in 18
45mSELECT 'amazing_features' FROM "postgresql"