Postgres 13

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 test (name CHAR(3), key CHAR(2), uuid CHAR(36));
INSERT INTO test VALUES
('na1',	'k1',	NULL),
('na2',	'k2',	NULL),
('na3',	'k1',	NULL);
SELECT * FROM test;

CREATE TABLE



INSERT 0 3




name
key
uuid


na1
k1
null

na2
k2
null

na3
k1
null



SELECT 3


WITH cte AS ( SELECT DISTINCT key, GEN_RANDOM_UUID() uuid
              FROM test )
UPDATE test
SET uuid = cte.uuid
FROM cte
WHERE test.key = cte.key;

UPDATE 3


SELECT * FROM test;


name
key
uuid


na1
k1
f14d488d-a2c4-4caa-972f-123fd04fad16

na2
k2
32d6eb00-b591-4e7a-91f0-4f12eb157ed3

na3
k1
f14d488d-a2c4-4caa-972f-123fd04fad16



SELECT 3

name	key	uuid
na1	k1	f14d488d-a2c4-4caa-972f-123fd04fad16
na2	k2	32d6eb00-b591-4e7a-91f0-4f12eb157ed3
na3	k1	f14d488d-a2c4-4caa-972f-123fd04fad16