Postgres 12

By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.

WITH RECURSIVE calendar AS (
    SELECT
		position_id,
		manager,
		d_range,
        d_range AS left,
        d_range AS center,
        d_range AS right
	FROM managers
	
    UNION

SELECT
		calendar.position_id,
		calendar.manager,
		calendar.d_range,

CASE travels.travel_dates && calendar.left
			WHEN TRUE THEN daterange(lower(calendar.left), lower(travels.travel_dates * calendar.left))
			ELSE daterange(lower(calendar.right), lower(travels.travel_dates * calendar.right))
		END AS left,

CASE travels.travel_dates && calendar.left
			WHEN TRUE THEN travels.travel_dates * calendar.left
			ELSE travels.travel_dates * calendar.right
		END AS center,

CASE travels.travel_dates && calendar.right
			WHEN TRUE THEN daterange(upper(travels.travel_dates * calendar.right), upper(calendar.right))
			ELSE daterange(upper(travels.travel_dates * calendar.left), upper(calendar.left))
		END AS right
    FROM calendar
    JOIN func_managers travels ON
		travels.position_id = calendar.position_id AND
        travels.travel_dates && calendar.d_range AND
		travels.travel_dates <> calendar.center AND (
			travels.travel_dates && calendar.left OR
			travels.travel_dates && calendar.right
		)
),
managers AS (
	SELECT
		position_id,
		manager,
		start_date,
		end_date,
		daterange(start_date, end_date, '[]') AS d_range
	FROM (
		VALUES 
			(1, 'team_lead', '2021-01-01'::date, '2021-12-31'::date),
			(2, 'ceo', '2021-01-01'::date, '9999-12-31'::date)
	) a (position_id, manager, start_date, end_date)
),
func_managers AS (
	SELECT 
		position_id,
		manager,
		start_date,
		end_date,
		daterange(start_date, end_date, '[]') AS travel_dates
	FROM (
		VALUES
			(1, 'ceo', '2021-01-01'::date, '2021-06-20'::date),
			(1, 'hr', '2021-06-25'::date, '2021-08-20'::date)
	) a (position_id, manager, start_date, end_date)
),
gap_managers AS (
	SELECT *
	FROM (
		SELECT
			a.position_id,
			(array_agg(DISTINCT a.manager))[1] AS manager,
			a.left AS available_dates
		FROM calendar a
		LEFT OUTER JOIN calendar b ON
			a.position_id = b.position_id AND
			a.left <> b.left AND
			a.left @> b.left
		GROUP BY a.position_id, a.left
		HAVING NOT bool_or(coalesce(a.left @> case when isempty(b.left) then null else b.left end, FALSE))

UNION

SELECT
			a.position_id,
			(array_agg(DISTINCT a.manager))[1] AS manager,
			a.right AS available_dates
		FROM calendar a
		LEFT OUTER JOIN calendar b ON
			a.position_id = b.position_id AND
			a.right <> b.right AND
			a.right @> b.right
		GROUP BY a.position_id, a.right
		HAVING NOT bool_or(coalesce(a.right @> case when isempty(b.right) then null else b.right end, false))

EXCEPT

SELECT 
			a.position_id,
			(array_agg(DISTINCT a.manager))[1] AS manager,
			a.center AS available_dates
		FROM calendar a
		LEFT OUTER JOIN calendar b ON
			a.position_id = b.position_id AND
			a.center <> b.center AND
			a.center @> b.center
		GROUP BY a.position_id, a.center
		HAVING NOT bool_or(COALESCE(a.center @> b.center, FALSE))
	) a
	WHERE NOT isempty(a.available_dates)
	ORDER BY a.position_id, a.available_dates
),
unified_managers AS (
	SELECT
		position_id,
		manager,
		LOWER(available_dates) AS start_date,
		UPPER(available_dates) - 1 AS end_date
	FROM gap_managers

UNION ALL

SELECT
		position_id,
		manager,
		start_date,
		end_date
	FROM managers WHERE NOT EXISTS (
		SELECT 1
		FROM gap_managers
		WHERE gap_managers.position_id = managers.position_id AND gap_managers.available_dates && managers.d_range
	)

UNION ALL

SELECT
		position_id,
		manager,
		start_date,
		end_date
	FROM func_managers
)
SELECT * FROM unified_managers
ORDER BY position_id, start_date

position_id	manager	start_date	end_date
1	ceo	2021-01-01	2021-06-20
1	team_lead	2021-06-21	2021-06-24
1	hr	2021-06-25	2021-08-20
1	team_lead	2021-08-21	2021-12-31
2	ceo	2021-01-01	9999-12-31

SELECT 5

WITH RECURSIVE calendar AS (
    SELECT
		position_id,
		manager,
		d_range,
        d_range AS left,
        d_range AS center,
        d_range AS right
	FROM managers
	
    UNION

SELECT
		calendar.position_id,
		calendar.manager,
		calendar.d_range,

CASE travels.travel_dates && calendar.left
			WHEN TRUE THEN travels.travel_dates * calendar.left
			ELSE travels.travel_dates * calendar.right
		END AS center,

CASE travels.travel_dates && calendar.right
			WHEN TRUE THEN daterange(upper(travels.travel_dates * calendar.right), upper(calendar.right))
			ELSE daterange(upper(travels.travel_dates * calendar.left), upper(calendar.left))
		END AS right
    FROM calendar
    JOIN func_managers travels ON
		travels.position_id = calendar.position_id AND
        travels.travel_dates && calendar.d_range AND
		travels.travel_dates <> calendar.center AND (
			travels.travel_dates && calendar.left OR
			travels.travel_dates && calendar.right
		)
),
managers AS (
	SELECT
		position_id,
		manager,
		start_date,
		end_date,
		daterange(start_date, end_date, '[]') AS d_range
	FROM (
		VALUES 
			(1, 'first', '2017-09-01'::date, '2018-10-14'::date),
			(1, 'second', '2018-10-15'::date, '2019-10-31'::date),
			(1, 'third', '2019-11-01'::date, '9999-12-31'::date)
	) a (position_id, manager, start_date, end_date)
),
func_managers AS (
	SELECT 
		position_id,
		manager,
		start_date,
		end_date,
		daterange(start_date, end_date, '[]') AS travel_dates
	FROM (
		VALUES
			(1, 'override', '2021-01-01'::date, '9999-12-31'::date)
	) a (position_id, manager, start_date, end_date)
),
gap_managers AS (
	SELECT *
	FROM (
		SELECT
			a.position_id,
			(array_agg(DISTINCT a.manager))[1] AS manager,
			a.left AS available_dates
		FROM calendar a
		LEFT OUTER JOIN calendar b ON
			a.position_id = b.position_id AND
			a.left <> b.left AND
			a.left @> b.left
		GROUP BY a.position_id, a.left
		HAVING NOT bool_or(coalesce(a.left @> case when isempty(b.left) then null else b.left end, FALSE))

UNION

EXCEPT

UNION ALL

SELECT
		position_id,
		manager,
		start_date,
		end_date
	FROM func_managers
)
SELECT * FROM unified_managers
ORDER BY position_id, start_date

position_id	manager	start_date	end_date
1	first	2017-09-01	2018-10-14
1	second	2018-10-15	2019-10-31
1	third	2019-11-01	2020-12-31
1	third	2019-11-01	9999-12-31
1	override	2021-01-01	9999-12-31

SELECT 5