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 courses
(id serial not null primary key,
nazwa text,
max_participants int);
CREATE TABLE
create table participations
(id serial not null primary key,
course_id integer not null references courses(id),
uczestnik text /* dla ułatwienia */
);
CREATE TABLE
insert into courses(nazwa, max_participants) values('posgtreSQL 1', 20), ('podstawy BD', 10);
INSERT 0 2
insert into participations(course_id, uczestnik) values(1, 'Człowiek'), (1, 'Kursant'), (1, 'Uczeń'), (1, 'Stażysta');
INSERT 0 4
SELECT max_participants - participations AS free_places FROM
(
SELECT max_participants, COUNT(participations.id) AS participations FROM courses
INNER JOIN participations ON participations.course_id = courses.id
GROUP BY courses.max_participants, participations.course_id
ORDER BY participations.course_id
) AS course_places;
| free_places |
|---|
| 16 |
SELECT 1