Lesser known features of PostgreSQL
summary
Shows concrete Postgres tricks: use the system column xmax to tell xmax = 0 trick which upsert rows were inserted vs updated, use pg_sleep_for(interval) to sleep with human intervals, match many patterns via SIMILAR TO / regex or email LIKE ANY(array), make random data reproducible with setseed(seed), write big string or JSON literals with dollar quoting, enforce identity keys with GENERATED ALWAYS plus OVERRIDING SYSTEM VALUE, add constraints fast with ALTER ... NOT VALID then VALIDATE, and export multi-line results by COPY TO STDOUT piped to \g.
AI-written summary · about
chapters (11)
- 0:00Introduction
- 1:56Lesser known features from PG 10
- 2:40of updated & inserted rows at upsert
- 7:18Sleeping
- 9:07Match against multiple patterns
- 11:55Generate reproducible random data
- 14:26Dollar quoting
- 17:51Prevent setting the value of an auto-generated key
- 20:34Add constraints without validating immediately
- 22:49PSQL tips
- 26:26Q&A with Haki & Claire
related talks
45mSELECT 'amazing_features' FROM "postgresql"
29mSELECT 'amazing_features' FROM "postgresql"
34mSimple and Awesome Database Tricks
49mSELECT 'amazing features' FROM "postgresql"
46mUse PostgreSQL to Step Up Your Game
42mPostgreSQL Tips & Tricks For App Devs
29mPostgres Demystified
33mPostgres, the Best Tool You're Already Using