RANGE_MERGE

Return the smallest range that spans both input ranges.

Category: miscReturns: RANGEDialect: PostgreSql

Syntax

RANGE_MERGE(range1, range2)

Description

## Overview Returns the smallest range that contains both input ranges. If the inputs overlap or are adjacent, the result is their union. If they are disjoint, the result still spans the gap between them, so the output may contain values not present in either input. Use this function when you want a convex hull over two ranges; use a union operator instead when you want to preserve gaps. ## Behavior - Returns NULL if either argument is NULL. - Both arguments must have the same element type. - Returns a range that spans from the lesser lower bound to the greater upper bound, with inclusive or exclusive bounds matching the original endpoints. - Deterministic and side effect free. ## Compatibility - PG-compat alias for `range_merge` function.

Parameters

Name	Type	Description
`range1`		Specifies the first range. Must be of the same type as range2.
`range2`		Specifies the second range. Must be of the same type as range1.

Examples

-- Merge overlapping ranges
SELECT RANGE_MERGE('[1,10]', '[5,15]');  -- '[1,15]'

-- Merge adjacent ranges
SELECT RANGE_MERGE('[1,5]', '[6,10]');  -- '[1,10]'

-- Non-overlapping ranges still merge by spanning the gap
SELECT RANGE_MERGE('[1,3]', '[7,10]');  -- '[1,10]'

-- Combine two scheduling windows
SELECT RANGE_MERGE(shift_1, shift_2) AS full_coverage
FROM ops.catalog.schedules;

Pitfalls

RANGE_MERGE does not return a union. If the inputs are disjoint, the result includes the gap between them. Use a true union if you need to preserve disconnected pieces.
Bound inclusivity is preserved at each end from the corresponding endpoint. An asymmetric input (`[1,5)` merged with `[5,10]`) produces `[1,10]`, which may surprise code that expects half-open semantics.
Both ranges must share an element type. Cast explicitly when mixing INT and BIGINT ranges.
Empty ranges are left untouched in RANGE_MERGE: merging an empty range with a non-empty range returns the non-empty range.