Postgres 9.6

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 TEMP TABLE tmp (
  curid int
, cuid  int
, rtid  int
, cd    date
, dd    date
, rm    text);

INSERT INTO tmp VALUES
  (10, 4, 4, '2016-01-02', '2016-07-02', NULL)  
 ,(16, 4, 4, '2016-06-12', '2016-12-12', 'Remarks Jun 12')
 ,(18, 5, 3, '2016-07-18', '2017-07-31', NULL)  
 ,(8 , 5, 3, '2015-06-21', '2016-06-30', 'Add some test')
 ,(11, 6, 4, '2017-01-01', '2017-07-01', NULL)
 ,(9 , 7, 3, '2017-01-01', '2018-01-31', NULL)
RETURNING *;


curid
cuid
rtid
cd
dd
rm


10
4
4
2016-01-02
2016-07-02
null

16
4
4
2016-06-12
2016-12-12
Remarks Jun 12

18
5
3
2016-07-18
2017-07-31
null

8
5
3
2015-06-21
2016-06-30
Add some test

11
6
4
2017-01-01
2017-07-01
null

9
7
3
2017-01-01
2018-01-31
null


CREATE TEMP TABLE a (
   id    serial
 , curid int  -- UNIQUE?
 , cuid  int
 , rtid  int
);

CREATE TEMP TABLE b (
   id    serial
 , curid int
 , cd    date
 , dd    date
 , rm    text
);
INSERT INTO a (curid, cuid, rtid)
SELECT DISTINCT ON (cuid, rtid)
       curid, cuid, rtid
FROM   tmp
ORDER  BY  cuid, rtid, curid  -- pick smallest curid per group
RETURNING *;


id
curid
cuid
rtid


1
10
4
4

2
8
5
3

3
11
6
4

4
9
7
3


INSERT INTO b (curid, cd, dd, rm)
SELECT min(curid) OVER (PARTITION BY cuid, rtid), cd, dd, rm
FROM   tmp
ORDER  BY cuid, rtid  -- optional
RETURNING *;


id
curid
cd
dd
rm


1
10
2016-01-02
2016-07-02
null

2
10
2016-06-12
2016-12-12
Remarks Jun 12

3
8
2016-07-18
2017-07-31
null

4
8
2015-06-21
2016-06-30
Add some test

5
11
2017-01-01
2017-07-01
null

6
9
2017-01-01
2018-01-31
null

curid	cuid	rtid	cd	dd	rm
10	4	4	2016-01-02	2016-07-02	null
16	4	4	2016-06-12	2016-12-12	Remarks Jun 12
18	5	3	2016-07-18	2017-07-31	null
8	5	3	2015-06-21	2016-06-30	Add some test
11	6	4	2017-01-01	2017-07-01	null
9	7	3	2017-01-01	2018-01-31	null