Postgres 12

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?.
--
-- Scenario
--

-- 
-- System to manage a portfolio system.
--
-- A portfolio is a set of dealers (aka managers) who have clients who make deals.  
--
-- A deal is an instrument - i.e. something worth some money - it is liquid (can
-- be sold at a moment's notice, but the value can vary depending on market 
-- conditions. The selling is somebody else's business - we just hold the 
-- instrument until instructed to sell at price X.

-- We loan our clients the money to purchase the instruments. In order to avoid
-- a Nick Leeson situation, clients have a credit limit (200) and managers also have
-- a limit (1000 - not implemented) so that nobody is overreaching their resources. 

-- This is for the _b (bronze portfolios) - clients and managers can move depending 
-- on performance.
--
-- We could implement (not done) a system whereby if a client goes above 200 but remains below
-- 300, they can only pay money back into the account - i.e. movement type 'C' - we
-- could also do the same for managers between 1000 and 1500 - we could use CASE
-- to set some value to NULL  and then the insert would fail if movement type was
-- 'D'
--

--
-- As for the d_tot_all_trans field - this is just a sample - I could query a totally
-- different client table say (I have no client_id in my portfolio table) - now, it
-- is conceivable that a client might have different managers for different account
-- types (shares, bonds, currency baskets... whatever) and we don't want a situation
-- arising where the client (unbeknownst to the individual managers) is building
-- up substantial debt in all their trading areas - so a sum from the overall client
-- table might be a more suitable metric for the client's financial reliability.
--
-- Of course, all of this could be done with TRIGGERs - what are the pros and cons
-- of the TRIGGER approach relative to this approach? Maybe ask a question on the site 
-- proper?
--
--
CREATE TABLE manager
(
  manager_id INTEGER NOT NULL GENERATED ALWAYS AS IDENTITY,
  manager_name TEXT NOT NULL,
  
  --
  -- other fields
  --

  CONSTRAINT manager_pk PRIMARY KEY(manager_id),
  CONSTRAINT m_name_uq  UNIQUE (manager_name),
  CONSTRAINT m_name_ck  CHECK (manager_name ~ '^[A-Z][a-zA-Z - \'']')  -- 4? last character small letter

  -- I wanted the regex to be something like '^[A-Z][a-zA-Z - \''][a-z]$'
  --
  -- to ensure that there were no spaces at the end and that the string finished
  -- in a lower case letter. Would also like to eliminate any possibility of
  -- double punctuation - i.e. two spaces in a row, two hyphens or two quotes
  --
  -- something like '^[A-Z][a-zA-Z - \''][a-z]$|^[ - \'']{2,}' but can't seem to
  -- get it to work - not sure if it's a fiddle thing or what?
 
  -- and WHY does my regex allow a 4 in the string C4itizen?...

);
CREATE TABLE deal
(
  deal_id    INTEGER NOT NULL GENERATED ALWAYS AS IDENTITY,
  deal_name  TEXT NOT NULL,

  --
  -- other fields
  --

  CONSTRAINT deal_pk      PRIMARY KEY (deal_id),
  CONSTRAINT deal_name_uq UNIQUE (deal_name)
);
--
-- Movement type - Debit or Credit
--
CREATE DOMAIN MOVEMENT CHAR(1) NOT NULL CHECK (VALUE IN ('D', 'C'));
SELECT version();


version


PostgreSQL 12.6 on x86_64-pc-linux-gnu, compiled by gcc (GCC) 8.3.1 20191121 (Red Hat 8.3.1-5), 64-bit


INSERT INTO manager (manager_name) 
VALUES 
('N''Ketchi Obaya'),
('Sean C4itizen'),
('Joe Sixpack'),
('Joan Doe'),
('Oisín O''Shea');
SELECT * FROM manager;
5 rows affected



manager_id
manager_name


1
N'Ketchi Obaya

2
Sean C4itizen

3
Joe Sixpack

4
Joan Doe

5
Oisín O'Shea


--
-- So I don't confuse man_id's and deal_id's!
--

SELECT SETVAL (PG_GET_SERIAL_SEQUENCE ('deal', 'deal_id'), 999);


setval


999


INSERT INTO deal (deal_name) VALUES
('Bitcoin 1'),
('Bitcoin 2'),
('Ethereum 1'),
('Ethereum 2');
SELECT * FROM deal;
4 rows affected



deal_id
deal_name


1000
Bitcoin 1

1001
Bitcoin 2

1002
Ethereum 1

1003
Ethereum 2


CREATE TABLE portfolio_b  -- bronze portfolio - can have _s (silver) and _g (gold)
(
     portfolio_id INTEGER GENERATED ALWAYS AS IDENTITY,

           man_id INTEGER NOT NULL,

             d_id INTEGER NOT NULL,
  
           m_type MOVEMENT NOT NULL,  -- movement - remember CHECK (VALUE IN ('D', 'C'))
  
             m_dt TIMESTAMP WITH TIME ZONE NOT NULL,

         m_amount INTEGER NOT NULL,  

       d_tot_prev INTEGER NOT NULL,
  
            d_tot INTEGER NOT NULL 
              GENERATED ALWAYS AS ((m_amount) + (d_tot_prev)) STORED,
 
  d_tot_all_trans INTEGER NOT NULL,
      
  CONSTRAINT man_id_fk  FOREIGN KEY (man_id)  REFERENCES manager (manager_id),
  CONSTRAINT deal_id_fk FOREIGN KEY (d_id) REFERENCES deal    (deal_id),
  
  CONSTRAINT m_id_d_id_m_type_uq UNIQUE (man_id, d_id, m_dt), -- no txns within same micro-second
  
  CONSTRAINT m_amount_gt_zero_ck CHECK ((ABS(m_amount) > 0) AND (ABS(m_amount) <= 200)),
  CONSTRAINT d_tot_lt_eq_200_ck  CHECK (d_tot >= 0 AND d_tot <= 200)
);
CREATE INDEX man_id_deal_id_ix ON portfolio_b (man_id, d_id);

--
-- other indexes?
--
WITH dummy (man_id, d_id, m_type, m_dt, m_amount)  AS 
(
  VALUES (1, 1000, 'D', NOW(), 111111111)
)
SELECT
        -- COALESCE(p1.portfolio_id) AS p_id,
        p1.m_dt::TIMESTAMP(0),
        d.man_id, 
        d.d_id, 
        d.m_type, 
        d.m_dt::TIMESTAMP(0), 
        -- d.m_amount,      
        CASE
          WHEN d.m_type = 'D' THEN d.m_amount
          ELSE                    (d.m_amount * -1)
        END AS ins_amount,
        
        COALESCE(p1.d_tot_prev, 0) AS p_tot_prev,
        
        COALESCE(p1.d_tot, 0)         AS p_tot,
        
        (SELECT COALESCE(SUM(ABS(p.m_amount)), 0) + d.m_amount FROM portfolio_b p)
        
FROM dummy d
  LEFT JOIN portfolio_b p1
  ON d.man_id = p1.man_id AND d.d_id = p1.d_id
ORDER BY p1.m_dt DESC
LIMIT 1;


m_dt
man_id
d_id
m_type
m_dt
ins_amount
p_tot_prev
p_tot
?column?


null
1
1000
D
2021-05-06 14:04:18
111111111
0
0
111111111


WITH dummy (man_id, d_id, m_type, m_dt, m_amount)  AS 
(
  VALUES (1, 1000, 'D', NOW(), 33)
)
INSERT INTO portfolio_b 
             (man_id, d_id, m_type, m_dt, m_amount, d_tot_prev, d_tot_all_trans)
SELECT 
        d.man_id, 
        d.d_id, 
        d.m_type, 
        d.m_dt, 
        -- d.m_amount,      
        CASE
          WHEN d.m_type = 'D' THEN d.m_amount
          ELSE                    (d.m_amount * -1)
        END AS x,
        
        COALESCE(p1.d_tot, 0),

       (SELECT COALESCE(SUM(ABS(p.m_amount)), 0) + d.m_amount FROM portfolio_b p)

FROM dummy d
  LEFT JOIN portfolio_b p1
  ON d.man_id = p1.man_id AND d.d_id = p1.d_id
ORDER BY p1.m_dt DESC
LIMIT 1;
1 rows affected

SELECT portfolio_id AS p_id, man_id AS mid, d_id, m_type AS typ, m_dt::TIMESTAMP(0), 
m_amount AS amount, d_tot_prev AS t_prev, d_tot, d_tot_all_trans AS t_t FROM portfolio_b;


p_id
mid
d_id
typ
m_dt
amount
t_prev
d_tot
t_t


1
1
1000
D
2021-05-06 14:04:18
33
0
33
33


WITH dummy (man_id, d_id, m_type, m_dt, m_amount)  AS 
(
  VALUES (1, 1000, 'D', NOW(), 1111111)
)
SELECT 
        d.man_id, 
        d.d_id, 
        d.m_type, 
        d.m_dt, 
        -- d.m_amount,      
        CASE
          WHEN d.m_type = 'D' THEN d.m_amount
          ELSE                    (d.m_amount * -1)
        END AS ins_amount,
        
        COALESCE(p1.d_tot_prev, 0) AS p_tot_prev,
        
        COALESCE(p1.d_tot, 0)         AS p_tot,

       (SELECT COALESCE(SUM(ABS(p.m_amount)), 0) + d.m_amount FROM portfolio_b p)

FROM dummy d
  LEFT JOIN portfolio_b p1
  ON d.man_id = p1.man_id AND d.d_id = p1.d_id
ORDER BY p1.m_dt DESC
LIMIT 1;


man_id
d_id
m_type
m_dt
ins_amount
p_tot_prev
p_tot
?column?


1
1000
D
2021-05-06 14:04:17.877629+01
1111111
0
33
1111144


WITH dummy (man_id, d_id, m_type, m_dt, m_amount)  AS 
(
  VALUES (1, 1000, 'C', NOW(), 13)
)
INSERT INTO portfolio_b 
              (man_id, d_id, m_type, m_dt, m_amount, d_tot_prev, d_tot_all_trans)
SELECT 
        d.man_id, 
        d.d_id, 
        d.m_type, 
        d.m_dt, 
        -- d.m_amount,      
        CASE
          WHEN d.m_type = 'D' THEN d.m_amount
          ELSE                    (d.m_amount * -1)
        END AS x,
        
        COALESCE(p1.d_tot, 0),
        
       (SELECT COALESCE(SUM(ABS(p.m_amount)), 0) + d.m_amount FROM portfolio_b p)

FROM dummy d
  LEFT JOIN portfolio_b p1
  ON d.man_id = p1.man_id AND d.d_id = p1.d_id
ORDER BY p1.m_dt DESC
LIMIT 1;
1 rows affected

SELECT portfolio_id AS p_id, man_id AS mid, d_id, m_type AS typ, m_dt::TIMESTAMP(0), 
m_amount AS amount, d_tot_prev AS t_prev, d_tot, d_tot_all_trans AS t_t FROM portfolio_b;


p_id
mid
d_id
typ
m_dt
amount
t_prev
d_tot
t_t


1
1
1000
D
2021-05-06 14:04:18
33
0
33
33

2
1
1000
C
2021-05-06 14:04:18
-13
33
20
46


WITH dummy (man_id, d_id, m_type, m_dt, m_amount)  AS 
(
  VALUES (1, 1000, 'D', NOW(), 11111111)
)
SELECT 
        p1.portfolio_id,
        d.man_id, 
        d.d_id, 
        d.m_type, 
        d.m_dt, 
        -- d.m_amount,      
        CASE
          WHEN d.m_type = 'D' THEN d.m_amount
          ELSE                    (d.m_amount * -1)
        END AS ins_amount,
        
        COALESCE(p1.d_tot, 0) AS p_tot_prev,
        
        (CASE
          WHEN d.m_type = 'D' THEN d.m_amount
          ELSE                    (d.m_amount * -1)
        END) + p1.d_tot AS total       
        

FROM dummy d
  LEFT JOIN portfolio_b p1
  ON d.man_id = p1.man_id AND d.man_id = p1.man_id
ORDER BY p1.man_id DESC
LIMIT 1;


portfolio_id
man_id
d_id
m_type
m_dt
ins_amount
p_tot_prev
total


1
1
1000
D
2021-05-06 14:04:17.879911+01
11111111
33
11111144


WITH dummy (man_id, d_id, m_type, m_dt, m_amount)  AS 
(
  VALUES (1, 1000, 'D', NOW(), 30)
)
INSERT INTO portfolio_b 
              (man_id, d_id, m_type, m_dt, m_amount, d_tot_prev, d_tot_all_trans)
SELECT 
        d.man_id, 
        d.d_id, 
        d.m_type, 
        d.m_dt, 
        -- d.m_amount,      
        CASE
          WHEN d.m_type = 'D' THEN d.m_amount
          ELSE                    (d.m_amount * -1)
        END AS x,
       
        COALESCE(p1.d_tot, 0),

       (SELECT COALESCE(SUM(ABS(p.m_amount)), 0) + d.m_amount FROM portfolio_b p)

FROM dummy d
  LEFT JOIN portfolio_b p1
  ON d.man_id = p1.man_id AND d.d_id = p1.d_id
ORDER BY p1.man_id DESC
LIMIT 1;
1 rows affected

SELECT portfolio_id AS p_id, man_id AS mid, d_id, m_type AS typ, m_dt::TIMESTAMP(0), 
m_amount AS amount, d_tot_prev AS t_prev, d_tot, d_tot_all_trans AS t_t FROM portfolio_b;


p_id
mid
d_id
typ
m_dt
amount
t_prev
d_tot
t_t


1
1
1000
D
2021-05-06 14:04:18
33
0
33
33

2
1
1000
C
2021-05-06 14:04:18
-13
33
20
46

3
1
1000
D
2021-05-06 14:04:18
30
20
50
76


WITH dummy (man_id, d_id, m_type, m_dt, m_amount)  AS 
(
  VALUES (1, 1000, 'D', NOW(), 1)
)
SELECT p1.portfolio_id, d.* FROM dummy d
 LEFT JOIN portfolio_b p1
  ON d.man_id = p1.man_id AND d.d_id = p1.d_id
ORDER BY p1.man_id DESC
LIMIT 1;


portfolio_id
man_id
d_id
m_type
m_dt
m_amount


3
1
1000
D
2021-05-06 14:04:17.882751+01
1


WITH dummy (man_id, d_id, m_type, m_dt, m_amount)  AS 
(
  VALUES (1, 1000, 'D', NOW(), 75)
)
INSERT INTO portfolio_b 
               (man_id, d_id, m_type, m_dt, m_amount, d_tot_prev, d_tot_all_trans)
SELECT 
        d.man_id, 
        d.d_id, 
        d.m_type, 
        d.m_dt, 
        -- d.m_amount,      
        CASE
          WHEN d.m_type = 'D' THEN d.m_amount
          ELSE                    (d.m_amount * -1)
        END AS x,
        
        COALESCE(p1.d_tot, 0),
        
       (SELECT COALESCE(SUM(ABS(p.m_amount)), 0) + d.m_amount FROM portfolio_b p)
        
FROM dummy d
  LEFT JOIN portfolio_b p1
  ON d.man_id = p1.man_id AND d.d_id = p1.d_id
ORDER BY p1.man_id DESC
LIMIT 1;
1 rows affected

SELECT portfolio_id AS p_id, man_id AS mid, d_id, m_type AS typ, m_dt::TIMESTAMP(0), 
m_amount AS amount, d_tot_prev AS t_prev, d_tot, d_tot_all_trans AS t_t FROM portfolio_b;


p_id
mid
d_id
typ
m_dt
amount
t_prev
d_tot
t_t


1
1
1000
D
2021-05-06 14:04:18
33
0
33
33

2
1
1000
C
2021-05-06 14:04:18
-13
33
20
46

3
1
1000
D
2021-05-06 14:04:18
30
20
50
76

4
1
1000
D
2021-05-06 14:04:18
75
50
125
151


WITH dummy (man_id, d_id, m_type, m_dt, m_amount)  AS 
(
  VALUES (1, 1000, 'C', NOW(), 25)
)
INSERT INTO portfolio_b 
              (man_id, d_id, m_type, m_dt, m_amount, d_tot_prev, d_tot_all_trans)
SELECT 
        d.man_id, 
        d.d_id, 
        d.m_type, 
        d.m_dt, 
        -- d.m_amount,      
        CASE
          WHEN d.m_type = 'D' THEN d.m_amount
          ELSE                    (d.m_amount * -1)
        END AS x,
        
        COALESCE(p1.d_tot, 0),
        
       (SELECT COALESCE(SUM(ABS(p.m_amount)), 0) + d.m_amount FROM portfolio_b p)
        
FROM dummy d
  LEFT JOIN portfolio_b p1
  ON d.man_id = p1.man_id AND d.d_id = p1.d_id
ORDER BY p1.m_dt DESC
LIMIT 1;
1 rows affected

SELECT portfolio_id AS p_id, man_id AS mid, d_id, m_type AS typ, m_dt::TIMESTAMP(0), 
m_amount AS amount, d_tot_prev AS t_prev, d_tot, d_tot_all_trans AS t_t FROM portfolio_b;


p_id
mid
d_id
typ
m_dt
amount
t_prev
d_tot
t_t


1
1
1000
D
2021-05-06 14:04:18
33
0
33
33

2
1
1000
C
2021-05-06 14:04:18
-13
33
20
46

3
1
1000
D
2021-05-06 14:04:18
30
20
50
76

4
1
1000
D
2021-05-06 14:04:18
75
50
125
151

5
1
1000
C
2021-05-06 14:04:18
-25
125
100
176


select * from portfolio_b ;


portfolio_id
man_id
d_id
m_type
m_dt
m_amount
d_tot_prev
d_tot
d_tot_all_trans


1
1
1000
D
2021-05-06 14:04:17.874563+01
33
0
33
33

2
1
1000
C
2021-05-06 14:04:17.878105+01
-13
33
20
46

3
1
1000
D
2021-05-06 14:04:17.880601+01
30
20
50
76

4
1
1000
D
2021-05-06 14:04:17.883123+01
75
50
125
151

5
1
1000
C
2021-05-06 14:04:17.884694+01
-25
125
100
176


INSERT INTO portfolio_b 
              (man_id, d_id, m_type, m_dt, m_amount, d_tot_prev, d_tot_all_trans)
VALUES
  (1, 1000, 'C', '2000-01-01', 50, -29, 17),
  (1, 1000, 'D', '2000-01-02', 20, 0, 0),
  (1, 1000, 'C', '2000-01-03', 30, 100, -100) ;
3 rows affected

select * from portfolio_b ;


portfolio_id
man_id
d_id
m_type
m_dt
m_amount
d_tot_prev
d_tot
d_tot_all_trans


1
1
1000
D
2021-05-06 14:04:17.874563+01
33
0
33
33

2
1
1000
C
2021-05-06 14:04:17.878105+01
-13
33
20
46

3
1
1000
D
2021-05-06 14:04:17.880601+01
30
20
50
76

4
1
1000
D
2021-05-06 14:04:17.883123+01
75
50
125
151

5
1
1000
C
2021-05-06 14:04:17.884694+01
-25
125
100
176

6
1
1000
C
2000-01-01 00:00:00+00
50
-29
21
17

7
1
1000
D
2000-01-02 00:00:00+00
20
0
20
0

8
1
1000
C
2000-01-03 00:00:00+00
30
100
130
-100
manager_id	manager_name
1	N'Ketchi Obaya
2	Sean C4itizen
3	Joe Sixpack
4	Joan Doe
5	Oisín O'Shea
p_id	mid	d_id	typ	m_dt	amount	t_prev	d_tot	t_t
1	1	1000	D	2021-05-06 14:04:18	33	0	33	33
2	1	1000	C	2021-05-06 14:04:18	-13	33	20	46
portfolio_id	man_id	d_id	m_type	m_dt	m_amount	d_tot_prev	d_tot	d_tot_all_trans
1	1	1000	D	2021-05-06 14:04:17.874563+01	33	0	33	33
2	1	1000	C	2021-05-06 14:04:17.878105+01	-13	33	20	46
3	1	1000	D	2021-05-06 14:04:17.880601+01	30	20	50	76
4	1	1000	D	2021-05-06 14:04:17.883123+01	75	50	125	151
5	1	1000	C	2021-05-06 14:04:17.884694+01	-25	125	100	176