clear markdown compare help best fiddles feedback dbanow.uk a leap of faith?
clear markdown donate comments/suggestions/bugs a leap of faith? bible365 diddy dollings
By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0. 2335661 fiddles created (27442 in the last week).

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
16 4 4 2016-06-12 2016-12-12 Remarks Jun 12
18 5 3 2016-07-18 2017-07-31
8 5 3 2015-06-21 2016-06-30 Add some test
11 6 4 2017-01-01 2017-07-01
9 7 3 2017-01-01 2018-01-31
 hidden batch(es)


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 );
 hidden batch(es)


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
 hidden batch(es)


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
2 10 2016-06-12 2016-12-12 Remarks Jun 12
3 8 2016-07-18 2017-07-31
4 8 2015-06-21 2016-06-30 Add some test
5 11 2017-01-01 2017-07-01
6 9 2017-01-01 2018-01-31
 hidden batch(es)