Postgres 13

By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.
Help with an interesting Postgres question: Why isn't an Index Only Scan used on a partition accessed via the parent table?.
CREATE TABLE portfolio
(
  manager_name VARCHAR (25) NOT NULL,
  loan_amount INT NOT NULL CHECK (loan_amount <= 100),
  change_date date NOT NULL,
  total_amount INT NOT NULL,
  previous_change_date date NULL,
  previous_total_amount INT NULL,

  PRIMARY KEY (manager_name, change_date),
  UNIQUE (manager_name, change_date, total_amount),
  UNIQUE (manager_name, previous_change_date, previous_total_amount),

  FOREIGN KEY
      (manager_name, previous_change_date, previous_total_amount)
    REFERENCES portfolio
      (manager_name, change_date, total_amount),

  CHECK (total_amount >= 0 
    AND (total_amount = COALESCE(previous_total_amount, 0) + loan_amount)),
  CHECK (previous_change_date < change_date),
  CHECK ((previous_change_date IS NULL AND previous_total_amount IS NULL)
     OR (previous_change_date IS NOT NULL AND previous_total_amount IS NOT NULL)),

  CHECK (total_amount <= 100)
);
SELECT manager_name, SUM(loan_amount)
FROM portfolio
GROUP BY manager_name;
-- INSERT INTO portfolio VALUES ('man_1', 20, '2020-01-01', 20, NULL, NULL);

WITH v (manager_name, loan_amount, change_date) AS
( VALUES ('man_1', 20, '2020-01-01'::date))
INSERT INTO portfolio
SELECT 
    v.manager_name, v.loan_amount, v.change_date,
    COALESCE(p.total_amount, 0) + v.loan_amount,
    p.change_date,
    p.total_amount
FROM v 
  LEFT JOIN portfolio AS p
  ON p.manager_name = v.manager_name
ORDER BY p.change_date DESC
LIMIT 1 ;
1 rows affected

WITH v (manager_name, loan_amount, change_date) AS
( VALUES ('man_1', 25, '2020-01-02'::date))
INSERT INTO portfolio
SELECT 
    v.manager_name, v.loan_amount, v.change_date,
    COALESCE(p.total_amount, 0) + v.loan_amount,
    p.change_date,
    p.total_amount
FROM v 
  LEFT JOIN portfolio AS p
  ON p.manager_name = v.manager_name
ORDER BY p.change_date DESC
LIMIT 1 ;
1 rows affected

WITH v (manager_name, loan_amount, change_date) AS
( VALUES ('man_1', 30, '2020-01-03'::date))
INSERT INTO portfolio
SELECT 
    v.manager_name, v.loan_amount, v.change_date,
    COALESCE(p.total_amount, 0) + v.loan_amount,
    p.change_date,
    p.total_amount
FROM v 
  LEFT JOIN portfolio AS p
  ON p.manager_name = v.manager_name
ORDER BY p.change_date DESC
LIMIT 1 ;
1 rows affected

WITH v (manager_name, loan_amount, change_date) AS
( VALUES ('man_2', 99, '2020-10-01'::date))
INSERT INTO portfolio
SELECT 
    v.manager_name, v.loan_amount, v.change_date,
    COALESCE(p.total_amount, 0) + v.loan_amount,
    p.change_date,
    p.total_amount
FROM v 
  LEFT JOIN portfolio AS p
  ON p.manager_name = v.manager_name
ORDER BY p.change_date DESC
LIMIT 1 ;
1 rows affected

SELECT manager_name, SUM(loan_amount) AS s FROM portfolio  
GROUP BY manager_name
ORDER BY manager_name ;


manager_name
s


man_1
75

man_2
99


--
-- fsasdfas
--
WITH v (manager_name, loan_amount, change_date) AS
( VALUES ('man_1', 500, '2000-01-04'::date))
INSERT INTO portfolio
SELECT 
    v.manager_name, v.loan_amount, v.change_date,
    p.total_amount + v.loan_amount,
    p.change_date,
    p.total_amount
FROM portfolio AS p
  JOIN v 
  ON p.manager_name = v.manager_name
ORDER BY p.change_date DESC
LIMIT 1 ;
ERROR:  new row for relation "portfolio" violates check constraint "portfolio_check1"
DETAIL:  Failing row contains (man_1, 500, 2000-01-04, 575, 2020-01-03, 75).


WITH v (manager_name, loan_amount, change_date) AS
( VALUES ('man_1', 26, '2020-01-05'::date))
INSERT INTO portfolio
SELECT 
    v.manager_name, v.loan_amount, v.change_date,
    p.total_amount + v.loan_amount,
    p.change_date,
    p.total_amount
FROM portfolio AS p
  JOIN v 
  ON p.manager_name = v.manager_name
ORDER BY p.change_date DESC
LIMIT 1 ;
ERROR:  new row for relation "portfolio" violates check constraint "portfolio_total_amount_check"
DETAIL:  Failing row contains (man_1, 26, 2020-01-05, 101, 2020-01-03, 75).


    SELECT SUM(loan_amount) AS s
    FROM portfolio 
    GROUP BY manager_name
    HAVING SUM(loan_amount) > 100;
SELECT * FROM portfolio
ORDER BY manager_name, change_date ;


manager_name
loan_amount
change_date
total_amount
previous_change_date
previous_total_amount


man_1
20
2020-01-01
20
null
null

man_1
25
2020-01-02
45
2020-01-01
20

man_1
30
2020-01-03
75
2020-01-02
45

man_2
99
2020-10-01
99
null
null


WITH v (manager_name, loan_amount, change_date) AS
( VALUES ('man_1', 25, '2020-01-05'::date))
INSERT INTO portfolio
SELECT 
    v.manager_name, v.loan_amount, v.change_date,
    p.total_amount + v.loan_amount,
    p.change_date,
    p.total_amount
FROM portfolio AS p
  JOIN v 
  ON p.manager_name = v.manager_name
ORDER BY p.change_date DESC
LIMIT 1 ;
1 rows affected

SELECT * FROM portfolio
ORDER BY manager_name, change_date ;


manager_name
loan_amount
change_date
total_amount
previous_change_date
previous_total_amount


man_1
20
2020-01-01
20
null
null

man_1
25
2020-01-02
45
2020-01-01
20

man_1
30
2020-01-03
75
2020-01-02
45

man_1
25
2020-01-05
100
2020-01-03
75

man_2
99
2020-10-01
99
null
null
manager_name	loan_amount	change_date	total_amount	previous_change_date	previous_total_amount
man_1	20	2020-01-01	20	null	null
man_1	25	2020-01-02	45	2020-01-01	20
man_1	30	2020-01-03	75	2020-01-02	45
man_2	99	2020-10-01	99	null	null