Postgrex range behavior is confusing

Questions / Help
1

I’m confused by the way Postgrex (0.17.1) interprets PostgreSQL ranges. For example, one test case is:

assert [[%Postgrex.Range{lower: 2, upper: 5, lower_inclusive: true, upper_inclusive: false}]] =
  query("SELECT '(1,5)'::int4range", [])

But that PostgreSQL syntax means “1 to 5, exclusive of both”.

In the text form of a range, an inclusive lower bound is represented by “[” while an exclusive lower bound is represented by “(”. Likewise, an inclusive upper bound is represented by “]”, while an exclusive upper bound is represented by “)”
PostgreSQL: Documentation: 16: 8.17. Range Types

So I would expect the test to say:

assert [[%Postgrex.Range{lower: 1, upper: 5, lower_inclusive: false, upper_inclusive: false}]] = 
  query("SELECT '(1,5)'::int4range", [])

In practice this doesn’t make much difference - either way, the first number included in the range is 2 - but the semantics are different.

Is Postgrex intentionally interpreting this differently than PostgreSQL? If so, maybe a @moduledoc on Postgrex.Range should explain why?

1 Like
2

Try running SELECT '(1,5)'::int4range in postgres. The result by the db is [2,5). That’s likely also what postgrex receives and therefore returns.

1 Like
3

Yep, you’re totally right. That’s weird, but OK.

4

Now it’s explained: Comment on how range retrieval works by nathanl · Pull Request #655 · elixir-ecto/postgrex · GitHub :slight_smile:

3 Likes