How Fivetran Syncs PostgreSQL Range Data Types

Question

How does Fivetran handle PostgreSQL range data types during sync?

Environment

Connector: PostgreSQL

Answer

Fivetran syncs PostgreSQL range type values as canonical JSON objects in the destination, preserving all structural details.

Range types in PostgreSQL represent intervals that may have defined or undefined start and end points. Each range is described by a start value, an end value, a start bound (inclusive, exclusive or open), and an end bound (inclusive, exclusive or open).

Fivetran supports syncing the following PostgreSQL range data types:

• int4range
• int8range
• numrange
• daterange
• tsrange
• tstzrange

JSON representation in destination

The following examples show how range type values are synced and represented as JSON in the destination.

int4range

Source value in PostgreSQL:

[1,10)

Synced JSON value in destination:

{
  "start_value": 1,
  "end_value": 10,
  "start_bound": "inclusive",
  "end_bound": "exclusive"
}

int8range

• Standard bounded range

  Source value in PostgreSQL:

  [10000000000,20000000000)
  

  Synced JSON value in destination:

  {
    "start_value": 10000000000,
    "end_value": 20000000000,
    "start_bound": "inclusive",
    "end_bound": "exclusive"
  }
  

• Open start bound, no lower value

  Source value in PostgreSQL:

  (,9)
  

  Synced JSON value in destination:

  {
    "end_value": 9,
    "start_bound": "open",
    "end_bound": "exclusive"
  }
  

• Unbounded end

  Source value in PostgreSQL:

  [1,)
  

  Synced JSON value in destination:

  {
    "start_value": 1,
    "start_bound": "inclusive",
    "end_bound": "open"
  }
  

• Empty ranges

  Source value in PostgreSQL:

  (,)
  

  Synced JSON value in destination:

  {
    "start_bound": "open",
    "end_bound": "open"
  }
  

numrange

Source value in PostgreSQL:

[1.0,10.0)

Synced JSON value in destination:

{
  "start_value": 1.0,
  "end_value": 10.0,
  "start_bound": "inclusive",
  "end_bound": "exclusive"
}

tsrange

Source value in PostgreSQL:

[2025-01-01,2025-12-31)

Synced JSON value in destination:

{
  "start_value": "2025-01-01T00:00",
  "end_value": "2025-12-31T00:00",
  "start_bound": "inclusive",
  "end_bound": "exclusive"
}

tstzrange

Source value in PostgreSQL:

[2025-01-01 00:00:00+0,2025-06-01 00:00:00+0)

Synced JSON value in destination:

{
  "start_value": "2025-01-01T00:00:00Z",
  "end_value": "2025-06-01T00:00:00Z",
  "start_bound": "inclusive",
  "end_bound": "exclusive"
}

daterange

Source value in PostgreSQL:

[2025-01-01,2025-06-01)

Synced JSON value in destination:

{
  "start_value": "2025-01-01",
  "end_value": "2025-06-01",
  "start_bound": "inclusive",
  "end_bound": "exclusive"
}

Empty range

All supported range types can have an empty range value. The following example uses a daterange:

Source value in PostgreSQL:

'empty'::daterange

Synced JSON value in destination:

{
  "empty": true
}