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 users
(
id bigserial not null
constraint users_pkey
primary key
);
create table user_has_bonuses
(
uhb_id bigserial not null
constraint user_has_bonuses_pkey
primary key,
user_id integer not null,
bonus_id integer not null
);
create table bonuses
(
bonus_id bigserial not null
constraint bonuses_pkey
primary key
);
INSERT INTO users (id) VALUES (10);
INSERT INTO users (id) VALUES (11);
INSERT INTO bonuses (bonus_id) VALUES (1);
INSERT INTO bonuses (bonus_id) VALUES (2);
INSERT INTO user_has_bonuses (user_id, bonus_id) VALUES (10, 1);
INSERT INTO user_has_bonuses (user_id, bonus_id) VALUES (11, 1);
INSERT INTO user_has_bonuses (user_id, bonus_id) VALUES (10, 2);
TABLE bonuses;
TABLE user_has_bonuses;
TABLE users;
1 rows affected
1 rows affected
1 rows affected
1 rows affected
1 rows affected
1 rows affected
1 rows affected
| bonus_id |
|---|
| 1 |
| 2 |
| uhb_id | user_id | bonus_id |
|---|---|---|
| 1 | 10 | 1 |
| 2 | 11 | 1 |
| 3 | 10 | 2 |
| id |
|---|
| 10 |
| 11 |
SELECT users.id as user_ids,
(SELECT
ARRAY_AGG(DISTINCT CONCAT(user_has_bonuses.bonus_id)) as bonus_ids
FROM user_has_bonuses
WHERE user_has_bonuses.user_id = users.id) as BONUS_IDS,
(SELECT
ARRAY_AGG(DISTINCT CONCAT(bonuses.bonus_id))
FROM bonuses
WHERE bonuses.bonus_id IN (BONUS_IDS)
) AS bonusIds
FROM users;
ERROR: column "bonus_ids" does not exist
LINE 9: WHERE bonuses.bonus_id IN (BONUS_IDS)
^
HINT: Perhaps you meant to reference the column "bonuses.bonus_id".