Postgres 11

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 transactions(
id SERIAL NOT NULL PRIMARY KEY,
value NUMERIC(10,3),
note TEXT,
group_id INTEGER);
CREATE FUNCTION upd_group() 
RETURNS trigger AS 
$upd_group$

DECLARE 

    max_group_id_ INTEGER := 0;

BEGIN

    SELECT MAX(group_id) INTO max_group_id_ FROM transactions;
    max_group_id_ := COALESCE(max_group_id_, 0) + 1;

    UPDATE transactions
    SET    group_id = max_group_id_
    FROM inserted
    WHERE  inserted.id = transactions.id
      AND  inserted.group_id IS NULL ;
    
    RETURN NULL;

END;
$upd_group$ language plpgsql;
CREATE TRIGGER trg_upd_group AFTER INSERT ON transactions
    REFERENCING NEW TABLE AS inserted
    FOR EACH STATEMENT
    EXECUTE FUNCTION upd_group();
INSERT INTO transactions (value, note, group_id)
VALUES
(12, 'note 12', NULL),
(13, 'note 13', 10),
(14, 'note 14', NULL),
(15, 'note 15', NULL);
4 rows affected

SELECT * FROM transactions;


id
value
note
group_id


2
13.000
note 13
10

1
12.000
note 12
11

3
14.000
note 14
11

4
15.000
note 15
11


INSERT INTO transactions (value, note, group_id)
VALUES
(112, 'note 112', NULL),
(113, 'note 113', NULL),
(114, 'note 114', NULL),
(115, 'note 115', NULL);
4 rows affected

SELECT * FROM transactions;


id
value
note
group_id


2
13.000
note 13
10

1
12.000
note 12
11

3
14.000
note 14
11

4
15.000
note 15
11

5
112.000
note 112
12

6
113.000
note 113
12

7
114.000
note 114
12

8
115.000
note 115
12

id	value	note	group_id
2	13.000	note 13	10
1	12.000	note 12	11
3	14.000	note 14	11
4	15.000	note 15	11