postgresql - Postgres 表继承 : move from parent to child and vice versa

Question

我想知道如何在 PostgreSQL (9.4) 中轻松地在父表及其子表之间移动数据，反之亦然。

假设我设置了以下数据库示例:

DROP TABLE IF EXISTS employee CASCADE;
DROP TABLE IF EXISTS director CASCADE;

CREATE TABLE employee(
id SERIAL PRIMARY KEY NOT NULL,
name VARCHAR(255) NOT NULL,
surname VARCHAR(255) NOT NULL,
employment_date DATE NOT NULL DEFAULT CURRENT_DATE
);

CREATE TABLE director(
director_id SERIAL PRIMARY KEY NOT NULL,
secretary_id INT4 REFERENCES employee(id),
extra_legal_benefits VARCHAR(255) ARRAY
) inherits (employee);

INSERT INTO employee(name, surname)
VALUES ('Alice', 'Alisson');

INSERT INTO employee(name, surname)
VALUES ('Bob', 'Bobson');

INSERT INTO employee(name, surname)
VALUES ('Carol', 'Clarckson');

INSERT INTO director(name, surname, secretary_id, extra_legal_benefits)
VALUES ('David', 'Davidson', 1, '{car, travel expenses}');

如何将其中一名员工提升(移动)到主管表(不得再出现在父表中)？

我怎样才能将其中一位董事降级(移动)回员工表(不能再出现在 child 中)？

Answer 1

提拔员工:

with deleted as (
  delete from only employee 
  where name = 'Carol'
  returning *
)
insert into director (name, surname, secretary_id, extra_legal_benefits)
select name, surname, null, '{flight}'
from deleted;

但是:

must no longer appear in the parent

根据定义，子表中的任何行在父表中都是可用的。如果从员工表中选择时使用谓词only，则只能“隐藏”这些行:

select *
from only employee;

以上不会显示兼任董事的员工。一个普通的 select * from employee 然而将显示所有名字(但你无法区分它们 - 这就是继承的本质)。

---

降级主管

with deleted as (
  delete from only director 
  where name = 'David'
  returning *
)
insert into employee (name, surname)
select name, surname
from deleted;

---

但老实说，我可能会通过员工实体上的附加列(如 position 或 role)而不是使用继承来对此进行建模。甚至是与 position(或 role)实体的多对多关系，因为员工具有多个角色的情况并不少见，例如在不同的部门、团队或其他环境中。