Example of a tree-like structure in SQL (specifically Postgres)

tree_insert.sql

SET search_path TO 'example';

INSERT INTO genre (name) VALUES ('Rock'), ('Classic Rock'), ('Metal'), ('Progressive Metal'), ('Arena Rock'), ('Alternative Metal'), ('Grunge');
INSERT INTO category (name) VALUES ('Book'), ('Music');
INSERT INTO format (name) VALUES ('Compact Disc'), ('Vinyl');

INSERT INTO band (year_formed, name) VALUES (1985, 'Majesty'), (1988, 'Dream Theater'), (1990, 'Tool'), (1970, 'Queen'), (1987, 'Alice in Chains');

INSERT INTO artist (first_name, last_name) VALUES ('John', 'Petrucci'), ('John', 'Myung'), ('James', 'LaBrie'), ('Jordan', 'Ruddess'), ('Mike', 'Portnoy'), ('Mike', 'Mangini');
INSERT INTO artist (first_name, last_name, prefix, suffix) VALUES ('Brian', 'May', 'Sir', 'CBE');
INSERT INTO artist (first_name, last_name, suffix) VALUES ('Roger', 'Taylor', 'OBE');
INSERT INTO artist (first_name, last_name) VALUES ('Freddie', 'Mercury'), ('John', 'Deacon');
INSERT INTO artist (first_name, last_name) VALUES ('Jerry', 'Cantrell'), ('Sean', 'Kinney'), ('Layne', 'Staley'), ('Mike', 'Starr'), ('Mike', 'Inez'), ('Maynard', 'Keenan'), ('Adam', 'Jones'), ('Danny', 'Carey'), ('Justin', 'Chancellor');

INSERT INTO band_artist (band_id, artist_id) SELECT b.id, a.id FROM band b CROSS JOIN artist a WHERE b.name IN ('Dream Theater', 'Majesty') AND a.last_name IN ('Petrucci', 'Myung', 'Portnoy');
INSERT INTO band_artist (band_id, artist_id) SELECT b.id, a.id FROM band b CROSS JOIN artist a WHERE b.name = 'Dream Theater' AND a.last_name IN ('Petrucci', 'Myung', 'Portnoy', 'Ruddess', 'LaBrie', 'Mangini') ON CONFLICT (band_id, artist_id) DO NOTHING;
INSERT INTO band_artist (band_id, artist_id) SELECT b.id, a.id FROM band b CROSS JOIN artist a WHERE b.name = 'Tool' AND a.last_name IN ('Keenan', 'Carey', 'Jones', 'Chancellor');
INSERT INTO band_artist (band_id, artist_id) SELECT b.id, a.id FROM band b CROSS JOIN artist a WHERE b.name = 'Alice in Chains' AND a.last_name IN ('Cantrell', 'Kinney', 'Inez', 'DuVall', 'Staley', 'Starr');

INSERT INTO band_genre (band_id, genre_id) SELECT b.id, g.id FROM band b CROSS JOIN genre g WHERE b.name = 'Queen' AND g.name LIKE '%Rock%';
INSERT INTO band_genre (band_id, genre_id) SELECT b.id, g.id FROM band b CROSS JOIN genre g WHERE b.name = 'Tool' AND g.name IN ('Rock', 'Metal', 'Progressive Metal', 'Alternative Metal');
INSERT INTO band_genre (band_id, genre_id) SELECT b.id, g.id FROM band b CROSS JOIN genre g WHERE b.name IN ('Dream Theater', 'Majesty') AND g.name IN ('Rock', 'Metal', 'Progressive Metal');

INSERT INTO album (band_id, format_id, year_released, name) VALUES ((SELECT id FROM band WHERE name = 'Tool'), (SELECT id FROM format WHERE name = 'Compact Disc'), 1993, 'Undertow');
INSERT INTO album (band_id, format_id, year_released, name) VALUES ((SELECT id FROM band WHERE name = 'Tool'), (SELECT id FROM format WHERE name = 'Vinyl'), 2006, 'Undertow');
INSERT INTO album (band_id, format_id, year_released, name) VALUES ((SELECT id FROM band WHERE name = 'Dream Theater'), (SELECT id FROM format WHERE name = 'Compact Disc'), 2003, 'Train of Thought');
INSERT INTO album (band_id, format_id, year_released, name) VALUES ((SELECT id FROM band WHERE name = 'Dream Theater'), (SELECT id FROM format WHERE name = 'Compact Disc'), 2011, 'A Dramatic Turn of Events');

INSERT INTO album_artist (album_id, artist_id) SELECT a.id, aa.id FROM album a CROSS JOIN artist aa WHERE a.name = 'Train of Thought' AND aa.last_name IN ('Rudess', 'Myung', 'Portnoy', 'LaBrie', 'Petrucci');
INSERT INTO album_artist (album_id, artist_id) SELECT a.id, aa.id FROM album a CROSS JOIN artist aa WHERE a.name = 'A Dramatic Turn of Events' AND aa.last_name IN ('Rudess', 'Myung', 'Mangini', 'LaBrie', 'Petrucci');
INSERT INTO album_artist (album_id, artist_id) SELECT a.id, aa.id FROM album a CROSS JOIN artist aa WHERE a.name = 'Undertow' AND aa.last_name IN ('Keenan', 'Carey', 'Chancellor', 'Jones');

INSERT INTO product (category_id, format_id, entity_id, price) VALUES (2, 1, (SELECT entity_id FROM album WHERE name = 'Undertow' AND year_released = 1993), 12.99);
INSERT INTO product (category_id, format_id, entity_id, price) VALUES (2, 1, (SELECT entity_id FROM album WHERE name = 'Undertow' AND year_released = 2006), 29.99);
INSERT INTO product (category_id, format_id, entity_id, price) VALUES (2, 1, (SELECT entity_id FROM album WHERE name = 'Train of Thought' AND year_released = 2003), 12.99);
INSERT INTO product (category_id, format_id, entity_id, price) VALUES (2, 1, (SELECT entity_id FROM album WHERE name = 'A Dramatic Turn of Events' AND year_released = 2011), 12.99);

tree_output

 product_id | price | category_name | format_name  |        album_name         | year_released | band_or_artist_name |                       artist_contributions
------------+-------+---------------+--------------+---------------------------+---------------+---------------------+-------------------------------------------------------------------
          1 | 12.99 | Music         | Compact Disc | Undertow                  |          1993 | Tool                | {"Adam Jones","Danny Carey","Justin Chancellor","Maynard Keenan"}
          2 | 29.99 | Music         | Compact Disc | Undertow                  |          2006 | Tool                | {"Adam Jones","Danny Carey","Justin Chancellor","Maynard Keenan"}
          3 | 12.99 | Music         | Compact Disc | Train of Thought          |          2003 | Dream Theater       | {"James LaBrie","John Myung","John Petrucci","Mike Portnoy"}
          4 | 12.99 | Music         | Compact Disc | A Dramatic Turn of Events |          2011 | Dream Theater       | {"James LaBrie","John Myung","John Petrucci","Mike Mangini"}
(4 rows)

tree_schema.sql

--
-- PostgreSQL database dump
--

-- Dumped from database version 15.5 (Debian 15.5-0+deb12u1)
-- Dumped by pg_dump version 15.5 (Debian 15.5-0+deb12u1)

SET statement_timeout = 0;
SET lock_timeout = 0;
SET idle_in_transaction_session_timeout = 0;
SET client_encoding = 'UTF8';
SET standard_conforming_strings = on;
SELECT pg_catalog.set_config('search_path', '', false);
SET check_function_bodies = false;
SET xmloption = content;
SET client_min_messages = warning;
SET row_security = off;

--
-- Name: example; Type: SCHEMA; Schema: -; Owner: postgres
--

CREATE SCHEMA example;


ALTER SCHEMA example OWNER TO postgres;

--
-- Name: entity_insert_trigger(); Type: FUNCTION; Schema: example; Owner: postgres
--

CREATE FUNCTION example.entity_insert_trigger() RETURNS trigger
    LANGUAGE plpgsql
    AS $$
BEGIN
    INSERT INTO entity DEFAULT VALUES
    RETURNING id INTO NEW.entity_id;
    RETURN NEW;
END;
$$;


ALTER FUNCTION example.entity_insert_trigger() OWNER TO postgres;

SET default_tablespace = '';

SET default_table_access_method = heap;

--
-- Name: album; Type: TABLE; Schema: example; Owner: postgres
--

CREATE TABLE example.album (
    id integer NOT NULL,
    entity_id bigint NOT NULL,
    band_id integer,
    artist_id integer,
    format_id smallint NOT NULL,
    year_released smallint NOT NULL,
    name character varying(126) NOT NULL,
    CONSTRAINT album_association_chk CHECK (((band_id IS NULL) <> (artist_id IS NULL)))
);


ALTER TABLE example.album OWNER TO postgres;

--
-- Name: album_artist; Type: TABLE; Schema: example; Owner: postgres
--

CREATE TABLE example.album_artist (
    album_id integer NOT NULL,
    artist_id integer NOT NULL
);


ALTER TABLE example.album_artist OWNER TO postgres;

--
-- Name: album_id_seq; Type: SEQUENCE; Schema: example; Owner: postgres
--

ALTER TABLE example.album ALTER COLUMN id ADD GENERATED ALWAYS AS IDENTITY (
    SEQUENCE NAME example.album_id_seq
    START WITH 1
    INCREMENT BY 1
    NO MINVALUE
    NO MAXVALUE
    CACHE 1
);


--
-- Name: artist; Type: TABLE; Schema: example; Owner: postgres
--

CREATE TABLE example.artist (
    id integer NOT NULL,
    first_name character varying(126) NOT NULL,
    last_name character varying(126) NOT NULL,
    prefix character varying(126),
    suffix character varying(126)
);


ALTER TABLE example.artist OWNER TO postgres;

--
-- Name: artist_id_seq; Type: SEQUENCE; Schema: example; Owner: postgres
--

ALTER TABLE example.artist ALTER COLUMN id ADD GENERATED ALWAYS AS IDENTITY (
    SEQUENCE NAME example.artist_id_seq
    START WITH 1
    INCREMENT BY 1
    NO MINVALUE
    NO MAXVALUE
    CACHE 1
);


--
-- Name: author; Type: TABLE; Schema: example; Owner: postgres
--

CREATE TABLE example.author (
    id integer NOT NULL,
    first_name character varying(126) NOT NULL,
    last_name character varying(126) NOT NULL,
    prefix character varying(126),
    suffix character varying(126)
);


ALTER TABLE example.author OWNER TO postgres;

--
-- Name: author_id_seq; Type: SEQUENCE; Schema: example; Owner: postgres
--

ALTER TABLE example.author ALTER COLUMN id ADD GENERATED ALWAYS AS IDENTITY (
    SEQUENCE NAME example.author_id_seq
    START WITH 1
    INCREMENT BY 1
    NO MINVALUE
    NO MAXVALUE
    CACHE 1
);


--
-- Name: band; Type: TABLE; Schema: example; Owner: postgres
--

CREATE TABLE example.band (
    id integer NOT NULL,
    year_formed smallint NOT NULL,
    name character varying(126) NOT NULL,
    CONSTRAINT band_year_formed_valid_chk CHECK (((year_formed > 999) AND (year_formed < 10000)))
);


ALTER TABLE example.band OWNER TO postgres;

--
-- Name: band_artist; Type: TABLE; Schema: example; Owner: postgres
--

CREATE TABLE example.band_artist (
    band_id integer NOT NULL,
    artist_id integer NOT NULL
);


ALTER TABLE example.band_artist OWNER TO postgres;

--
-- Name: band_genre; Type: TABLE; Schema: example; Owner: postgres
--

CREATE TABLE example.band_genre (
    band_id integer NOT NULL,
    genre_id smallint NOT NULL
);


ALTER TABLE example.band_genre OWNER TO postgres;

--
-- Name: band_id_seq; Type: SEQUENCE; Schema: example; Owner: postgres
--

ALTER TABLE example.band ALTER COLUMN id ADD GENERATED ALWAYS AS IDENTITY (
    SEQUENCE NAME example.band_id_seq
    START WITH 1
    INCREMENT BY 1
    NO MINVALUE
    NO MAXVALUE
    CACHE 1
);


--
-- Name: book; Type: TABLE; Schema: example; Owner: postgres
--

CREATE TABLE example.book (
    id integer NOT NULL,
    format_id smallint NOT NULL,
    edition_id smallint,
    title character varying(126) NOT NULL,
    entity_id bigint NOT NULL
);


ALTER TABLE example.book OWNER TO postgres;

--
-- Name: book_author; Type: TABLE; Schema: example; Owner: postgres
--

CREATE TABLE example.book_author (
    book_id integer NOT NULL,
    author_id integer NOT NULL
);


ALTER TABLE example.book_author OWNER TO postgres;

--
-- Name: book_genre; Type: TABLE; Schema: example; Owner: postgres
--

CREATE TABLE example.book_genre (
    book_id integer NOT NULL,
    genre_id smallint NOT NULL
);


ALTER TABLE example.book_genre OWNER TO postgres;

--
-- Name: book_id_seq; Type: SEQUENCE; Schema: example; Owner: postgres
--

ALTER TABLE example.book ALTER COLUMN id ADD GENERATED ALWAYS AS IDENTITY (
    SEQUENCE NAME example.book_id_seq
    START WITH 1
    INCREMENT BY 1
    NO MINVALUE
    NO MAXVALUE
    CACHE 1
);


--
-- Name: category; Type: TABLE; Schema: example; Owner: postgres
--

CREATE TABLE example.category (
    id integer NOT NULL,
    name character varying(126) NOT NULL
);


ALTER TABLE example.category OWNER TO postgres;

--
-- Name: category_id_seq; Type: SEQUENCE; Schema: example; Owner: postgres
--

ALTER TABLE example.category ALTER COLUMN id ADD GENERATED ALWAYS AS IDENTITY (
    SEQUENCE NAME example.category_id_seq
    START WITH 1
    INCREMENT BY 1
    NO MINVALUE
    NO MAXVALUE
    CACHE 1
);


--
-- Name: edition; Type: TABLE; Schema: example; Owner: postgres
--

CREATE TABLE example.edition (
    id smallint NOT NULL,
    name character varying(126)
);


ALTER TABLE example.edition OWNER TO postgres;

--
-- Name: edition_id_seq; Type: SEQUENCE; Schema: example; Owner: postgres
--

ALTER TABLE example.edition ALTER COLUMN id ADD GENERATED ALWAYS AS IDENTITY (
    SEQUENCE NAME example.edition_id_seq
    START WITH 1
    INCREMENT BY 1
    NO MINVALUE
    NO MAXVALUE
    CACHE 1
);


--
-- Name: entity; Type: TABLE; Schema: example; Owner: postgres
--

CREATE TABLE example.entity (
    id bigint NOT NULL
);


ALTER TABLE example.entity OWNER TO postgres;

--
-- Name: entity_id_seq; Type: SEQUENCE; Schema: example; Owner: postgres
--

ALTER TABLE example.entity ALTER COLUMN id ADD GENERATED ALWAYS AS IDENTITY (
    SEQUENCE NAME example.entity_id_seq
    START WITH 1
    INCREMENT BY 1
    NO MINVALUE
    NO MAXVALUE
    CACHE 1
);


--
-- Name: format; Type: TABLE; Schema: example; Owner: postgres
--

CREATE TABLE example.format (
    id smallint NOT NULL,
    name character varying(126)
);


ALTER TABLE example.format OWNER TO postgres;

--
-- Name: format_id_seq; Type: SEQUENCE; Schema: example; Owner: postgres
--

ALTER TABLE example.format ALTER COLUMN id ADD GENERATED ALWAYS AS IDENTITY (
    SEQUENCE NAME example.format_id_seq
    START WITH 1
    INCREMENT BY 1
    NO MINVALUE
    NO MAXVALUE
    CACHE 1
);


--
-- Name: genre; Type: TABLE; Schema: example; Owner: postgres
--

CREATE TABLE example.genre (
    id smallint NOT NULL,
    name character varying(126) NOT NULL
);


ALTER TABLE example.genre OWNER TO postgres;

--
-- Name: genre_id_seq; Type: SEQUENCE; Schema: example; Owner: postgres
--

ALTER TABLE example.genre ALTER COLUMN id ADD GENERATED ALWAYS AS IDENTITY (
    SEQUENCE NAME example.genre_id_seq
    START WITH 1
    INCREMENT BY 1
    NO MINVALUE
    NO MAXVALUE
    CACHE 1
);


--
-- Name: music; Type: TABLE; Schema: example; Owner: postgres
--

CREATE TABLE example.music (
    id integer NOT NULL,
    format_id smallint NOT NULL,
    edition_id smallint,
    title character varying(126) NOT NULL
);


ALTER TABLE example.music OWNER TO postgres;

--
-- Name: music_artist; Type: TABLE; Schema: example; Owner: postgres
--

CREATE TABLE example.music_artist (
    music_id integer NOT NULL,
    artist_id integer NOT NULL
);


ALTER TABLE example.music_artist OWNER TO postgres;

--
-- Name: music_id_seq; Type: SEQUENCE; Schema: example; Owner: postgres
--

ALTER TABLE example.music ALTER COLUMN id ADD GENERATED ALWAYS AS IDENTITY (
    SEQUENCE NAME example.music_id_seq
    START WITH 1
    INCREMENT BY 1
    NO MINVALUE
    NO MAXVALUE
    CACHE 1
);


--
-- Name: product; Type: TABLE; Schema: example; Owner: postgres
--

CREATE TABLE example.product (
    id bigint NOT NULL,
    entity_id bigint NOT NULL,
    category_id integer NOT NULL,
    format_id smallint NOT NULL,
    price numeric(12,2) NOT NULL
);


ALTER TABLE example.product OWNER TO postgres;

--
-- Name: product_id_seq; Type: SEQUENCE; Schema: example; Owner: postgres
--

ALTER TABLE example.product ALTER COLUMN id ADD GENERATED ALWAYS AS IDENTITY (
    SEQUENCE NAME example.product_id_seq
    START WITH 1
    INCREMENT BY 1
    NO MINVALUE
    NO MAXVALUE
    CACHE 1
);


--
-- Name: album_artist album_artist_pkey; Type: CONSTRAINT; Schema: example; Owner: postgres
--

ALTER TABLE ONLY example.album_artist
    ADD CONSTRAINT album_artist_pkey PRIMARY KEY (album_id, artist_id);


--
-- Name: album album_entity_id_key; Type: CONSTRAINT; Schema: example; Owner: postgres
--

ALTER TABLE ONLY example.album
    ADD CONSTRAINT album_entity_id_key UNIQUE (entity_id);


--
-- Name: album album_pkey; Type: CONSTRAINT; Schema: example; Owner: postgres
--

ALTER TABLE ONLY example.album
    ADD CONSTRAINT album_pkey PRIMARY KEY (id);


--
-- Name: artist artist_pkey; Type: CONSTRAINT; Schema: example; Owner: postgres
--

ALTER TABLE ONLY example.artist
    ADD CONSTRAINT artist_pkey PRIMARY KEY (id);


--
-- Name: author author_pkey; Type: CONSTRAINT; Schema: example; Owner: postgres
--

ALTER TABLE ONLY example.author
    ADD CONSTRAINT author_pkey PRIMARY KEY (id);


--
-- Name: band_artist band_artist_pkey; Type: CONSTRAINT; Schema: example; Owner: postgres
--

ALTER TABLE ONLY example.band_artist
    ADD CONSTRAINT band_artist_pkey PRIMARY KEY (band_id, artist_id);


--
-- Name: band_genre band_genre_pkey; Type: CONSTRAINT; Schema: example; Owner: postgres
--

ALTER TABLE ONLY example.band_genre
    ADD CONSTRAINT band_genre_pkey PRIMARY KEY (band_id, genre_id);


--
-- Name: band band_pkey; Type: CONSTRAINT; Schema: example; Owner: postgres
--

ALTER TABLE ONLY example.band
    ADD CONSTRAINT band_pkey PRIMARY KEY (id);


--
-- Name: book_author book_author_pkey; Type: CONSTRAINT; Schema: example; Owner: postgres
--

ALTER TABLE ONLY example.book_author
    ADD CONSTRAINT book_author_pkey PRIMARY KEY (book_id, author_id);


--
-- Name: book book_entity_id_key; Type: CONSTRAINT; Schema: example; Owner: postgres
--

ALTER TABLE ONLY example.book
    ADD CONSTRAINT book_entity_id_key UNIQUE (entity_id);


--
-- Name: book_genre book_genre_pkey; Type: CONSTRAINT; Schema: example; Owner: postgres
--

ALTER TABLE ONLY example.book_genre
    ADD CONSTRAINT book_genre_pkey PRIMARY KEY (book_id, genre_id);


--
-- Name: book book_pkey; Type: CONSTRAINT; Schema: example; Owner: postgres
--

ALTER TABLE ONLY example.book
    ADD CONSTRAINT book_pkey PRIMARY KEY (id);


--
-- Name: category category_name_key; Type: CONSTRAINT; Schema: example; Owner: postgres
--

ALTER TABLE ONLY example.category
    ADD CONSTRAINT category_name_key UNIQUE (name);


--
-- Name: category category_pkey; Type: CONSTRAINT; Schema: example; Owner: postgres
--

ALTER TABLE ONLY example.category
    ADD CONSTRAINT category_pkey PRIMARY KEY (id);


--
-- Name: edition edition_name_key; Type: CONSTRAINT; Schema: example; Owner: postgres
--

ALTER TABLE ONLY example.edition
    ADD CONSTRAINT edition_name_key UNIQUE (name);


--
-- Name: edition edition_pkey; Type: CONSTRAINT; Schema: example; Owner: postgres
--

ALTER TABLE ONLY example.edition
    ADD CONSTRAINT edition_pkey PRIMARY KEY (id);


--
-- Name: entity entity_pkey; Type: CONSTRAINT; Schema: example; Owner: postgres
--

ALTER TABLE ONLY example.entity
    ADD CONSTRAINT entity_pkey PRIMARY KEY (id);


--
-- Name: format format_name_key; Type: CONSTRAINT; Schema: example; Owner: postgres
--

ALTER TABLE ONLY example.format
    ADD CONSTRAINT format_name_key UNIQUE (name);


--
-- Name: format format_pkey; Type: CONSTRAINT; Schema: example; Owner: postgres
--

ALTER TABLE ONLY example.format
    ADD CONSTRAINT format_pkey PRIMARY KEY (id);


--
-- Name: genre genre_name_key; Type: CONSTRAINT; Schema: example; Owner: postgres
--

ALTER TABLE ONLY example.genre
    ADD CONSTRAINT genre_name_key UNIQUE (name);


--
-- Name: genre genre_pkey; Type: CONSTRAINT; Schema: example; Owner: postgres
--

ALTER TABLE ONLY example.genre
    ADD CONSTRAINT genre_pkey PRIMARY KEY (id);


--
-- Name: music_artist music_artist_pkey; Type: CONSTRAINT; Schema: example; Owner: postgres
--

ALTER TABLE ONLY example.music_artist
    ADD CONSTRAINT music_artist_pkey PRIMARY KEY (music_id, artist_id);


--
-- Name: music music_pkey; Type: CONSTRAINT; Schema: example; Owner: postgres
--

ALTER TABLE ONLY example.music
    ADD CONSTRAINT music_pkey PRIMARY KEY (id);


--
-- Name: product product_entity_id_key; Type: CONSTRAINT; Schema: example; Owner: postgres
--

ALTER TABLE ONLY example.product
    ADD CONSTRAINT product_entity_id_key UNIQUE (entity_id);


--
-- Name: product product_pkey; Type: CONSTRAINT; Schema: example; Owner: postgres
--

ALTER TABLE ONLY example.product
    ADD CONSTRAINT product_pkey PRIMARY KEY (id);


--
-- Name: artist_fname_lname_idx; Type: INDEX; Schema: example; Owner: postgres
--

CREATE INDEX artist_fname_lname_idx ON example.artist USING btree (first_name varchar_pattern_ops, last_name varchar_pattern_ops) INCLUDE (prefix, suffix);


--
-- Name: author_fname_lname_idx; Type: INDEX; Schema: example; Owner: postgres
--

CREATE INDEX author_fname_lname_idx ON example.author USING btree (first_name varchar_pattern_ops, last_name varchar_pattern_ops) INCLUDE (prefix, suffix);


--
-- Name: book_title_trgm_idx; Type: INDEX; Schema: example; Owner: postgres
--

CREATE INDEX book_title_trgm_idx ON example.book USING gin (title public.gin_trgm_ops);


--
-- Name: album trg_album_before_ins; Type: TRIGGER; Schema: example; Owner: postgres
--

CREATE TRIGGER trg_album_before_ins BEFORE INSERT ON example.album FOR EACH ROW EXECUTE FUNCTION example.entity_insert_trigger();


--
-- Name: book trg_book_before_ins; Type: TRIGGER; Schema: example; Owner: postgres
--

CREATE TRIGGER trg_book_before_ins BEFORE INSERT ON example.book FOR EACH ROW EXECUTE FUNCTION example.entity_insert_trigger();


--
-- Name: album_artist album_artist_album_id_fkey; Type: FK CONSTRAINT; Schema: example; Owner: postgres
--

ALTER TABLE ONLY example.album_artist
    ADD CONSTRAINT album_artist_album_id_fkey FOREIGN KEY (album_id) REFERENCES example.album(id);


--
-- Name: album_artist album_artist_artist_id_fkey; Type: FK CONSTRAINT; Schema: example; Owner: postgres
--

ALTER TABLE ONLY example.album_artist
    ADD CONSTRAINT album_artist_artist_id_fkey FOREIGN KEY (artist_id) REFERENCES example.artist(id);


--
-- Name: album album_artist_id_fkey; Type: FK CONSTRAINT; Schema: example; Owner: postgres
--

ALTER TABLE ONLY example.album
    ADD CONSTRAINT album_artist_id_fkey FOREIGN KEY (artist_id) REFERENCES example.artist(id);


--
-- Name: album album_band_id_fkey; Type: FK CONSTRAINT; Schema: example; Owner: postgres
--

ALTER TABLE ONLY example.album
    ADD CONSTRAINT album_band_id_fkey FOREIGN KEY (band_id) REFERENCES example.band(id);


--
-- Name: album album_entity_id_fkey; Type: FK CONSTRAINT; Schema: example; Owner: postgres
--

ALTER TABLE ONLY example.album
    ADD CONSTRAINT album_entity_id_fkey FOREIGN KEY (entity_id) REFERENCES example.entity(id);


--
-- Name: album album_format_id_fkey; Type: FK CONSTRAINT; Schema: example; Owner: postgres
--

ALTER TABLE ONLY example.album
    ADD CONSTRAINT album_format_id_fkey FOREIGN KEY (format_id) REFERENCES example.format(id);


--
-- Name: band_artist band_artist_artist_id_fkey; Type: FK CONSTRAINT; Schema: example; Owner: postgres
--

ALTER TABLE ONLY example.band_artist
    ADD CONSTRAINT band_artist_artist_id_fkey FOREIGN KEY (artist_id) REFERENCES example.artist(id);


--
-- Name: band_artist band_artist_band_id_fkey; Type: FK CONSTRAINT; Schema: example; Owner: postgres
--

ALTER TABLE ONLY example.band_artist
    ADD CONSTRAINT band_artist_band_id_fkey FOREIGN KEY (band_id) REFERENCES example.band(id);


--
-- Name: band_genre band_genre_band_id_fkey; Type: FK CONSTRAINT; Schema: example; Owner: postgres
--

ALTER TABLE ONLY example.band_genre
    ADD CONSTRAINT band_genre_band_id_fkey FOREIGN KEY (band_id) REFERENCES example.band(id);


--
-- Name: band_genre band_genre_genre_id_fkey; Type: FK CONSTRAINT; Schema: example; Owner: postgres
--

ALTER TABLE ONLY example.band_genre
    ADD CONSTRAINT band_genre_genre_id_fkey FOREIGN KEY (genre_id) REFERENCES example.genre(id);


--
-- Name: book_author book_author_author_id_fkey; Type: FK CONSTRAINT; Schema: example; Owner: postgres
--

ALTER TABLE ONLY example.book_author
    ADD CONSTRAINT book_author_author_id_fkey FOREIGN KEY (author_id) REFERENCES example.author(id);


--
-- Name: book_author book_author_book_id_fkey; Type: FK CONSTRAINT; Schema: example; Owner: postgres
--

ALTER TABLE ONLY example.book_author
    ADD CONSTRAINT book_author_book_id_fkey FOREIGN KEY (book_id) REFERENCES example.book(id);


--
-- Name: book book_edition_id_fkey; Type: FK CONSTRAINT; Schema: example; Owner: postgres
--

ALTER TABLE ONLY example.book
    ADD CONSTRAINT book_edition_id_fkey FOREIGN KEY (edition_id) REFERENCES example.edition(id);


--
-- Name: book book_entity_id_fkey; Type: FK CONSTRAINT; Schema: example; Owner: postgres
--

ALTER TABLE ONLY example.book
    ADD CONSTRAINT book_entity_id_fkey FOREIGN KEY (entity_id) REFERENCES example.entity(id);


--
-- Name: book book_format_id_fkey; Type: FK CONSTRAINT; Schema: example; Owner: postgres
--

ALTER TABLE ONLY example.book
    ADD CONSTRAINT book_format_id_fkey FOREIGN KEY (format_id) REFERENCES example.format(id);


--
-- Name: book_genre book_genre_book_id_fkey; Type: FK CONSTRAINT; Schema: example; Owner: postgres
--

ALTER TABLE ONLY example.book_genre
    ADD CONSTRAINT book_genre_book_id_fkey FOREIGN KEY (book_id) REFERENCES example.book(id);


--
-- Name: book_genre book_genre_genre_id_fkey; Type: FK CONSTRAINT; Schema: example; Owner: postgres
--

ALTER TABLE ONLY example.book_genre
    ADD CONSTRAINT book_genre_genre_id_fkey FOREIGN KEY (genre_id) REFERENCES example.genre(id);


--
-- Name: music_artist music_artist_artist_id_fkey; Type: FK CONSTRAINT; Schema: example; Owner: postgres
--

ALTER TABLE ONLY example.music_artist
    ADD CONSTRAINT music_artist_artist_id_fkey FOREIGN KEY (artist_id) REFERENCES example.artist(id);


--
-- Name: music_artist music_artist_music_id_fkey; Type: FK CONSTRAINT; Schema: example; Owner: postgres
--

ALTER TABLE ONLY example.music_artist
    ADD CONSTRAINT music_artist_music_id_fkey FOREIGN KEY (music_id) REFERENCES example.music(id);


--
-- Name: music music_edition_id_fkey; Type: FK CONSTRAINT; Schema: example; Owner: postgres
--

ALTER TABLE ONLY example.music
    ADD CONSTRAINT music_edition_id_fkey FOREIGN KEY (edition_id) REFERENCES example.edition(id);


--
-- Name: music music_format_id_fkey; Type: FK CONSTRAINT; Schema: example; Owner: postgres
--

ALTER TABLE ONLY example.music
    ADD CONSTRAINT music_format_id_fkey FOREIGN KEY (format_id) REFERENCES example.format(id);


--
-- Name: product product_category_id_fkey; Type: FK CONSTRAINT; Schema: example; Owner: postgres
--

ALTER TABLE ONLY example.product
    ADD CONSTRAINT product_category_id_fkey FOREIGN KEY (category_id) REFERENCES example.category(id);


--
-- Name: product product_entity_id_fkey; Type: FK CONSTRAINT; Schema: example; Owner: postgres
--

ALTER TABLE ONLY example.product
    ADD CONSTRAINT product_entity_id_fkey FOREIGN KEY (entity_id) REFERENCES example.entity(id);


--
-- Name: product product_format_id_fkey; Type: FK CONSTRAINT; Schema: example; Owner: postgres
--

ALTER TABLE ONLY example.product
    ADD CONSTRAINT product_format_id_fkey FOREIGN KEY (format_id) REFERENCES example.format(id);


--
-- PostgreSQL database dump complete
--

tree_select.sql

SET 
  search_path TO example;
SELECT 
  p.id AS product_id, 
  p.price, 
  c.name AS category_name, 
  f.name AS format_name, 
  alb.name AS album_name, 
  alb.year_released, 
  COALESCE(b.name, 'Solo Artist') AS band_or_artist_name, 
  array_agg(
    DISTINCT a.first_name || ' ' || a.last_name
  ) FILTER (
    WHERE 
      a.id IS NOT NULL
  ) AS artist_contributions 
FROM 
  product p 
  JOIN entity e ON p.entity_id = e.id 
  JOIN album alb ON e.id = alb.entity_id 
  LEFT JOIN band b ON alb.band_id = b.id 
  LEFT JOIN album_artist aa ON alb.id = aa.album_id 
  LEFT JOIN artist a ON aa.artist_id = a.id 
  JOIN category c ON p.category_id = c.id 
  JOIN format f ON p.format_id = f.id 
GROUP BY 
  p.id, 
  c.name, 
  f.name, 
  alb.name, 
  alb.year_released, 
  b.name 
ORDER BY 
  p.id;