Postgres table inheritance: move from parent to child and vice versa

Question

I am wondering how I can easily move data between a parent table and its child table in PostgreSQL (9.4) and vice versa.

Assume that I have the following database sample set up:

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}');

How can I promote (move) one of the employees to the director table (must no longer appear in the parent)?

How can I demote (move) one of the directors back to the employees table (must no longer apear in the child)?

Answer 1

Promote an employee:

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;

However:

must no longer appear in the parent

Any row in the child table is by definition available in the parent table. You can only "hide" those rows if you use the predicate only when selecting from the employee table:

select *
from only employee;

The above will not show employees that are also director. A plain select * from employee however will show all names (but you can't distinguish them - that's the nature of inheritance).

---

Demote a director:

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

---

But to be honest, I'd probably model this through an additional column (like position or role) on the employee entity instead of using inheritance. Or even a many-to-many relationship to a position (or role) entity as it is not uncommon that employees have multiple roles, e.g. in different departments, teams or other contexts.