Can my DB?

-- Built-in range types
SELECT
  '[2024-01-01, 2024-12-31]'::daterange AS year_2024,
  '[10, 20)'::int4range AS half_open_range;

-- Check overlap between two date ranges
SELECT *
FROM bookings
WHERE during && '[2024-06-01, 2024-06-30]'::daterange;

-- Containment: does range contain a value?
SELECT '[1, 10]'::int4range @> 5;   -- true

-- Exclusion constraint: no overlapping bookings for the same room
CREATE TABLE room_bookings (
  room_id   INT,
  during    tstzrange NOT NULL,
  EXCLUDE USING GIST (room_id WITH =, during WITH &&)
);

-- Custom range type
CREATE TYPE float8range AS RANGE (SUBTYPE = float8, SUBTYPE_DIFF = float8mi);

-- Multirange type (PostgreSQL 14+)
SELECT '{[1,3), [5,8)}'::int4multirange;

• Built-in range types: int4range, int8range, numrange, tsrange, tstzrange, daterange.
• PostgreSQL 14 added multirange types: int4multirange, int8multirange, nummultirange, datemultirange, tsmultirange, tstzmultirange.
• Operators: && (overlap), @> (contains element or range), <@ (is contained by), -|- (adjacent), + (union), * (intersection), - (difference).
• GiST and SP-GiST indexes support all range operators efficiently.
• Range bounds are inclusive [ ] or exclusive ( ) and can be empty or infinite.
• Used with EXCLUDE USING GIST to enforce non-overlapping constraints (e.g., hotel bookings, shift scheduling).

-- Workaround: separate start/end columns
CREATE TABLE bookings (
  id         INT AUTO_INCREMENT PRIMARY KEY,
  room_id    INT NOT NULL,
  start_date DATE NOT NULL,
  end_date   DATE NOT NULL,
  CHECK (end_date > start_date)
);

-- Overlap check in application or query
SELECT * FROM bookings
WHERE room_id = 1
  AND start_date < '2024-06-30'
  AND end_date > '2024-06-01';

• No range types.
• Use separate start and end columns with CHECK and application logic for overlap detection.
• Overlap queries require explicit range intersection predicates.

-- Workaround: separate start/end columns
CREATE TABLE bookings (
  id         INT AUTO_INCREMENT PRIMARY KEY,
  room_id    INT NOT NULL,
  start_date DATE NOT NULL,
  end_date   DATE NOT NULL
);

-- Overlap check
SELECT * FROM bookings
WHERE room_id = 1
  AND start_date < '2024-06-30'
  AND end_date > '2024-06-01';

• No range types.
• Use separate start and end columns as a workaround.

-- Workaround: separate start/end columns
CREATE TABLE bookings (
  id         NUMBER GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
  room_id    NUMBER NOT NULL,
  start_date DATE NOT NULL,
  end_date   DATE NOT NULL,
  CONSTRAINT chk_dates CHECK (end_date > start_date)
);

-- Overlap check
SELECT * FROM bookings
WHERE room_id = 1
  AND start_date < DATE '2024-06-30'
  AND end_date > DATE '2024-06-01';

• No range type.
• Use separate columns or Oracle's INTERVAL type for durations.
• Oracle Workspace Manager provides temporal versioning features for some use cases.

-- Workaround: separate start/end columns with CHECK constraint
CREATE TABLE bookings (
  id         INT IDENTITY(1,1) PRIMARY KEY,
  room_id    INT NOT NULL,
  start_date DATE NOT NULL,
  end_date   DATE NOT NULL,
  CONSTRAINT chk_dates CHECK (end_date > start_date)
);

-- Overlap check
SELECT * FROM bookings
WHERE room_id = 1
  AND start_date < '2024-06-30'
  AND end_date > '2024-06-01';

• No range type.
• Use separate start and end columns with CHECK constraints or filtered indexes.
• SQL Server Temporal Tables (2016+) provide system-versioning for slowly changing data but are not general-purpose range types.

-- Workaround: separate start/end columns
CREATE TABLE bookings (
  id         INTEGER PRIMARY KEY,
  room_id    INTEGER NOT NULL,
  start_date TEXT NOT NULL,
  end_date   TEXT NOT NULL,
  CHECK (end_date > start_date)
);

-- Overlap check
SELECT * FROM bookings
WHERE room_id = 1
  AND start_date < '2024-06-30'
  AND end_date > '2024-06-01';

• No range type.
• Use separate start and end columns with TEXT (ISO 8601 format) or REAL for dates.

-- Workaround: separate start/end columns
CREATE TABLE bookings (
  id         INTEGER IDENTITY(1,1) PRIMARY KEY,
  room_id    INTEGER NOT NULL,
  start_date DATE NOT NULL,
  end_date   DATE NOT NULL
);

-- Overlap check
SELECT * FROM bookings
WHERE room_id = 1
  AND start_date < '2024-06-30'
  AND end_date > '2024-06-01';

• No range type.
• Use separate start and end columns.

-- Workaround: separate start/end columns
CREATE TABLE bookings (
  id         INTEGER GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
  room_id    INTEGER NOT NULL,
  start_date DATE NOT NULL,
  end_date   DATE NOT NULL,
  CONSTRAINT chk_dates CHECK (end_date > start_date)
);

• No range type.
• Use separate start and end columns.
• DB2's temporal table support (period predicates, PERIOD FOR) provides some overlap-detection syntax for bi-temporal data.

-- Workaround: separate start/end columns
CREATE TABLE bookings (
  id         INTEGER AUTOINCREMENT PRIMARY KEY,
  room_id    INTEGER NOT NULL,
  start_date DATE NOT NULL,
  end_date   DATE NOT NULL
);

-- Overlap check
SELECT * FROM bookings
WHERE room_id = 1
  AND start_date < '2024-06-30'
  AND end_date > '2024-06-01';

• No range type.
• Use separate start and end columns.

-- Workaround: separate start/end columns
CREATE TABLE dataset.bookings (
  id         INT64,
  room_id    INT64 NOT NULL,
  start_date DATE NOT NULL,
  end_date   DATE NOT NULL
);

-- Overlap check
SELECT * FROM dataset.bookings
WHERE room_id = 1
  AND start_date < '2024-06-30'
  AND end_date > '2024-06-01';

• No range type.
• Use separate start and end DATE or TIMESTAMP columns.
• BigQuery supports RANGE as a parameterized type in some contexts (e.g., RANGE<DATE>) for time travel and table snapshots, but not as a general-purpose column data type for user tables.