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?.
select version();
| version |
|---|
| PostgreSQL 16.0 (Debian 16.0-1.pgdg120+1) on x86_64-pc-linux-gnu, compiled by gcc (Debian 12.2.0-14) 12.2.0, 64-bit |
SELECT 1
CREATE TABLE notification_detail (
id SERIAL PRIMARY KEY,
activity VARCHAR(255) NOT NULL,
channel VARCHAR(255), -- Assuming channel is a string that represents a type of communication
description TEXT
);
INSERT INTO notification_detail (activity, channel, description) VALUES
('login', 'email', 'User logged in'),
('login', 'sms', 'User logged in'),
('login', 'push', 'User logged in'),
('purchase', 'email', 'User made a purchase'),
('purchase', 'sms', 'User made a purchase'),
('logout', 'email', 'User logged out'),
('logout', 'sms', 'User logged out');
select * from notification_detail
CREATE TABLE
INSERT 0 7
| id | activity | channel | description |
|---|---|---|---|
| 1 | login | User logged in | |
| 2 | login | sms | User logged in |
| 3 | login | push | User logged in |
| 4 | purchase | User made a purchase | |
| 5 | purchase | sms | User made a purchase |
| 6 | logout | User logged out | |
| 7 | logout | sms | User logged out |
SELECT 7
SELECT activity,
COALESCE(json_agg(channel), '[]') AS channels,
description
FROM notification_detail
GROUP BY activity, description;
| activity | channels | description |
|---|---|---|
| purchase | ["email", "sms"] | User made a purchase |
| login | ["email", "sms", "push"] | User logged in |
| logout | ["email", "sms"] | User logged out |
SELECT 3