Postgres 17

By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.

CREATE TABLE projects (  
  project_id serial PRIMARY KEY,  
  project_name text NOT NULL,  
  status text NOT NULL  
);  
  
CREATE TABLE team_members (  
  member_id text PRIMARY KEY,  
  name text NOT NULL  
);  
  
CREATE TABLE project_team (  
  project_id int REFERENCES projects(project_id),  
  member_id text REFERENCES team_members(member_id),  
  role text,  
  PRIMARY KEY (project_id, member_id)  
);  

CREATE TABLE



CREATE TABLE



CREATE TABLE


INSERT INTO projects (project_id, project_name, status) VALUES  
  (1, 'Troubleshooting PostgreSQL issues', 'active'),  
  (2, 'Build new apps with MongoDB', 'planned');  
INSERT INTO team_members (member_id, name) VALUES  
  ('cyclops', 'Cyclops'),  
  ('wolverine', 'Wolverine'),  
  ('storm', 'Storm'),  
  ('beast', 'Beast'),  
  ('tony', 'Tony');  
-- Project 1 team  
INSERT INTO project_team (project_id, member_id, role) VALUES  
  (1, 'cyclops', 'Postgres Expert'),  
  (1, 'wolverine', 'Consultant'),  
  (1, 'storm', 'DBA'),  
  (1, 'beast', 'Developer'),  
  (1, 'tony', 'Architect');  
  
-- Project 2 team  
INSERT INTO project_team (project_id, member_id, role) VALUES  
  (2, 'tony', 'Developer');  

INSERT 0 2



INSERT 0 5



INSERT 0 5



INSERT 0 1


SELECT  
  tm.member_id,  
  tm.name,  
  ARRAY_AGG(p.project_name ORDER BY p.project_id) AS projects  
FROM  
  team_members tm  
  JOIN project_team pt ON tm.member_id = pt.member_id  
  JOIN projects p ON pt.project_id = p.project_id  
GROUP BY  
  tm.member_id, tm.name  
ORDER BY  
  tm.member_id;  


member_id
name
projects


beast
Beast
{"Troubleshooting PostgreSQL issues"}

cyclops
Cyclops
{"Troubleshooting PostgreSQL issues"}

storm
Storm
{"Troubleshooting PostgreSQL issues"}

tony
Tony
{"Troubleshooting PostgreSQL issues","Build new apps with MongoDB"}

wolverine
Wolverine
{"Troubleshooting PostgreSQL issues"}



SELECT 5

member_id	name	projects
beast	Beast	{"Troubleshooting PostgreSQL issues"}
cyclops	Cyclops	{"Troubleshooting PostgreSQL issues"}
storm	Storm	{"Troubleshooting PostgreSQL issues"}
tony	Tony	{"Troubleshooting PostgreSQL issues","Build new apps with MongoDB"}
wolverine	Wolverine	{"Troubleshooting PostgreSQL issues"}