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 TABLE elbat
             (original_id integer,
              duplicate_id integer,
              tag text);
INSERT INTO elbat
            (original_id,
             duplicate_id)
            SELECT n,
                   n
                   FROM generate_series(1, 10) gs(n);
10 rows affected

SELECT original_id,
       duplicate_id,
       CASE
         WHEN rn <= (SELECT count(*) / 2
                            FROM elbat) THEN
           'control'
         ELSE
           'treatment'
       END tag
       FROM (SELECT original_id,
                    duplicate_id,
                    row_number() OVER (ORDER BY random()) rn
                    FROM elbat) x;


original_id
duplicate_id
tag


3
3
control

6
6
control

2
2
control

5
5
control

9
9
control

1
1
treatment

7
7
treatment

4
4
treatment

10
10
treatment

8
8
treatment


UPDATE elbat t
       SET tag = CASE
                   WHEN rn <= (SELECT count(*) / 2
                                      FROM elbat) THEN
                     'control'
                   ELSE
                     'treatment'
                 END
       FROM (SELECT original_id,
                    duplicate_id,
                    row_number() OVER (ORDER BY random()) rn
                    FROM elbat) x
       WHERE x.original_id = t.original_id
             AND x.duplicate_id = t.duplicate_id;
10 rows affected

SELECT *
       FROM elbat;


original_id
duplicate_id
tag


1
1
treatment

2
2
treatment

3
3
control

4
4
control

5
5
control

6
6
treatment

7
7
treatment

8
8
treatment

9
9
control

10
10
control

original_id	duplicate_id	tag
3	3	control
6	6	control
2	2	control
5	5	control
9	9	control
1	1	treatment
7	7	treatment
4	4	treatment
10	10	treatment
8	8	treatment