Resources SQL SQL with Postgres

SQL with Postgres · Lesson 10 of 14 · about 25 minutes

Checks as queries

Every problem found in Lessons 3 to 8 was a query whose right answer is no rows. This lesson gathers them into one view, adds a check no constraint can make (two mayors in office at the same time), and wraps it in a script that fails when the data is wrong, so a change can be stopped before it does damage. The same way CivicGrid checks its data after every bulk change.

By the end you'll know

  • Why a check is a query whose right answer is no rows
  • How to collect many checks into one view with UNION ALL
  • How to check a rule across rows, like overlapping terms, that no constraint can express
  • The difference between an error that must stop the work and a warning for a person to read
  • How a script turns the checks into a pass or fail that other steps can rely on

Before you start

  • You need: Lessons 1 to 9 done, with the larkspur container running.
  • Get the new files: in your sql-practice folder, git pull. You should now have checks.sql and check.sh next to larkspur.sql.
  • Where commands run: in your computer's terminal, in sql-practice. This lesson runs psql from scripts, not by hand.

Constraints, and the things they can't say

Lesson 6 put rules in the tables, and Lesson 7 added them to the county once its data was clean. Constraints are the best kind of rule: nothing can get past them. But they can only say certain things. A CHECK sees one row. UNIQUE sees one value, or one combination. Plenty of real rules are bigger than that:

  • Two mayors of the same town in office at the same time, even in the past. That compares the dates in one row with the dates in another.
  • A town with no current mayor. That's about a row that isn't there.
  • A website that isn't https. Lesson 6's "Spot the problem" showed why that shouldn't be a constraint: it would refuse a true fact.

For those, there's the other kind of rule, the one Lessons 3 to 8 have been writing all along: a query that finds the rows breaking the rule. If it returns no rows, the rule holds. Every one of them has the same shape, so they can be collected, run together, and run every time.

Think of a pre-flight checklist. The plane is built to be safe: that's the constraints. Before every flight, the crew still walks through a list (fuel, flaps, doors), and each item has one right answer. Nobody skips it because the plane passed last time. A check is the same: cheap to run, run every time, and it only matters on the day something's wrong.

The checks, as one view

Open checks.sql. It's one view: a saved query with a name, which you can select from like a table. Each part of it is one check, and each check returns the same four columns, so they can be stacked with UNION ALL, which joins the rows of several queries into one result.

Here's the first, from Lesson 4:

SQL
CREATE OR REPLACE VIEW problems AS

-- one current mayor per town
SELECT 'error' AS severity, 'two_current_mayors' AS check_name,
       'town ' || town_id AS ref, count(*) || ' current mayors' AS detail
FROM leaders
WHERE role = 'mayor' AND ended IS NULL
GROUP BY town_id HAVING count(*) > 1

Every check returns:

  • severity: error for a rule that must always hold, warn for something a person should look at.
  • check_name: which rule, so you know what broke.
  • ref: which rows, by key, so you can find them.
  • detail: enough to understand the problem without another query.

Then UNION ALL, and the next check, from Lesson 3:

SQL
UNION ALL
-- a term can't end before it starts
SELECT 'error', 'term_order', 'leader ' || id, name || ': ' || started || ' to ' || ended
FROM leaders
WHERE ended < started

Only the first part names the columns; the rest just line up with them. The file goes on the same way: names with spaces (Lesson 3), the phone format (Lesson 7), shared phones with the empty ones left out (Lessons 4 and 8), towns with no current mayor using NOT EXISTS (Lesson 5), websites that are missing or not https, and populations that are unknown.

A rule no constraint can make

One check is new. Two mayors of the same town whose terms overlap:

SQL
UNION ALL
-- two mayors of the same town at the same time, past or present
SELECT 'error', 'overlapping_mayors', 'leaders ' || a.id || ' and ' || b.id,
       btrim(a.name) || ' and ' || btrim(b.name) || ', town ' || a.town_id
FROM leaders a
JOIN leaders b ON b.town_id = a.town_id AND b.id > a.id
WHERE a.role = 'mayor' AND b.role = 'mayor'
  AND a.started < coalesce(b.ended, 'infinity')
  AND b.started < coalesce(a.ended, 'infinity')

Three ideas in it:

  • A table joined to itself. leaders a JOIN leaders b pairs each leader with every other leader of the same town. b.id > a.id keeps each pair once, and stops a leader being paired with themselves.
  • The overlap test. Two periods overlap when each starts before the other ends. That's the whole of it: a.started < b.ended AND b.started < a.ended.
  • coalesce(ended, 'infinity'). A current mayor has no end date, which means "still going". 'infinity' is a real date value in Postgres, later than any other, so a term with no end is treated as one that never ends. Without it, Lesson 8's rule would strike: comparing with NULL is unknown, and current mayors would never be found overlapping anyone.

The comparisons are strict, < and not <=, so a term that ends on the same day the next one starts, a handover, doesn't count as an overlap.

Errors and warnings

Not every problem means the data is wrong. Two mayors at once is impossible: that's an error. A town with no current mayor might be true. Maybe the office is vacant, or maybe nobody has recorded the new one, and a person has to find out. That's a warning.

The split matters because of what happens next. An error should stop whatever is running: an import, a bulk update, a deployment. A warning should be read, and never stop anything, or people learn to ignore the checks. That's why severity is a column, and why the script below only fails on errors.

The script

check.sh loads the view, prints every problem, and decides:

BASH
#!/usr/bin/env bash
# Run the Larkspur County checks against the larkspur container.
# Prints every problem found. Exits 1 if any error-level check finds a row.
set -euo pipefail

psql_() { docker exec larkspur psql -U postgres -d larkspur -v ON_ERROR_STOP=1 "$@"; }

psql_ -q -f /practice/checks.sql
psql_ -c "SELECT severity, check_name, ref, detail FROM problems ORDER BY severity, check_name, ref;"

errors=$(psql_ -tA -c "SELECT count(*) FROM problems WHERE severity = 'error';")
warnings=$(psql_ -tA -c "SELECT count(*) FROM problems WHERE severity = 'warn';")

if [ "$errors" -gt 0 ]; then
  echo "FAIL: $errors error(s), $warnings warning(s)"
  exit 1
fi
echo "PASS: 0 errors, $warnings warning(s)"

The new parts, if you haven't done the Linux track:

  • -v ON_ERROR_STOP=1 makes psql stop, and fail, at the first SQL error. Without it, psql carries on past errors, as you saw in every lesson's batch, and a broken check would look like a passing one.
  • -tA prints just the value: no header, no lines, no (1 row). That's what you want when a script reads the answer, here into errors.
  • exit 1 is how a script says "failed". Every command finishes with an exit code: 0 means success, anything else means failure. Other scripts, and tools like CI pipelines, read it to decide whether to carry on.

Run it on the original county

Reload the county with all its mistakes, then run the checks:

BASH
docker exec larkspur psql -U postgres -q -f /practice/larkspur.sql
./check.sh; echo "exit code: $?"
OUTPUT
 severity |     check_name     |        ref        |                    detail                    
----------+--------------------+-------------------+----------------------------------------------
 error    | name_spaces        | leader 8          | "Sam Whitfield "
 error    | overlapping_mayors | leaders 19 and 20 | Joy Hart and Carl Webb, town 12
 error    | overlapping_mayors | leaders 9 and 10  | Grace Lin and Owen Price, town 5
 error    | phone_format       | town 7            | Fairview: 555-010-7700
 error    | shared_phone       | towns 4, 5        | (555) 010-4400
 error    | term_order         | leader 20         | Carl Webb: 2024-01-01 to 2023-06-30
 error    | two_current_mayors | town 5            | 2 current mayors
 warn     | no_current_mayor   | town 10           | Juniper
 warn     | no_current_mayor   | town 8            | Glen Oak
 warn     | population_missing | town 5            | Dunmore
 warn     | website_not_https  | town 10           | Juniper: (none)
 warn     | website_not_https  | town 2            | Ashford: (none)
 warn     | website_not_https  | town 4            | Cedar Hollow: http://cedarhollow.larkspur.us
(13 rows)

FAIL: 7 error(s), 6 warning(s)
exit code: 1

Every problem from Lessons 3 to 8, in one run. Seven errors and six warnings, each with the key to find it. $? holds the exit code of the last command: 1, failed.

The quotes in "Sam Whitfield " are there on purpose, so the trailing space can be seen. And town 10 sorts before town 2: ref is text, and text sorts one character at a time, Lesson 8's trap, harmless here.

Dunmore's two current mayors show up twice: as two_current_mayors, and as overlapping_mayors, since two people in office now overlap. Two checks catching the same fact from different angles is normal, and fine.

After Lesson 7's fixes

Apply the same fixes as Lesson 7, in one transaction:

BASH
docker exec larkspur psql -U postgres -d larkspur -c "
BEGIN;
UPDATE leaders SET name = btrim(name) WHERE id = 8;
UPDATE leaders SET ended = '2023-01-01' WHERE id = 9;
UPDATE leaders SET started = '2023-01-01' WHERE id = 20;
UPDATE towns SET phone = '(555) 010-7700' WHERE id = 7;
UPDATE towns SET phone = '(555) 010-5500' WHERE id = 5;
COMMIT;"
./check.sh; echo "exit code: $?"
OUTPUT
BEGIN
UPDATE 1
UPDATE 1
UPDATE 1
UPDATE 1
UPDATE 1
COMMIT
 severity |     check_name     |        ref        |                    detail                    
----------+--------------------+-------------------+----------------------------------------------
 error    | overlapping_mayors | leaders 19 and 20 | Joy Hart and Carl Webb, town 12
 warn     | no_current_mayor   | town 10           | Juniper
 warn     | no_current_mayor   | town 8            | Glen Oak
 warn     | population_missing | town 5            | Dunmore
 warn     | website_not_https  | town 10           | Juniper: (none)
 warn     | website_not_https  | town 2            | Ashford: (none)
 warn     | website_not_https  | town 4            | Cedar Hollow: http://cedarhollow.larkspur.us
(7 rows)

FAIL: 1 error(s), 6 warning(s)
exit code: 1

Six errors fixed. One left, and it's one Lesson 7 created. Fixing Carl Webb's start year made his term valid, early 2023, and every rule Lesson 7 added was happy with it. But Joy Hart has been Linden's mayor since 2022. Look at the two of them:

BASH
docker exec larkspur psql -U postgres -d larkspur -c "SELECT id, name, started, ended FROM leaders WHERE town_id = 12 ORDER BY started;"
OUTPUT
 id |   name    |  started   |   ended    
----+-----------+------------+------------
 19 | Joy Hart  | 2022-01-01 | 
 20 | Carl Webb | 2023-01-01 | 2023-06-30
(2 rows)

Joy Hart's term starts in 2022 and never ends; Carl Webb's sits inside it. Two mayors at once, which the constraints couldn't see and the check could. This is the real value of running checks after every change: a fix can create a new problem, and the rules you thought of last time won't catch it.

Fix it, and pass

Which record is wrong? Again, that needs a source. Say Linden's records show Joy Hart took over the day Carl Webb left:

BASH
docker exec larkspur psql -U postgres -d larkspur -c "UPDATE leaders SET started = '2023-06-30' WHERE id = 19 RETURNING id, name, started, ended;"
./check.sh; echo "exit code: $?"
OUTPUT
 id |   name   |  started   | ended 
----+----------+------------+-------
 19 | Joy Hart | 2023-06-30 | 
(1 row)

UPDATE 1
 severity |     check_name     |   ref   |                    detail                    
----------+--------------------+---------+----------------------------------------------
 warn     | no_current_mayor   | town 10 | Juniper
 warn     | no_current_mayor   | town 8  | Glen Oak
 warn     | population_missing | town 5  | Dunmore
 warn     | website_not_https  | town 10 | Juniper: (none)
 warn     | website_not_https  | town 2  | Ashford: (none)
 warn     | website_not_https  | town 4  | Cedar Hollow: http://cedarhollow.larkspur.us
(6 rows)

PASS: 0 errors, 6 warning(s)
exit code: 0

PASS, exit code 0. Carl Webb ends on 30 June and Joy Hart starts on 30 June: a handover, not an overlap, because the comparison is strict. The six warnings remain, listed every run, for a person to work through: two towns to research a mayor for, one population to find, three websites to look at.

Checks as a gate

The exit code is what makes the checks useful to other steps. && runs the next command only if the last one succeeded; || runs it only if the last one failed:

BASH
./check.sh > /dev/null && echo "checks passed: safe to carry on" || echo "checks failed: stop"
OUTPUT
checks passed: safe to carry on

(> /dev/null throws away the table, to keep just the verdict.) Put ./check.sh && in front of whatever comes next, a publish, an export, the next stage of an import, and it only runs on good data.

In real systems

  • CivicGrid runs four checks after every bulk change, and stops if any of them finds a row: every city's metro area contains its own state, the population density is believable, every phone is in the right format, and no two cities share a phone. They're queries, like these, with the same rule: a check that finds a row stops the work.
  • Checks also run on a schedule. Data changes for reasons nobody planned: someone edits a row by hand, a source changes. A nightly run that reports to a channel catches what the after-change runs couldn't.
  • The same idea in CI. A pipeline runs tests and fails the build on any failure, by exit code. Data checks in a pipeline are tests for the data, and check.sh would drop straight into one.

Spot the problem

Work out what's wrong before you read the explanation.

The check that always passes. Someone adds a check for meetings held before their town was founded, and it never finds anything, for months. Its query is SELECT ... FROM meetings m JOIN towns t ON t.id = m.town_id WHERE extract(year FROM m.held_on) < t.founded, in the view, and check.sh passes every time. Then someone tries a deliberate bad row, a meeting in 1700, to test it, and it still passes. What's wrong? Look at the script: errors are counted with WHERE severity = 'error'. If the new check's severity was written as 'Error', or 'err', its rows are in the view but never counted, and the script says PASS. Test every check by breaking the rule on purpose, once, and seeing it fail. A check you've never seen fail might not be checking anything. (A CHECK (severity IN ('error', 'warn')) can't go on a view, but a quick query over problems for any other severity can be a check of its own.)

Keep this

ToWrite
Run the checks./check.sh, in sql-practice
See the last exit codeecho $?
Carry on only if they pass./check.sh && next-step
A saved queryCREATE OR REPLACE VIEW problems AS SELECT ...;
Stack query resultsSELECT ... UNION ALL SELECT ...
Pair rows of one tableFROM leaders a JOIN leaders b ON b.town_id = a.town_id AND b.id > a.id
Do two periods overlap?a.started < coalesce(b.ended, 'infinity') AND b.started < coalesce(a.ended, 'infinity')
Stop psql at the first errorpsql -v ON_ERROR_STOP=1
Just the value, for a scriptpsql -tA -c 'SELECT count(*) ...'

Where you are now

You have a set of checks that run in one command, find every problem from Lessons 3 to 8 and one more, and fail when the data is wrong. Your county is clean now, apart from the warnings. Lesson 11 is about who can see and change it: roles, permissions, and row-level security.

Check yourself

  1. What's the right answer to a check query?

  2. Why can't 'two mayors of a town in office at the same time' be a CHECK constraint?

  3. Why does the overlap check use coalesce(ended, 'infinity')?

  4. Why is 'no current mayor' a warning and not an error?

  5. After Lesson 7's fixes, every constraint passed but the checks still failed. Why?

  6. What does ./check.sh && publish.sh do when a check finds an error?

Key terms

  • Check: a query that returns the rows breaking a rule. No rows means the rule holds.
  • View: a saved query with a name, which can be selected from like a table.
  • UNION ALL: stacks the rows of several queries with the same columns into one result.
  • Self-join: a table joined to itself, to compare rows with each other.
  • Error and warning: a problem that must stop the work, and one a person should read.
  • Exit code: the number a command finishes with. 0 means success; anything else, failure.
  • Gate: a step that only lets the next one run if it passes.

Go deeper

  • The PostgreSQL manual: Views, and Combining Queries for UNION.
  • Try: add a check that no meeting has a negative attendance, as an error. Then break it on purpose with an UPDATE in a transaction, run ./check.sh, and ROLLBACK.
  • Try: add a warning for meetings with no minutes link. How many does it find? Is that a warning you'd want to see every run?

From www.kwamib.dev/resources/sql/checks-as-queries