Empresas
Empleos
  • Sobre nosotros
  • Soluciones
    • Publicación de vacantes
      Publica tu vacante y recibe candidatos calificados en 48h.
    • Evaluación de candidatos
      500+ pruebas técnicas y psicológicas, más anti-fraude.
    • Headhunting
      Búsqueda ejecutiva a la medida de principio a fin.
    • Nómina + EOR
      Dispersión de nómina y EOR en más de 15 países de LATAM.
  • Precios
  • Empleos

0

682
Vistas
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 Respuestas
Responde la pregunta

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 Denunciar
Responde la pregunta
Encuentra empleos remotos

¡Descubre la nueva forma de encontrar empleo!

Top de empleos
Top categorías de empleo
Empresas
Publicar vacante Precios Comercial
Legal
Términos y condiciones Política de privacidad
© 2026 PeakU Inc. All Rights Reserved.
Andres GPT
Recomiéndame algunas ofertas
Necesito ayuda