Postgresql - create table with disjoint subclasses

Question

I'm unsure how one can create a table on postgresql with disjoint subclasses. I have represented (a very simplified version of) my problem below in an ER diagram, showing the two subclasses and the attributes of each subclass.

For the columns common to all rows (id, common1, common2), it's clearly very simple (as shown in the code below).

create table Music (
    id          serial,
    common1 int not null, 
    common2 boolean not null, 

--<what to put here???>

);

However I'm not sure of the best way of considering the subclass issue. Does anyone know where to go from here?

What do layers, at1, lead, and strings and their values represent? — Schwern, Jul 03 '20 at 02:56
I think that [this thread](https://stackoverflow.com/questions/1567935/how-to-do-inheritance-modeling-in-relational-databases) may help you. — Carlos Bazilio, Jul 03 '20 at 02:57
@Schwern Columns of symphony are id, common1, common2, layers, and at1. Columns of concerto are id, common1, common2, lead and strings. I'm wondering if this can all be considered in a single table named Music. — Mathew Carroll, Jul 03 '20 at 03:03
@CarlosBazilio looked through this, but only contains references to books which I don't want to purchase — Mathew Carroll, Jul 03 '20 at 03:04

Schwern · Accepted Answer · 2020-07-03T03:20:24.493

Postgres table inheritance would work like so:

create table music (
    id serial primary key,
    common1 int not null, 
    common2 boolean not null
);

create table symphony (
    layers int not null,
    at1 text not null
) inherits(music);

create table concerto (
    lead text not null,
    strings integer not null
) inherits(music);

Consider if we have a row in each table.

insert into concerto (common1, common2, lead, strings)
  values (1, true, 'a', 5);
insert into symphony (common1, common2, layers, at1)
  values (2, false, 3, 'b');
insert into music (common1, common2)
  values (3, true);

They are all rows of music.

-- Fetches id, common1, and common2 from all rows.
select *
from music

If you only want to query rows in music specify only music.

-- Fetches id, common1, and common2 from only the one row in music.
select *
from only music

If you want to use symphony columns, you have to query symphony.

-- Fetches id, common1, common2, layers, at1 only from symphony
select *
from symphony

Try it

A more traditional structure would use join tables like so:

create table music (
    id serial primary key,
    common1 int not null, 
    common2 boolean not null
);

create table music_symphony (
    music_id integer references music(id),
    layers int not null,
    at1 text not null
);

create table music_concerto (
    music_id integer references music(id),
    lead text not null,
    strings integer not null
);

insert into music (id, common1, common2)
  values (1, 1, true);
insert into music_concerto(lead, strings)
  values ('a', 5);

insert into music (id, common1, common2)
  values (2, 2, false);
insert into music_symphony (music_id, layers, at1)
  values (2, 3, 'b');
  
insert into music (id, common1, common2)
  values (3, 3, true);

-- Fetch all symphonies
select *
from music m
join music_symphony ms on ms.music_id = m.id

Try it

Postgresql - create table with disjoint subclasses

1 Answers1