PGCon2018 - 2.5
PGCon 2018
The PostgreSQL Conference
| Speakers | |
|---|---|
|
David Fetter |
| Schedule | |
|---|---|
| Day | Talks - Day 1: Thursday - 2018-05-31 |
| Room | DMS 1110 |
| Start time | 13:15 |
| Duration | 00:45 |
| Info | |
| ID | 1180 |
| Event type | Lecture |
| Track | New Features |
| Language used for presentation | English |
ASSERTIONs
and how to use them
ASSERTIONs are a way to make very generalized constraints in a database. They were first described in SQL-92, and are only now coming to the fore. ASSERTIONs can be used for constraints like, "The sum of dice in the first_winning_roll table must be either 7 or 11," but that's just the beginning. ASSERTIONs can also constrain the state of multiple inter-related tables, as in "Any given payment_id can be in exactly one table among payment_check, payment_credit, and payment_manila," or even, "The sum of amounts across of type check, credit_card, and manila_envelope_stuffed_with_notes tables for a given payment_id must be the same as the amount in the payment table to which they refer."
Learn what's been done, what's happening, and what they hold for the future.
ASSERTIONs constrain data in ways that other types of constraints cannot. In this talk, you will learn how to use them, how they are coming to PostgreSQL, (some of) what the future holds. Whole new vistas of possibility are opening with this feature.
The last example above might be written as
CREATE ASSERTION payment_split_consistent AS CHECK (
SELECT bool_and(p.amount = COALESCE(sum_ch, 0) + COALESCE(sum_cr,0) + COALESCE(sum_ma, 0))
FROM payment p
LEFT JOIN LATERAL (SELECT sum(amount) AS sum_ch FROM payment_check ch WHERE p.payment_id = ch.payment_id) AS ch ON true
LEFT JOIN LATERAL (SELECT sum(amount) AS sum_cr FROM payment_credit cr WHERE p.payment_id = cr.payment_id) AS cr ON true
LEFT JOIN LATERAL (SELECT sum(amount) AS sum_ma FROM payment_manila ma WHERE p.payment_id = ma.payment_id) AS ma ON true
) DEFERRABLE INITIALLY DEFERRED;
This will be yet another of the features you will soon forget (or at least want to forget) how you ever worked without.
