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?
--
--
SELECT pg_encoding_to_char(encoding) FROM pg_database;


pg_encoding_to_char


UTF8

UTF8

UTF8

UTF8

UTF8

UTF8

UTF8

UTF8

UTF8

UTF8

UTF8

UTF8

UTF8

UTF8

UTF8

UTF8

UTF8

UTF8

UTF8

UTF8

UTF8

UTF8

UTF8

UTF8

UTF8

UTF8

UTF8

UTF8

UTF8

UTF8

UTF8

UTF8

UTF8

UTF8

UTF8

UTF8

UTF8

UTF8

UTF8

UTF8

UTF8

UTF8

UTF8

UTF8

UTF8

UTF8

UTF8

UTF8

UTF8

UTF8

UTF8

UTF8

UTF8

UTF8

UTF8

UTF8

UTF8

UTF8

UTF8


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à-öø-ÿő \- \'']+[a-zà-öø-ÿő]$')  -- 4? last character small letter


 -- CONSTRAINT m_name_ck  CHECK 
  --  (manager_name ~ '^[[:upper:]][[:lower:]]+$')
    
    
-- https://stackoverflow.com/a/39706636/470530    
    
    -- [[[:upper:]][[:lower:]]] \- \'']+[[:lower:]]$')  -- 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'),
('Seán Citizen'),  -- want Seán Citizen
('Joe Sixpack'),
('Joan Doe'),
('Oisín O''Shea');  -- want Oisín O'Shea
SELECT * FROM manager;
4 rows affected



manager_id
manager_name


1
Seán Citizen

2
Joe Sixpack

3
Joan Doe

4
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 15:27:38
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 15:27:38
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 15:27:37.916521+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 15:27:38
33
0
33
33

2
1
1000
C
2021-05-06 15:27:38
-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 15:27:37.918861+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 15:27:38
33
0
33
33

2
1
1000
C
2021-05-06 15:27:38
-13
33
20
46

3
1
1000
D
2021-05-06 15:27:38
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 15:27:37.921528+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 15:27:38
33
0
33
33

2
1
1000
C
2021-05-06 15:27:38
-13
33
20
46

3
1
1000
D
2021-05-06 15:27:38
30
20
50
76

4
1
1000
D
2021-05-06 15:27:38
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 15:27:38
33
0
33
33

2
1
1000
C
2021-05-06 15:27:38
-13
33
20
46

3
1
1000
D
2021-05-06 15:27:38
30
20
50
76

4
1
1000
D
2021-05-06 15:27:38
75
50
125
151

5
1
1000
C
2021-05-06 15:27:38
-25
125
100
176
p_id	mid	d_id	typ	m_dt	amount	t_prev	d_tot	t_t
1	1	1000	D	2021-05-06 15:27:38	33	0	33	33
2	1	1000	C	2021-05-06 15:27:38	-13	33	20	46