README.adoc

PostgreSQL: Seven Databases in Seven Weeks

Table of Contents

• Prerequisites
• Day 1
• Day 2
• Day 3

Book

Seven Databases in Seven Weeks

Prerequisites

ℹ️	For simplicity, we will be using the Docker variant.

$ ../docker/scripts/credentials_add.sh

$ ../docker/scripts/docker_build.sh -d ../docker/Dockerfile

$ ../docker/scripts/docker_start.sh -d . -i ../docker/initdb

The superuser name and password:

$ cat .docker/secrets/postgres-superuser
$ cat .docker/secrets/postgres-superuser-pw

Day 1

$ ../docker/scripts/docker_psql.sh -d . -f day01-db-create.psql
$ ../docker/scripts/docker_psql.sh -d . -n book -f day01-init.psql
$ ../docker/scripts/docker_psql.sh -d . -n book -f day01.psql
$ ../docker/scripts/docker_psql.sh -d . -n book -f day01-cleanup.psql
$ ../docker/scripts/docker_psql.sh -d . -f day01-db-drop.psql

ℹ️

Ensure that you set the correct search_path: SET search_path TO book; search_path Use explicit names for constraints: CREATE TABLE example ( example_id integer PRIMARY KEY, name text NOT NULL CHECK (name <> ''), other_id integer NOT NULL REFERENCES other ); ⇓ CREATE TABLE example ( example_id integer CONSTRAINT example_pk PRIMARY KEY, name text NOT NULL CONSTRAINT name_ck CHECK (name <> ''), other_id integer NOT NULL CONSTRAINT example_other_fk REFERENCES other ); Be explicit about where the column comes from instead of relying on disjunct column names: SELECT title, name FROM event e JOIN venue v ON e.venue_id = v.venue_id; ⇓ SELECT e.title, v.name FROM event e JOIN venue v ON e.venue_id = v.venue_id; Be explicit about the target columns in an INSERT instead of relying on the column’s declaration order: INSERT INTO country VALUES ('de', 'Germany'); ⇓ INSERT INTO country (code, name) VALUES ('de', 'Germany'); Be explicit where you want an extension to be installed: CREATE EXTENSION cube; ⇓ CREATE EXTENSION cube SCHEMA extension; One does not need to explicitly mark a primary key NOT NULL. Use integer GENERATED ALWAYS AS IDENTITY PRIMARY KEY instead of serial PRIMARY KEY. Serial Types Identity Columns Indexes may not be used for small tables; SET enable_seqscan = OFF; will force their usage. enable_seqscan Why isn’t postgres using my index? Postgres not using index when index scan is a much better option

💡	Use singular table names: city/venue instead of cities/venues. Use the following suffixes: Table no suffix Primary Key _pk Unique Key _uq Foreign Key _fk Check Constraint _ck Index _ix Trigger _tr

Day 2

$ ../docker/scripts/docker_psql.sh -d . -f day01-db-create.psql
$ ../docker/scripts/docker_psql.sh -d . -n book -f day01-init.psql
$ ../docker/scripts/docker_psql.sh -d . -n book -f day01.psql
$ ../docker/scripts/docker_psql.sh -d . -n book -f day02-extensions-create.psql
$ ../docker/scripts/docker_psql.sh -d . -n book -f day02.psql
$ ../docker/scripts/docker_psql.sh -d . -n book -f day02-cleanup.psql
$ ../docker/scripts/docker_psql.sh -d . -n book -f day02-extensions-drop.psql
$ ../docker/scripts/docker_psql.sh -d . -n book -f day01-cleanup.psql
$ ../docker/scripts/docker_psql.sh -d . -f day01-db-drop.psql

ℹ️

Ensure that you add the extensions to the search_path: SET search_path TO book,extension; The current date and time: CURRENT_TIMESTAMP and NOW() ⇒ start of current transaction CLOCK_TIMESTAMP() ⇒ changes during statement execution Date/Time Functions and Operators Delaying Execution Convert a timestamp to a date: CAST(some_timestamp AS date): 2025-03-24 03:00:00.000000 ⇒ 2025-03-24 Convert a date to a timestamp: some_date + '03:00:00'::time: 2025-03-24 ⇒ 2025-03-24 03:00:00.000000 Display a localized timestamp: Ensure your server has the desired locales: Locale Customization Set the desired locale: SET lc_time TO 'de_DE.utf8'; lc_time Locale Support Use a localized format: TO_CHAR(date, 'TMDay, DD TMMonth YYYY') en_US.utf8 ⇒ Friday, 14 February 2025 de_DE.utf8 ⇒ Freitag, 14 Februar 2025 Data Type Formatting Functions Template Pattern Modifiers for Date/Time Formatting Accessing old and new rows in RULE and TRIGGER: Triggers on Data Changes Rules on INSERT, UPDATE, and DELETE

Day 3

$ ../docker/scripts/docker_psql.sh -d . -f day03-db-create.psql
$ ../docker/scripts/docker_psql.sh -d . -n movie -f day03-extensions-create.psql
$ ../docker/scripts/docker_psql.sh -d . -n movie -f day03-init.psql
$ ../docker/scripts/docker_psql.sh -d . -n movie -f day03-data.psql
$ ../docker/scripts/docker_psql.sh -d . -n movie -f day03.psql
$ ../docker/scripts/docker_psql.sh -d . -n movie -f day03-cleanup.psql
$ ../docker/scripts/docker_psql.sh -d . -n movie -f day03-extensions-drop.psql
$ ../docker/scripts/docker_psql.sh -d . -f day03-db-drop.psql

ℹ️	Show text search configurations: \dF Show text search dictionaries: \dFd Display the stop words for the English language: $ docker exec sdavids-database-playground-postgres cat /usr/share/postgresql/17/tsearch_data/english.stop