Empresas
Empregos
  • Sobre nós
  • Soluções
    • Publicação de vagas
      Publique sua vaga e receba candidatos qualificados em 48h.
    • Avaliações de candidatos
      Mais de 500 testes técnicos e psicológicos, mais anti-fraude.
    • Headhunting
      Busca executiva personalizada do início ao fim.
    • Folha de Pagamento + EOR
      Dispersão de folha e EOR em mais de 15 países da LATAM.
  • Preços
  • Empregos

0

683
Visualizações
Postgresql - create table with disjoint subclasses

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.

enter image description here

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?

over 4 years ago · Santiago Trujillo
1 Respostas
Responde à pergunta

0

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

over 4 years ago · Santiago Trujillo Relatório
Responde à pergunta
Encontrar trabalhos remotos

Descubra a nova forma de encontrar um emprego!

melhores empregos
Principais categorias de trabalho
Empresas
Postar vaga Preços Comercial
Jurídico
Termos e Condições Política de privacidade
© 2026 PeakU Inc. All Rights Reserved.
Andres GPT
Recomende algumas ofertas para mim
Preciso de ajuda