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 person (
pid int
, name text
, dob date
, younger_sibling_name text
, younger_sibling_dob date
);
-- proper test case with successive twins
INSERT INTO person (pid, name, dob) VALUES
(1, 'John2' , '1980-01-05') -- twins 1
, (2, 'Jimmy' , '1975-04-25')
, (3, 'Sarah' , '2004-02-10') -- twins 2
, (4, 'Frank' , '1934-12-12')
, (5, 'John' , '1980-01-05') -- twins 1
, (6, 'Sarah2', '2004-02-10') -- twins 2
, (7, 'Xaver' , '2005-02-10')
, (8, 'Zulu' , '2006-02-10')
;
CREATE TABLE
INSERT 0 8
-- NEW variant (recommended!)
BEGIN;
UPDATE person p
SET younger_sibling_name = y.name
, younger_sibling_dob = y.dob
FROM (
SELECT dob, name, lead(dob) OVER (ORDER BY dob) AS next_dob
FROM (
SELECT DISTINCT ON (dob)
dob, name
FROM person p
ORDER BY dob, name -- or any other additional ORDER BY items to break ties
) sub
) y
WHERE p.dob = y.next_dob;
SELECT * FROM person ORDER BY dob;
ROLLBACK; -- to reuse test case in following tests
BEGIN
UPDATE 7
| pid | name | dob | younger_sibling_name | younger_sibling_dob |
|---|---|---|---|---|
| 4 | Frank | 1934-12-12 | null | null |
| 2 | Jimmy | 1975-04-25 | Frank | 1934-12-12 |
| 5 | John | 1980-01-05 | Jimmy | 1975-04-25 |
| 1 | John2 | 1980-01-05 | Jimmy | 1975-04-25 |
| 3 | Sarah | 2004-02-10 | John | 1980-01-05 |
| 6 | Sarah2 | 2004-02-10 | John | 1980-01-05 |
| 7 | Xaver | 2005-02-10 | Sarah | 2004-02-10 |
| 8 | Zulu | 2006-02-10 | Xaver | 2005-02-10 |
SELECT 8
ROLLBACK
-- OLD variant 1
BEGIN;
WITH cte AS (
SELECT dob, min(name) AS name
, row_number() OVER (ORDER BY dob) rn
FROM person p
GROUP BY dob
)
UPDATE person p
SET younger_sibling_name = y.name
, younger_sibling_dob = y.dob
FROM cte x
JOIN cte y ON y.rn = x.rn - 1
WHERE p.dob = x.dob;
SELECT * FROM person ORDER BY dob;
ROLLBACK;
BEGIN
UPDATE 7
| pid | name | dob | younger_sibling_name | younger_sibling_dob |
|---|---|---|---|---|
| 4 | Frank | 1934-12-12 | null | null |
| 2 | Jimmy | 1975-04-25 | Frank | 1934-12-12 |
| 5 | John | 1980-01-05 | Jimmy | 1975-04-25 |
| 1 | John2 | 1980-01-05 | Jimmy | 1975-04-25 |
| 3 | Sarah | 2004-02-10 | John | 1980-01-05 |
| 6 | Sarah2 | 2004-02-10 | John | 1980-01-05 |
| 7 | Xaver | 2005-02-10 | Sarah | 2004-02-10 |
| 8 | Zulu | 2006-02-10 | Xaver | 2005-02-10 |
SELECT 8
ROLLBACK
-- OLD variant 3
WITH cte AS (
SELECT *, dense_rank() OVER (ORDER BY dob) AS drk
FROM person
)
UPDATE person p
SET younger_sibling_name = y.name
, younger_sibling_dob = y.dob
FROM cte x
JOIN (SELECT DISTINCT ON (drk) * FROM cte) y ON y.drk = x.drk - 1
WHERE x.pid = p.pid;
SELECT * FROM person ORDER BY dob;
UPDATE 7
| pid | name | dob | younger_sibling_name | younger_sibling_dob |
|---|---|---|---|---|
| 4 | Frank | 1934-12-12 | null | null |
| 2 | Jimmy | 1975-04-25 | Frank | 1934-12-12 |
| 5 | John | 1980-01-05 | Jimmy | 1975-04-25 |
| 1 | John2 | 1980-01-05 | Jimmy | 1975-04-25 |
| 3 | Sarah | 2004-02-10 | John2 | 1980-01-05 |
| 6 | Sarah2 | 2004-02-10 | John2 | 1980-01-05 |
| 7 | Xaver | 2005-02-10 | Sarah2 | 2004-02-10 |
| 8 | Zulu | 2006-02-10 | Xaver | 2005-02-10 |
SELECT 8