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

Question

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

Suppose I have the following sample database installed:

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’s table (should no longer appear in the parent)?

How can I lower (move) one of the directors back to the employee table (should no longer appear in the child)?

Answer 1

Assist 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:

should no longer appear in parent

. "" , only employee:

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), , , . , .