Tsrange - Subtracting the differences between two tables based on an id field

Question

I have two tables. free_time and appointment. I would like to subtract all of the appointment records from the records of free time. The result would be a set of records in which free time remains. The appointments are guaranteed to exist within the range of free_time. I would like to make this query WHERE doctor_id = 1.

They have the following records:

CREATE TABLE free_time
AS
  SELECT freetime::tsrange, doctor_id
  FROM ( VALUES
    ('[2017-04-19 09:00, 2017-04-19 12:30)', 1),
    ('[2017-04-19 13:30, 2017-04-19 15:30)', 1),
    ('[2017-04-20 08:30, 2017-04-20 16:30)', 1),
    ('[2017-04-19 09:00, 2017-04-19 16:30)', 2)
  ) AS t(freetime, doctor_id);


CREATE TABLE appointment
AS
  SELECT appointment::tsrange, doctor_id
  FROM ( VALUES
    ('[2017-04-19 10:30, 2017-04-19 11:30)', 1),
    ('[2017-04-19 13:30, 2017-04-19 14:30)', 1),
    ('[2017-04-20 10:30, 2017-04-20 13:30)', 1),
    ('[2017-04-20 14:30, 2017-04-20 16:30)', 1),
    ('[2017-04-19 10:30, 2017-04-19 11:30)', 2)
  ) AS t(appointment, doctor_id);

The result set should look something like:

["2017-04-19 09:00:00","2017-04-19 10:30:00"),
["2017-04-19 11:30:00","2017-04-19 12:30:00"),
["2017-04-19 14:30:00","2017-04-19 15:30:00"),
["2017-04-20 08:30:00","2017-04-20 10:30:00"),
["2017-04-20 13:30:00","2017-04-20 14:30:00"),

Answer 1

There are two functions (from my older answer with minor fix):

create or replace function range_exclude(anyelement, anyelement) returns anyarray as $$
declare
  r1 text;
  r2 text;
begin
  -- Check input parameters
  if not pg_typeof($1) in ('numrange'::regtype, 'int8range'::regtype, 'daterange'::regtype, 'tsrange'::regtype, 'tstzrange'::regtype) then
    raise exception 'Function accepts only range types but got % type.', pg_typeof($1);
  end if;

  -- If result is single element
  if ($1 &< $2 or $1 &> $2) then
    return array[$1 - $2];
  end if;

  -- Else build array of two intervals
  if lower_inc($1) then r1 := '['; else r1 := '('; end if;
  r1 := r1 || lower($1) || ',' || lower($2);
  if lower_inc($2) then r1 := r1 || ')'; else r1 := r1 || ']'; end if;

  if upper_inc($2) then r2 := '('; else r2 := '['; end if;
  r2 := r2 || upper($2) || ',' || upper($1);
  if upper_inc($1) then r2 := r2 || ']'; else r2 := r2 || ')'; end if;
  return array[r1, r2];
end $$ immutable language plpgsql;

create or replace function range_exclude(anyelement, anyarray) returns anyarray as $$
declare
  i int;
  j int;
begin
  -- Check input parameters
  if not pg_typeof($1) in ('numrange'::regtype, 'int8range'::regtype, 'daterange'::regtype, 'tsrange'::regtype, 'tstzrange'::regtype) then
    raise exception 'Function accepts only range types but got % type.', pg_typeof($1);
  end if;

  if array_length($2,1) is null then
    return array[$1];
  end if;

  $0 := range_exclude($1,$2[array_lower($2,1)]);
  for i in array_lower($2,1) + 1 .. array_upper($2,1) loop
    select array(select x from (select unnest(range_exclude(x,$2[i])) from unnest($0) as t(x)) as t(x) where not isempty(x)) into $0;
  end loop;
  return $0;
end $$ immutable language plpgsql;

After that your query could be:

with t as (
  select ft.doctor_id, freetime, range_exclude(freetime, array_agg(appointment)) as ex 
  from free_time ft join appointment ap on (ft.doctor_id = ap.doctor_id)
  group by ft.doctor_id, freetime)
select doctor_id, unnest(ex) from t order by 1,2;

Result:

╔═══════════╤═══════════════════════════════════════════════╗
║ doctor_id │                    unnest                     ║
╠═══════════╪═══════════════════════════════════════════════╣
║         1 │ ["2017-04-19 09:00:00","2017-04-19 10:30:00") ║
║         1 │ ["2017-04-19 11:30:00","2017-04-19 12:30:00") ║
║         1 │ ["2017-04-19 14:30:00","2017-04-19 15:30:00") ║
║         1 │ ["2017-04-20 08:30:00","2017-04-20 10:30:00") ║
║         1 │ ["2017-04-20 13:30:00","2017-04-20 14:30:00") ║
║         2 │ ["2017-04-19 09:00:00","2017-04-19 10:30:00") ║
║         2 │ ["2017-04-19 11:30:00","2017-04-19 16:30:00") ║
╚═══════════╧═══════════════════════════════════════════════╝