Postgres 13

By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.
CREATE TABLE employee (
  emp_id int GENERATED BY DEFAULT AS IDENTITY PRIMARY KEY
, emp_dept int NOT NULL
, emp_manager int
, emp_name text NOT NULL
, CONSTRAINT fk_emp_manager FOREIGN KEY (emp_manager) REFERENCES employee(emp_id)
, UNIQUE (emp_dept, emp_id)  -- needed for FK fk_dept_manager
);

CREATE TABLE department (
  dept_id int GENERATED BY DEFAULT AS IDENTITY PRIMARY KEY
, dept_manager int
, dept_name text NOT NULL
, CONSTRAINT fk_dept_manager FOREIGN KEY (dept_id, dept_manager) REFERENCES employee(emp_dept, emp_id)
);

ALTER TABLE employee
  ADD CONSTRAINT fk_emp_dept
  FOREIGN KEY (emp_dept) REFERENCES department(dept_id);

CREATE TABLE



CREATE TABLE



ALTER TABLE


WITH ins_dept AS (
   INSERT INTO department
          (dept_manager                                         , dept_name)          
   VALUES (nextval(pg_get_serial_sequence('employee', 'emp_id')), 'Sales')
   RETURNING *
   )
INSERT INTO employee
      (emp_id      , emp_dept, emp_manager, emp_name)
SELECT dept_manager, dept_id , NULL, 'Bob'
FROM   ins_dept;

INSERT 0 1


SELECT dept_id, dept_name, dept_manager, e.emp_name as dept_manager_name
FROM   department d
JOIN   employee e ON e.emp_id = d.dept_manager;


dept_id
dept_name
dept_manager
dept_manager_name


1
Sales
1
Bob



SELECT 1