Postgres 18

By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.

CREATE TABLE Activities (
  activity_id INT PRIMARY KEY,
  category VARCHAR(4),
  activity VARCHAR,
  due_date DATE,
  n_repeats INT,
  done BOOLEAN DEFAULT FALSE,
  notes TEXT DEFAULT NULL
);


CREATE TABLE PushUps (
  duration_s DOUBLE PRECISION
  ) INHERITS (Activities);

CREATE TABLE Squats (
  weight_kg DOUBLE PRECISION
  ) INHERITS (Activities);




CREATE TABLE



CREATE TABLE



CREATE TABLE


INSERT INTO Activities (activity_id, category, activity, due_date, n_repeats)
VALUES
  ( 3, 'pull', 'pull-ups', '2026-02-01',  5),
  ( 4, 'push', 'dips',     '2026-02-01', 16),
  ( 5, 'quad', 'lunges',   '2026-02-01', 25),
  ( 6, 'pull', 'pull-ups', '2026-02-01',  5),
  ( 9, 'pull', 'pull-ups', '2026-02-02',  6),
  (10, 'push', 'dips',     '2026-02-02', 18),
  (11, 'quad', 'lunges',   '2026-02-02', 30),
  (12, 'pull', 'pull-ups', '2026-02-02',  6)
;


INSERT INTO PushUps (activity_id, category, activity, due_date, n_repeats)
VALUES
  ( 1, 'push', 'push-ups', '2026-02-01', 20),
  ( 7, 'push', 'push-ups', '2026-02-02', 22)
;

INSERT INTO Squats (activity_id, category, activity, due_date, n_repeats, weight_kg)
VALUES
  ( 2, 'quad', 'squats',   '2026-02-01', 16, 60),
  ( 8, 'quad', 'squats',   '2026-02-02', 17, 64)
;


INSERT 0 8



INSERT 0 2



INSERT 0 2


SELECT * 
FROM ONLY Activities 
NATURAL FULL JOIN PushUps
NATURAL FULL JOIN Squats
ORDER BY activity_id ASC
;


activity_id
category
activity
due_date
n_repeats
done
notes
duration_s
weight_kg


1
push
push-ups
2026-02-01
20
f
null
null
null

2
quad
squats
2026-02-01
16
f
null
null
60

3
pull
pull-ups
2026-02-01
5
f
null
null
null

4
push
dips
2026-02-01
16
f
null
null
null

5
quad
lunges
2026-02-01
25
f
null
null
null

6
pull
pull-ups
2026-02-01
5
f
null
null
null

7
push
push-ups
2026-02-02
22
f
null
null
null

8
quad
squats
2026-02-02
17
f
null
null
64

9
pull
pull-ups
2026-02-02
6
f
null
null
null

10
push
dips
2026-02-02
18
f
null
null
null

11
quad
lunges
2026-02-02
30
f
null
null
null

12
pull
pull-ups
2026-02-02
6
f
null
null
null



SELECT 12


CREATE VIEW AllActivities AS
SELECT * 
FROM ONLY Activities 
NATURAL FULL JOIN PushUps
NATURAL FULL JOIN Squats
ORDER BY activity_id ASC
;

CREATE VIEW


-- first, erase all default updating activities
CREATE RULE Activities_upd0 AS
ON UPDATE TO AllActivities
DO INSTEAD NOTHING;

-- update common fields of general activities
CREATE RULE Activities_upd_all AS
ON UPDATE TO AllActivities
DO ALSO
  UPDATE Activities
  SET
    done = NEW.done,
    notes = NEW.notes
  WHERE activity_id = OLD.activity_id
;

-- finally, update specific activities which require user input
CREATE RULE Activities_upd_pushups AS
ON UPDATE TO AllActivities
DO ALSO
  UPDATE PushUps
  SET
    duration_s = NEW.duration_s
  WHERE activity_id = OLD.activity_id
;

CREATE RULE



CREATE RULE



CREATE RULE


UPDATE AllActivities
SET duration_s = 16, notes = 'best attempt ever!'
WHERE category = 'push'
AND activity = 'push-ups'
AND due_date = '2026-02-01'
;

SELECT * FROM AllActivities;

UPDATE 0




activity_id
category
activity
due_date
n_repeats
done
notes
duration_s
weight_kg


1
push
push-ups
2026-02-01
20
f
best attempt ever!
16
null

2
quad
squats
2026-02-01
16
f
null
null
60

3
pull
pull-ups
2026-02-01
5
f
null
null
null

4
push
dips
2026-02-01
16
f
null
null
null

5
quad
lunges
2026-02-01
25
f
null
null
null

6
pull
pull-ups
2026-02-01
5
f
null
null
null

7
push
push-ups
2026-02-02
22
f
null
null
null

8
quad
squats
2026-02-02
17
f
null
null
64

9
pull
pull-ups
2026-02-02
6
f
null
null
null

10
push
dips
2026-02-02
18
f
null
null
null

11
quad
lunges
2026-02-02
30
f
null
null
null

12
pull
pull-ups
2026-02-02
6
f
null
null
null



SELECT 12

activity_id	category	activity	due_date	n_repeats	done	notes	duration_s	weight_kg
1	push	push-ups	2026-02-01	20	f	null	null	null
2	quad	squats	2026-02-01	16	f	null	null	60
3	pull	pull-ups	2026-02-01	5	f	null	null	null
4	push	dips	2026-02-01	16	f	null	null	null
5	quad	lunges	2026-02-01	25	f	null	null	null
6	pull	pull-ups	2026-02-01	5	f	null	null	null
7	push	push-ups	2026-02-02	22	f	null	null	null
8	quad	squats	2026-02-02	17	f	null	null	64
9	pull	pull-ups	2026-02-02	6	f	null	null	null
10	push	dips	2026-02-02	18	f	null	null	null
11	quad	lunges	2026-02-02	30	f	null	null	null
12	pull	pull-ups	2026-02-02	6	f	null	null	null