Repository: diesel-rs/diesel (language: rust)

Issue:
Add support for currently unsupported range operators and methods
Diesel currently supports the postgres range types. We do not provide built-in support for [various operators and methods](https://www.postgresql.org/docs/current/functions-range.html) available for these types. This is a tracking issue for adding support for these operators. 

The general strategy for adding support for new operators is as following:

1. Define the operator via [`infix_operator!()`](https://docs.diesel.rs/master/diesel/macro.infix_operator.html). The RangeExtends operator  would be defined as following: `infix_operator!(RangeExtends, " &> ", backend: Pg);` These operators can be defined [here](https://github.com/diesel-rs/diesel/blob/dd5f41dc93c574077646185393cd064c7103000b/diesel/src/pg/expression/operators.rs). If there is already an existing definition, this step could be skipped.
2. Define a new helper type [here](https://github.com/diesel-rs/diesel/blob/master/diesel/src/pg/expression/helper_types.rs#L30). Helper types are a tool for diesel users to simplify type definitions. Again if there is already a matching type definition this could be skipped. 
3. Add a new method to the [`PgRangeExpressionMethods` trait](https://github.com/diesel-rs/diesel/blob/99ef8bbdee614b8eb6560b00c4c1ead88fb0e737/diesel/src/pg/expression/expression_methods.rs#L706-L758). This definition should:
    * Use the previously defined helper type
    * Contain a documentation with an example
4. If possible add a test case for the auto_type attribute [here](https://github.com/diesel-rs/diesel/blob/99ef8bbdee614b8eb6560b00c4c1ead88fb0e737/diesel_derives/tests/auto_type.rs#L277-L288). (If not possible add a documentation entry there)
5. Check if compile test output is up to date by running `TRYBUILD=overwrite cargo test` inside of `diesel_compile_tests`. (This is required if you've added a new operator based on `infix_operator!`
5. Submit a PR with the change

The general strategy for adding support for new methods is as following:

1. Define the operator via [`define_sql_function!()`](https://docs.diesel.rs/master/diesel/expression/functions/macro.define_sql_function.html). The lower method would be defined as following: `define_sql_function!(fn lower<T: RangeHelper>(range: T) -> T::Inner);` These operators can be defined [here](https://github.com/diesel-rs/diesel/blob/99ef8bbdee614b8eb6560b00c4c1ead88fb0e737/diesel/src/pg/expression/functions.rs). If there is already an existing definition, this step could be skipped. This function should have a short documentation snippet with en example
6. Check if compile test output is up to date by running `TRYBUILD=overwrite cargo test` inside of `diesel_compile_tests`. (This is required if you've added a new operator based on `infix_operator!`
5. Submit a PR with the change



Operator list:

- [x] `anyrange @> anyrange -> bool` (Does the first range contain the second?)
- [x] `anyrange @> anyelement -> boolean` (Does the range contain the element?) * 
- [x] `anyrange <@ anyrange -> boolean` (Is the first range contained by the second?)  *
- [x] `anyelement <@ anyrange -> boolean` (Is the element contained in the range?) *
- [x] `anyrange && anyrange -> boolean` (Do the ranges overlap, that is, have any elements in common?) *
- [x] `anyrange << anyrange -> boolean` (Is the first range strictly left of the second?) *
- [x] `anyrange >> anyrange -> boolean` (Is the first range strictly right of the second?) *
- [x] `anyrange &< anyrange -> boolean` (Does the first range not extend to the right of the second?) *
- [x] `anyrange &> anyrange -> boolean` (Does the first range not extend to the left of the second?) *
- [x] `anyrange -|- anyrange -> boolean` (Are the ranges adjacent?) *
- [x] `anyrange + anyrange -> anyrange` (Computes the union of the ranges. The ranges must overlap or be adjacent, so that the union is a single range (but see range_merge()).) **
- [x] `anyrange * anyrange -> anyrange` (Computes the intersection of the ranges.) **
- [x] `anyrange - anyrange -> anyrange` (Computes the difference of the ranges. The second range must not be contained in the first in such a way that the difference would not be a single range.) **

Method list:

- [x] `lower ( anyrange ) -> anyelement` (Extracts the lower bound of the range (NULL if the range is empty or has no lower bound).) *
- [x] `upper ( anyrange ) -> anyelement` (Extracts the upper bound of the range (NULL if the range is empty or has no upper bound).) *
- [x] `isempty ( anyrange ) -> boolean` (Is the range empty?) *
- [x] `lower_inc ( anyrange ) -> boolean` (Is the range's lower bound inclusive?) *
- [x] `upper_inc ( anyrange ) -> boolean` (Is the range's upper bound inclusive?) *
- [x] `lower_inf ( anyrange ) -> boolean` (Does the range have no lower bound? (A lower bound of -Infinity returns false.) *
- [x] `upper_inf ( anyrange ) -> boolean` (Does the range have no upper bound? (An upper bound of Infinity returns false.)) *
- [x] `range_merge ( anyrange, anyrange ) -> anyrange` (Computes the smallest range that includes both of the given ranges.) *
- [x] `int4range(Nullable<Integer>, Nullable<Integer>, RangeBoundEnum) -> int4range` ***
- [x] `int8range(Nullable<BigInteger>, Nullable<BigInteger>, RangeBoundEnum) -> int8range` ***
- [x] `numrange(Nullable<Numeric>, Nullable<Numeric>, RangeBoundEnum) -> numrange` ***
- [x] `tsrange(Nullable<Timestamp>, Nullable<Timestamp>, RangeBoundEnum) -> tsrange` ***
- [x] `tstzrange(Nullable<Timestamptz>, Nullable<Timestamptz>, RangeBoundEnum) -> tstzrange` ***
- [x] `daterange(Nullable<Date>, Nullable<Date>, RangeBoundEnum) -> daterange` ***


Items marked with one `*` can follow the instructions directly. For items marked with ** it might be useful to look into how to use the [existing](https://github.com/diesel-rs/diesel/tree/99ef8bbdee614b8eb6560b00c4c1ead88fb0e737/diesel/src/expression/ops) rust side operator implementations as well. For items marked with *** we need to introduce an `enum RangeBoundEnum` type (or similar named) that specifies which bound kinds should be used. That likely requires a custom sql type + implementing the relevant type mapping.

Please add a comment to this issue if you plan to work on a specific operator/method.

If there is anything unclear about how to add a support for a specific operator/method just ask and we will try to answer your questions.



Discussion / hints:
I would like to help out with this issue. I haven't worked on diesel before so I'm currently working on building the repo locally
Hi! I'm also interested in helping with this issue. 
It's many small issues so we can split I think, or maybe work together to discover how to do it!
 
Hi! I would like to help out with this issue as well. 
I can start working on the `anyrange && anyrange -> boolean`. 
Hi! Started working on `anyelement <@ anyrange -> boolean`
> Hi! Started working on `anyelement <@ anyrange -> boolean`

Oops, It's already implemented.  I see `anyrange &< anyrange -> boolean` isn't yet so I'm working on that instead.
Hi, I can work on `anyrange << anyrange -> boolean` and `anyrange >> anyrange -> boolean` next.
I can work on `anyrange + anyrange -> anyrange`, `anyrange * anyrange -> anyrange` and`anyrange - anyrange -> anyrange` next.




Fix patch:
diff --git a/CHANGELOG.md b/CHANGELOG.md
index 6b3435b9b926..53e7eb9026e6 100644
--- a/CHANGELOG.md
+++ b/CHANGELOG.md
@@ -13,6 +13,7 @@ Increasing the minimal supported Rust version will always be coupled at least wi
 ### Added
 
 * Support for libsqlite3-sys 0.29.0
+* Add support for built-in PostgreSQL range operators and functions
 * Support for postgres multirange type
 
 ## [2.2.0] 2024-05-31
diff --git a/diesel/src/pg/expression/expression_methods.rs b/diesel/src/pg/expression/expression_methods.rs
index 76136d3563d2..5aaca4773a15 100644
--- a/diesel/src/pg/expression/expression_methods.rs
+++ b/diesel/src/pg/expression/expression_methods.rs
@@ -11,7 +11,7 @@ use crate::expression::grouped::Grouped;
 use crate::expression::operators::{Asc, Concat, Desc, Like, NotLike};
 use crate::expression::{AsExpression, Expression, IntoSql, TypedExpressionType};
 use crate::pg::expression::expression_methods::private::BinaryOrNullableBinary;
-use crate::sql_types::{Array, Inet, Integer, SqlType, Text, VarChar};
+use crate::sql_types::{Array, Inet, Integer, Range, SqlType, Text, VarChar};
 use crate::EscapeExpressionMethods;
 
 /// PostgreSQL specific methods which are present on all expressions.
@@ -72,6 +72,89 @@ pub trait PgExpressionMethods: Expression + Sized {
     {
         Grouped(IsDistinctFrom::new(self, other.as_expression()))
     }
+
+    /// Creates a PostgreSQL `<@` expression.
+    ///
+    /// This operator returns true whether a element is contained by a range
+    ///
+    /// This operator evaluates to true for the following cases:
+    ///
+    /// ```text
+    /// self:     |
+    /// other:  [-----]
+    /// ```
+    ///
+    /// ```text
+    /// self:  |
+    /// other: [----]
+    /// ```
+    ///
+    /// ```text
+    /// self:       |
+    /// other:  [----]
+    /// ```
+    ///
+    /// This operator evaluates to false for the following cases:
+    ///
+    /// ```text
+    /// self:           |
+    /// other:  [-----]
+    /// ```
+    ///
+    /// ```text
+    /// self:  |
+    /// other:   [----]
+    /// ```
+    ///
+    /// ```text
+    /// self:       |
+    /// other: [----)
+    /// ```
+    ///
+    /// # Example
+    ///
+    /// ```rust
+    /// # include!("../../doctest_setup.rs");
+    /// #
+    /// # table! {
+    /// #     posts {
+    /// #         id -> Integer,
+    /// #         versions -> Range<Integer>,
+    /// #     }
+    /// # }
+    /// #
+    /// # fn main() {
+    /// #     run_test().unwrap();
+    /// # }
+    /// #
+    /// # fn run_test() -> QueryResult<()> {
+    /// #     use std::collections::Bound;
+    /// #     use diesel::dsl::int4range;
+    /// #     use diesel::sql_types::Integer;
+    /// #
+    /// #     let conn = &mut establish_connection();
+    /// #
+    ///       let my_range = int4range(1, 5, diesel::sql_types::RangeBound::LowerBoundInclusiveUpperBoundExclusive);
+    ///
+    ///       let (first, second) = diesel::select((
+    ///           4.into_sql::<Integer>().is_contained_by_range(my_range),
+    ///           10.into_sql::<Integer>().is_contained_by_range(my_range)
+    ///       )).get_result::<(bool, bool)>(conn)?;
+    ///
+    ///       assert_eq!(first, true);
+    ///       assert_eq!(second, false);
+    /// #
+    /// #     Ok(())
+    /// # }
+    /// ```
+    #[allow(clippy::wrong_self_convention)] // This is named after the sql operator
+    fn is_contained_by_range<T>(self, other: T) -> dsl::IsContainedByRange<Self, T>
+    where
+        Self::SqlType: SqlType,
+        T: AsExpression<Range<Self::SqlType>>,
+    {
+        Grouped(IsContainedBy::new(self, other.as_expression()))
+    }
 }
 
 impl<T: Expression> PgExpressionMethods for T {}
@@ -706,7 +789,41 @@ impl<T, U> EscapeExpressionMethods for Grouped<NotSimilarTo<T, U>> {
 pub trait PgRangeExpressionMethods: Expression + Sized {
     /// Creates a PostgreSQL `@>` expression.
     ///
-    /// This operator returns whether a range contains an specific element
+    /// This operator returns true whether a range contains an specific element
+    ///
+    /// This operator evaluates to true for the following cases:
+    ///
+    /// ```text
+    /// self:   [-----]
+    /// other:    |
+    /// ```
+    ///
+    /// ```text
+    /// self:  [----]
+    /// other: |
+    /// ```
+    ///
+    /// ```text
+    /// self:  [----]
+    /// other:      |
+    /// ```
+    ///
+    /// This operator evaluates to false for the following cases:
+    ///
+    /// ```text
+    /// self:   [-----]
+    /// other:          |
+    /// ```
+    ///
+    /// ```text
+    /// self:    [----]
+    /// other: |
+    /// ```
+    ///
+    /// ```text
+    /// self:  [----)
+    /// other:      |
+    /// ```
     ///
     /// # Example
     ///
@@ -758,7 +875,36 @@ pub trait PgRangeExpressionMethods: Expression + Sized {
 
     /// Creates a PostgreSQL `@>` expression.
     ///
-    /// This operator returns whether a range contains an specific element
+    /// This operator returns true whether a range contains another range
+    ///
+    /// This operator evaluates to true for the following cases:
+    ///
+    /// ```text
+    /// self:   [-------]
+    /// other:     [--]
+    /// ```
+    ///
+    /// ```text
+    /// self:  [------]
+    /// other: [------]
+    /// ```
+    ///
+    /// This operator evaluates to false for the following cases:
+    ///
+    /// ```text
+    /// self:   [-----]
+    /// other:     [----]
+    /// ```
+    ///
+    /// ```text
+    /// self:    [----]
+    /// other: [--------]
+    /// ```
+    ///
+    /// ```text
+    /// self:  [----)
+    /// other: [----]
+    /// ```
     ///
     /// # Example
     ///
@@ -809,7 +955,36 @@ pub trait PgRangeExpressionMethods: Expression + Sized {
 
     /// Creates a PostgreSQL `<@` expression.
     ///
-    /// This operator returns whether a range is contained by a specific element
+    /// This operator returns true whether a range is contained by another range
+    ///
+    /// This operator evaluates to true for the following cases:
+    ///
+    /// ```text
+    /// self:   [-----]
+    /// other: [-------]
+    /// ```
+    ///
+    /// ```text
+    /// self:  [----]
+    /// other: [----]
+    /// ```
+    ///
+    /// This operator evaluates to false for the following cases:
+    ///
+    /// ```text
+    /// self:   [------]
+    /// other:   [---]
+    /// ```
+    ///
+    /// ```text
+    /// self:   [----]
+    /// other: [-----)
+    /// ```
+    ///
+    /// ```text
+    /// self:  [----]
+    /// other:   [----]
+    /// ```
     ///
     /// # Example
     ///
@@ -861,7 +1036,46 @@ pub trait PgRangeExpressionMethods: Expression + Sized {
 
     /// Creates a PostgreSQL `&&` expression.
     ///
-    /// This operator returns whether two ranges overlap.
+    /// This operator returns true whether two ranges overlap.
+    ///
+    /// This operator evaluates to true for the following cases:
+    ///
+    /// ```text
+    /// self:   [-----]
+    /// other:    [-----]
+    /// ```
+    ///
+    /// ```text
+    /// self:     [----]
+    /// other: [----]
+    /// ```
+    ///
+    /// ```text
+    /// self:     [----]
+    /// other:  [-------]
+    /// ```
+    ///
+    /// ```text
+    /// self:   [----]
+    /// other:  [----]
+    /// ```
+    ///
+    /// ```text
+    /// self:   [----]
+    /// other:  [----)
+    /// ```
+    ///
+    /// This operator evaluates to false for the following cases:
+    ///
+    /// ```text
+    /// self:   [-----]
+    /// other:          [-----]
+    /// ```
+    ///
+    /// ```text
+    /// self:       [----]
+    /// other: [--]
+    /// ```
     ///
     /// # Example
     ///
@@ -916,7 +1130,44 @@ pub trait PgRangeExpressionMethods: Expression + Sized {
 
     /// Creates a PostgreSQL `&<` expression.
     ///
-    /// This operator returns whether a range doesn't extend to the right of another
+    /// This operator returns true whether the argument range extend to the right of the current range
+    ///
+    /// Postgresql defines "extends" as does not have a lower bound smaller than the lower bound of the
+    /// self range. That means the right hand side range can overlap parts of the left hand side
+    /// range or be on the right side of the left hand side range
+    ///
+    /// The following constelations evaluate to true:
+    /// ```text
+    /// self:   [------)
+    /// other:    [----)
+    /// ```
+    ///
+    /// ```text
+    /// self:  [----)
+    /// other:         [----)
+    /// ```
+    ///
+    /// ```text
+    /// self:  [------)
+    /// other:    [------)
+    /// ```
+    ///
+    /// The following constelations evaluate to false:
+    ///
+    /// ```text
+    /// self:             [------]
+    /// other:    [----]
+    /// ```
+    ///
+    /// ```text
+    /// self:            [------]
+    /// other:         [----]
+    /// ```
+    ///
+    /// ```text
+    /// self:  [------]
+    /// other: [------)
+    /// ```
     ///
     /// # Example
     ///
@@ -945,30 +1196,150 @@ pub trait PgRangeExpressionMethods: Expression + Sized {
     ///     .values(versions.eq((Bound::Included(1), Bound::Excluded(20))))
     ///     .execute(conn)?;
     ///
-    /// let cool_posts = posts.select(id)
-    ///     .filter(versions.range_not_extends_right_to((Bound::Included(18), Bound::Excluded(20))))
-    ///     .load::<i32>(conn)?;
-    /// assert_eq!(vec![1], cool_posts);
+    /// let cool_posts = posts.select(versions.range_extends_right_to((Bound::Included(18), Bound::Excluded(20))))
+    ///     .get_result::<bool>(conn)?;
+    /// assert_eq!(true, cool_posts);
     ///
-    /// let amazing_posts = posts.select(id)
-    ///     .filter(versions.contains_range((Bound::Included(20), Bound::Excluded(25))))
-    ///     .load::<i32>(conn)?;
-    /// assert!(amazing_posts.is_empty());
+    /// let cool_posts = posts.select(versions.range_extends_right_to((Bound::Included(25), Bound::Excluded(30))))
+    ///     .get_result::<bool>(conn)?;
+    /// assert_eq!(true, cool_posts);
+    ///
+    /// let amazing_posts = posts.select(versions.range_extends_right_to((Bound::Included(-10), Bound::Excluded(0))))
+    ///     .get_result::<bool>(conn)?;
+    /// assert_eq!(false, amazing_posts);
+    /// #     Ok(())
+    /// # }
+    /// ```
+    fn range_extends_right_to<T>(self, other: T) -> dsl::RangeExtendsRightTo<Self, T>
+    where
+        Self::SqlType: SqlType,
+        T: AsExpression<Self::SqlType>,
+    {
+        Grouped(ExtendsRightTo::new(self, other.as_expression()))
+    }
+
+    /// Creates a PostgreSQL `&>` expression.
+    ///
+    /// This operator returns true whether a range does extend to the left of another
+    ///
+    /// Postgresql defines "extends" as does not have a upper bound greater than the upper bound of the
+    /// self range. That means the right hand side range can overlap parts of the left hand side
+    /// range or be on the left side of the left hand side range
+    ///
+    /// The following constelations evaluate to true:
+    /// ```text
+    /// self:        [------)
+    /// other:    [----)
+    /// ```
+    ///
+    /// ```text
+    /// self:          [----)
+    /// other: [----)
+    /// ```
+    ///
+    /// ```text
+    /// self:  [------)
+    /// other:        [------)
+    /// ```
+    ///
+    /// The following constelations evaluate to false:
+    ///
+    /// ```text
+    /// self:   [--------]
+    /// other:    [----]
+    /// ```
+    ///
+    /// ```text
+    /// self:  [------]
+    /// other:          [----]
+    /// ```
+    ///
+    /// ```text
+    /// self:  [------]
+    /// other: (------]
+    /// ```
+    ///
+    /// # Example
+    ///
+    /// ```rust
+    /// # include!("../../doctest_setup.rs");
+    /// #
+    /// # table! {
+    /// #     posts {
+    /// #         id -> Integer,
+    /// #         versions -> Range<Integer>,
+    /// #     }
+    /// # }
+    /// #
+    /// # fn main() {
+    /// #     run_test().unwrap();
+    /// # }
+    /// #
+    /// # fn run_test() -> QueryResult<()> {
+    /// #     use self::posts::dsl::*;
+    /// #     use std::collections::Bound;
+    /// #     let conn = &mut establish_connection();
+    /// #     diesel::sql_query("DROP TABLE IF EXISTS posts").execute(conn).unwrap();
+    /// #     diesel::sql_query("CREATE TABLE posts (id SERIAL PRIMARY KEY, versions INT4RANGE NOT NULL)").execute(conn).unwrap();
+    /// #
+    /// diesel::insert_into(posts)
+    ///     .values(versions.eq((Bound::Included(1), Bound::Excluded(20))))
+    ///     .execute(conn)?;
+    ///
+    /// let cool_posts = posts.select(versions.range_extends_left_to((Bound::Included(-10), Bound::Excluded(5))))
+    ///     .get_result::<bool>(conn)?;
+    /// assert_eq!(true, cool_posts);
+    ///
+    /// let cool_posts = posts.select(versions.range_extends_left_to((Bound::Included(-10), Bound::Excluded(-5))))
+    ///     .get_result::<bool>(conn)?;
+    /// assert_eq!(true, cool_posts);
+    ///
+    /// let amazing_posts = posts.select(versions.range_extends_left_to((Bound::Included(25), Bound::Excluded(30))))
+    ///     .get_result::<bool>(conn)?;
+    /// assert_eq!(false, amazing_posts);
     /// #     Ok(())
     /// # }
     /// ```
-    fn range_not_extends_right_to<T>(self, other: T) -> dsl::RangeNotExtendsRightTo<Self, T>
+    fn range_extends_left_to<T>(self, other: T) -> dsl::RangeExtendsLeftTo<Self, T>
     where
         Self::SqlType: SqlType,
         T: AsExpression<Self::SqlType>,
     {
-        Grouped(NotExtendsRightTo::new(self, other.as_expression()))
+        Grouped(ExtendsLeftTo::new(self, other.as_expression()))
     }
 
     /// Creates a PostgreSQL `<<` expression.
     ///
     /// Is the first range strictly left of the second?
     ///
+    /// The following constelations evaluate to true:
+    /// ```text
+    /// self:   [------)
+    /// other:            [----)
+    /// ```
+    ///
+    /// ```text
+    /// self:  [----)
+    /// other:      [----)
+    /// ```
+    ///
+    /// The following constelations evaluate to false:
+    ///
+    /// ```text
+    /// self:             [------]
+    /// other:    [----]
+    /// ```
+    ///
+    /// ```text
+    /// self:     [------]
+    /// other:         [----]
+    /// ```
+    ///
+    /// ```text
+    /// self:  [------]
+    /// other:        [------)
+    /// ```
+    ///
     /// # Example
     ///
     /// ```rust
@@ -1024,6 +1395,34 @@ pub trait PgRangeExpressionMethods: Expression + Sized {
     ///
     /// Is the first range strictly right of the second?
     ///
+    /// The following constelations evaluate to true:
+    /// ```text
+    /// self:          [------)
+    /// other: [----)
+    /// ```
+    ///
+    /// ```text
+    /// self:        [----)
+    /// other:  [----)
+    /// ```
+    ///
+    /// The following constelations evaluate to false:
+    ///
+    /// ```text
+    /// self:  [------]
+    /// other:          [----]
+    /// ```
+    ///
+    /// ```text
+    /// self:     [------]
+    /// other:         [----]
+    /// ```
+    ///
+    /// ```text
+    /// self:         [------]
+    /// other: [------]
+    /// ```
+    ///
     /// # Example
     ///
     /// ```rust
@@ -1075,10 +1474,120 @@ pub trait PgRangeExpressionMethods: Expression + Sized {
         Grouped(ContainsNet::new(self, other.as_expression()))
     }
 
+    /// Creates a PostgreSQL ` -|- ` expression.
+    ///
+    /// This operator evaluates to true if the two ranges are adjacent
+    ///
+    /// The following constelations evaluate to true:
+    /// ```text
+    /// self:   [------)
+    /// other:         [----)
+    /// ```
+    ///
+    /// ```text
+    /// self:       [----)
+    /// other: [----)
+    /// ```
+    ///
+    /// ```text
+    /// self:        [----)
+    /// other: [----]
+    /// ```
+    ///
+    /// ```text
+    /// self:  [----]
+    /// other:       [----]
+    /// ```
+    ///
+    /// The following constelations evaluate to false:
+    ///
+    /// ```text
+    /// self:        [------]
+    /// other:    [----]
+    /// ```
+    ///
+    /// ```text
+    /// self:     [------]
+    /// other:         [----]
+    /// ```
+    ///
+    /// ```text
+    /// self:  [------]
+    /// other:        [------]
+    /// ```
+    ///
+    /// ```text
+    /// self:  [------]
+    /// other:           [------]
+    /// ```
+    ///
+    /// # Example
+    ///
+    /// ```rust
+    /// # include!("../../doctest_setup.rs");
+    /// #
+    /// # table! {
+    /// #     posts {
+    /// #         id -> Integer,
+    /// #         versions -> Range<Integer>,
+    /// #     }
+    /// # }
+    /// #
+    /// # fn main() {
+    /// #     run_test().unwrap();
+    /// # }
+    /// #
+    /// # fn run_test() -> QueryResult<()> {
+    /// #     use self::posts::dsl::*;
+    /// #     use std::collections::Bound;
+    /// #     let conn = &mut establish_connection();
+    /// #     diesel::sql_query("DROP TABLE IF EXISTS posts").execute(conn).unwrap();
+    /// #     diesel::sql_query("CREATE TABLE posts (id SERIAL PRIMARY KEY, versions INT4RANGE NOT NULL)").execute(conn).unwrap();
+    /// #
+    /// diesel::insert_into(posts)
+    ///     .values(&vec![
+    ///         (versions.eq((Bound::Included(1), Bound::Excluded(2)))),
+    ///         (versions.eq((Bound::Included(4), Bound::Excluded(7))))
+    ///     ])
+    ///     .execute(conn)?;
+    ///
+    /// let data = posts.select(versions.range_adjacent((Bound::Included(2),Bound::Included(6))))
+    ///     .load::<bool>(conn)?;
+    /// let expected = vec![true, false];
+    /// assert_eq!(expected, data);
+    /// #     Ok(())
+    /// # }
+    /// ```
+    fn range_adjacent<T>(self, other: T) -> dsl::RangeAdjacent<Self, T>
+    where
+        Self::SqlType: SqlType,
+        T: AsExpression<Self::SqlType>,
+    {
+        Grouped(RangeAdjacent::new(self, other.as_expression()))
+    }
+
     /// Creates a PostgreSQL ` + ` expression.
     ///
     /// This operator unions two ranges and returns the union.
     ///
+    /// ```text
+    /// self:   [------)
+    /// other:      [----)
+    /// result: [--------)
+    /// ```
+    ///
+    /// ```text
+    /// self:          [----)
+    /// other: [----)
+    /// result: error
+    /// ```
+    ///
+    /// ```text
+    /// self:      [----)
+    /// other: [----]
+    /// result [--------)
+    /// ```
+    ///
     /// # Example
     ///
     /// ```rust
@@ -1130,6 +1639,24 @@ pub trait PgRangeExpressionMethods: Expression + Sized {
     /// The second range must not be contained in the first in such a way that the
     /// difference would not be a single range.
     ///
+    /// ```text
+    /// self:   [------)
+    /// other:      [----)
+    /// result: [---)
+    /// ```
+    ///
+    /// ```text
+    /// self:      [----)
+    /// other:  [----)
+    /// result:      [--)
+    /// ```
+    ///
+    /// ```text
+    /// self:      [--------)
+    /// other:       [----]
+    /// result: error
+    /// ```
+    ///
     /// # Example
     ///
     /// ```rust
@@ -1178,6 +1705,30 @@ pub trait PgRangeExpressionMethods: Expression + Sized {
     ///
     /// This operator takes two ranges and returns the intersection.
     ///
+    /// ```text
+    /// self:   [------)
+    /// other:      [----)
+    /// result:     [--)
+    /// ```
+    ///
+    /// ```text
+    /// self:      [----)
+    /// other:  [----)
+    /// result:    [-)
+    /// ```
+    ///
+    /// ```text
+    /// self:    [--------)
+    /// other:     [----]
+    /// result:    [----]
+    /// ```
+    ///
+    /// ```text
+    /// self:    [--------)
+    /// other:               [----]
+    /// result: empty range
+    /// ```
+    ///
     /// # Example
     ///
     /// ```rust
diff --git a/diesel/src/pg/expression/functions.rs b/diesel/src/pg/expression/functions.rs
index 313958927be8..0b7bfa9eb989 100644
--- a/diesel/src/pg/expression/functions.rs
+++ b/diesel/src/pg/expression/functions.rs
@@ -66,8 +66,10 @@ define_sql_function! {
 }
 
 define_sql_function! {
-    /// Returns the lower bound of the range.
-    /// if the range is empty or has no lower bound, it returns NULL.
+    /// Returns the lower bound of the range
+    ///
+    /// If the range is empty or has no lower bound, it returns NULL.
+    ///
     /// # Example
     ///
     /// ```rust
@@ -109,8 +111,10 @@ define_sql_function! {
 }
 
 define_sql_function! {
-    /// Returns the upper bound of the range.
-    /// if the range is empty or has no upper bound, it returns NULL.
+    /// Returns the upper bound of the range
+    ///
+    /// If the range is empty or has no upper bound, it returns NULL.
+    ///
     /// # Example
     ///
     /// ```rust
@@ -152,7 +156,8 @@ define_sql_function! {
 }
 
 define_sql_function! {
-    /// Returns true if the range is empty.
+    /// Returns true if the range is empty
+    ///
     /// # Example
     ///
     /// ```rust
@@ -194,7 +199,8 @@ define_sql_function! {
 }
 
 define_sql_function! {
-    /// Returns true if the range's lower bound is inclusive.
+    /// Returns true if the range's lower bound is inclusive
+    ///
     /// # Example
     ///
     /// ```rust
@@ -236,7 +242,8 @@ define_sql_function! {
 }
 
 define_sql_function! {
-    /// Returns true if the range's upper bound is inclusive.
+    /// Returns true if the range's upper bound is inclusive
+    ///
     /// # Example
     ///
     /// ```rust
@@ -277,7 +284,8 @@ define_sql_function! {
 }
 
 define_sql_function! {
-    /// Returns true if the range's lower bound is unbounded.
+    /// Returns true if the range's lower bound is unbounded
+    ///
     /// # Example
     ///
     /// ```rust
@@ -319,7 +327,8 @@ define_sql_function! {
 }
 
 define_sql_function! {
-    /// Returns true if the range's upper bound is unbounded.
+    /// Returns true if the range's upper bound is unbounded
+    ///
     /// # Example
     ///
     /// ```rust
@@ -361,7 +370,8 @@ define_sql_function! {
 }
 
 define_sql_function! {
-    /// Returns the smallest range which includes both of the given ranges.
+    /// Returns the smallest range which includes both of the given ranges
+    ///
     /// # Example
     ///
     /// ```rust
@@ -400,11 +410,12 @@ define_sql_function! {
     /// # }
     /// ```
     #[cfg(feature = "postgres_backend")]
-    fn range_merge<T: RangeHelper>(lhs: T, rhs: T) -> Range<<T as RangeHelper>::Inner>;
+    fn range_merge<T1: RangeHelper, T2: RangeHelper<Inner = T1::Inner>>(lhs: T1, rhs: T2) -> Range<T1::Inner>;
 }
 
 define_sql_function! {
-    /// Returns range of integer.
+    /// Returns range of integer
+    ///
     /// # Example
     ///
     /// ```rust
@@ -451,7 +462,8 @@ define_sql_function! {
 }
 
 define_sql_function! {
-    /// Returns range of integer.
+    /// Returns range of big ints
+    ///
     /// # Example
     ///
     /// ```rust
@@ -498,7 +510,8 @@ define_sql_function! {
 }
 
 define_sql_function! {
-    /// Returns range of number.
+    /// Returns range of numeric values
+    ///
     /// # Example
     ///
     /// ```rust
@@ -548,7 +561,8 @@ define_sql_function! {
 }
 
 define_sql_function! {
-    /// Returns range of timestamp without timezone.
+    /// Returns range of timestamps without timezone
+    ///
     /// # Example
     ///
     /// ```rust
@@ -598,7 +612,8 @@ define_sql_function! {
 }
 
 define_sql_function! {
-    /// Returns range of timestamp with timezone.
+    /// Returns range of timestamps with timezone
+    ///
     /// # Example
     ///
     /// ```rust
@@ -646,7 +661,8 @@ define_sql_function! {
 }
 
 define_sql_function! {
-    /// Returns range of date.
+    /// Returns range of dates
+    ///
     /// # Example
     ///
     /// ```rust
@@ -697,7 +713,8 @@ define_sql_function! {
 
 #[cfg(feature = "postgres_backend")]
 define_sql_function! {
-    /// Append an element to the end of an array.
+    /// Append an element to the end of an array
+    ///
     /// # Example
     ///
     /// ```rust
diff --git a/diesel/src/pg/expression/helper_types.rs b/diesel/src/pg/expression/helper_types.rs
index b38e4bc9a68a..5bcaadfc3e98 100644
--- a/diesel/src/pg/expression/helper_types.rs
+++ b/diesel/src/pg/expression/helper_types.rs
@@ -60,11 +60,17 @@ pub type RangeContains<Lhs, Rhs> = Grouped<
     >,
 >;
 
-/// The return type of [`lhs.range_not_extends_right_to(rhs)`](super::expression_methods::PgRangeExpressionMethods::range_not_extends_right_to)
+/// The return type of [`lhs.range_extends_right_to(rhs)`](super::expression_methods::PgRangeExpressionMethods::range_extends_right_to)
 /// for range expressions
 #[cfg(feature = "postgres_backend")]
-pub type RangeNotExtendsRightTo<Lhs, Rhs> =
-    Grouped<super::operators::NotExtendsRightTo<Lhs, AsExpr<Rhs, Lhs>>>;
+pub type RangeExtendsRightTo<Lhs, Rhs> =
+    Grouped<super::operators::ExtendsRightTo<Lhs, AsExpr<Rhs, Lhs>>>;
+
+/// The return type of [`lhs.range_extends_left_to(rhs)`](super::expression_methods::PgRangeExpressionMethods::range_extends_left_to)
+/// for range expressions
+#[cfg(feature = "postgres_backend")]
+pub type RangeExtendsLeftTo<Lhs, Rhs> =
+    Grouped<super::operators::ExtendsLeftTo<Lhs, AsExpr<Rhs, Lhs>>>;
 
 /// The return type of [`lhs.contains_range(rhs)`](super::expression_methods::PgRangeExpressionMethods::contains_range)
 /// for range expressions
@@ -76,6 +82,12 @@ pub type ContainsRange<Lhs, Rhs> = Contains<Lhs, Rhs>;
 #[cfg(feature = "postgres_backend")]
 pub type IsContainedBy<Lhs, Rhs> = Grouped<super::operators::IsContainedBy<Lhs, AsExpr<Rhs, Lhs>>>;
 
+/// The return type of [`lhs.is_contained_by_range(rhs)`](super::expression_methods::PgExpressionMethods::is_contained_by_range)
+#[cfg(feature = "postgres_backend")]
+pub type IsContainedByRange<Lhs, Rhs> = Grouped<
+    super::operators::IsContainedBy<Lhs, AsExprOf<Rhs, diesel::sql_types::Range<SqlTypeOf<Lhs>>>>,
+>;
+
 /// The return type of [`lhs.range_is_contained_by(rhs)`](super::expression_methods::PgRangeExpressionMethods::lesser_than)
 #[cfg(feature = "postgres_backend")]
 pub type LesserThanRange<Lhs, Rhs> =
@@ -102,6 +114,10 @@ pub type Difference<Lhs, Rhs> = Grouped<super::operators::DifferenceRange<Lhs, A
 #[doc(hidden)] // used by `#[auto_type]`
 pub type DifferenceRange<Lhs, Rhs> = Difference<Lhs, Rhs>;
 
+/// The return type of [`lhs.range_adjacent(rhs)`](super::expression_methods::PgRangeExpressionMethods::range_adjacent)
+#[cfg(feature = "postgres_backend")]
+pub type RangeAdjacent<Lhs, Rhs> = Grouped<super::operators::RangeAdjacent<Lhs, AsExpr<Rhs, Lhs>>>;
+
 /// The return type of [`lhs.intersection_range(rhs)`](super::expression_methods::PgRangeExpressionMethods::intersection_range)
 #[cfg(feature = "postgres_backend")]
 pub type Intersection<Lhs, Rhs> =
@@ -291,3 +307,48 @@ pub type NotLikeBinary<Lhs, Rhs> = crate::dsl::NotLike<Lhs, Rhs>;
 #[doc(hidden)]
 #[deprecated(note = "Use `dsl::Concat` instead")]
 pub type ConcatArray<Lhs, Rhs> = crate::dsl::Concat<Lhs, Rhs>;
+
+/// Return type of [`lower(range)`](super::functions::lower())
+#[allow(non_camel_case_types)]
+#[cfg(feature = "postgres_backend")]
+pub type lower<R> = super::functions::lower<SqlTypeOf<R>, R>;
+
+/// Return type of [`upper(range)`](super::functions::upper())
+#[allow(non_camel_case_types)]
+#[cfg(feature = "postgres_backend")]
+pub type upper<R> = super::functions::upper<SqlTypeOf<R>, R>;
+
+/// Return type of [`isempty(range)`](super::functions::isempty())
+#[allow(non_camel_case_types)]
+#[cfg(feature = "postgres_backend")]
+pub type isempty<R> = super::functions::isempty<SqlTypeOf<R>, R>;
+
+/// Return type of [`lower_inc(range)`](super::functions::lower_inc())
+#[allow(non_camel_case_types)]
+#[cfg(feature = "postgres_backend")]
+pub type lower_inc<R> = super::functions::lower_inc<SqlTypeOf<R>, R>;
+
+/// Return type of [`upper_inc(range)`](super::functions::upper_inc())
+#[allow(non_camel_case_types)]
+#[cfg(feature = "postgres_backend")]
+pub type upper_inc<R> = super::functions::upper_inc<SqlTypeOf<R>, R>;
+
+/// Return type of [`lower_inf(range)`](super::functions::lower_inf())
+#[allow(non_camel_case_types)]
+#[cfg(feature = "postgres_backend")]
+pub type lower_inf<R> = super::functions::lower_inf<SqlTypeOf<R>, R>;
+
+/// Return type of [`upper_inf(range)`](super::functions::upper_inf())
+#[allow(non_camel_case_types)]
+#[cfg(feature = "postgres_backend")]
+pub type upper_inf<R> = super::functions::upper_inf<SqlTypeOf<R>, R>;
+
+/// Return type of [`range_merge(range_a, range_b)`](super::functions::range_merge())
+#[allow(non_camel_case_types)]
+#[cfg(feature = "postgres_backend")]
+pub type range_merge<R1, R2> = super::functions::range_merge<SqlTypeOf<R1>, SqlTypeOf<R2>, R1, R2>;
+
+/// Return type of [`array_append(array, element)`](super::functions::array_append())
+#[allow(non_camel_case_types)]
+#[cfg(feature = "postgres_backend")]
+pub type array_append<A, E> = super::functions::array_append<SqlTypeOf<A>, SqlTypeOf<E>, A, E>;
diff --git a/diesel/src/pg/expression/operators.rs b/diesel/src/pg/expression/operators.rs
index 0fa057e6b329..dc6100694b98 100644
--- a/diesel/src/pg/expression/operators.rs
+++ b/diesel/src/pg/expression/operators.rs
@@ -15,7 +15,8 @@ infix_operator!(OverlapsWith, " && ", backend: Pg);
 infix_operator!(Contains, " @> ", backend: Pg);
 infix_operator!(IsContainedBy, " <@ ", backend: Pg);
 infix_operator!(ILike, " ILIKE ", backend: Pg);
-infix_operator!(NotExtendsRightTo, " &< ", backend: Pg);
+infix_operator!(ExtendsRightTo, " &< ", backend: Pg);
+infix_operator!(ExtendsLeftTo, " &> ", backend: Pg);
 infix_operator!(NotILike, " NOT ILIKE ", backend: Pg);
 infix_operator!(SimilarTo, " SIMILAR TO ", backend: Pg);
 infix_operator!(NotSimilarTo, " NOT SIMILAR TO ", backend: Pg);
@@ -31,6 +32,7 @@ infix_operator!(DifferenceNet, " - ", Bigint, backend: Pg);
 infix_operator!(HasKeyJsonb, " ? ", backend: Pg);
 infix_operator!(HasAnyKeyJsonb, " ?| ", backend: Pg);
 infix_operator!(HasAllKeysJsonb, " ?& ", backend: Pg);
+infix_operator!(RangeAdjacent, " -|- ", backend: Pg);
 infix_operator!(RemoveFromJsonb, " - ", Jsonb, backend: Pg);
 __diesel_infix_operator!(
     RetrieveAsObjectJson,


Test patch:
diff --git a/diesel_derives/tests/auto_type.rs b/diesel_derives/tests/auto_type.rs
index 223c68dce163..dc2c91c50c11 100644
--- a/diesel_derives/tests/auto_type.rs
+++ b/diesel_derives/tests/auto_type.rs
@@ -11,6 +11,9 @@ table! {
         id -> Integer,
         name -> Text,
         time -> Timestamp,
+        bigint -> BigInt,
+        numeric -> Numeric,
+        date -> Date,
     }
 }
 
@@ -46,6 +49,7 @@ table! {
         blob -> Binary,
         timestamp -> Timestamp,
         range -> Range<Integer>,
+        timestamptz -> Timestamptz,
     }
 }
 
@@ -287,7 +291,9 @@ fn test_pg_range_expression_methods() -> _ {
         .and(pg_extras::range.overlaps_with(my_range))
         .and(pg_extras::range.lesser_than(my_range))
         .and(pg_extras::range.greater_than(my_range))
-        .and(pg_extras::range.range_not_extends_right_to(my_range))
+        .and(pg_extras::range.range_extends_right_to(my_range))
+        .and(pg_extras::range.range_extends_left_to(my_range))
+        .and(pg_extras::id.is_contained_by_range(my_range))
         .and(
             pg_extras::range
                 .union_range(pg_extras::range)
@@ -309,12 +315,6 @@ fn test_pg_range_expression_methods() -> _ {
     // function. We could likely support it by
     // renaming the function to `.range_contains()` (or something similar)
     // .contains(42_i32)
-    //.select(
-    // this kind of free standing functions is not supported by auto_type yet
-    //lower(pg_extras::range),
-    // The auto_trait also didn't like this one
-    //int4range(None, Some(5i32), RangeBound::LowerBoundInclusiveUpperBoundInclusive),
-    //)
 }
 
 #[cfg(feature = "postgres")]
@@ -386,6 +386,34 @@ fn test_normal_functions() -> _ {
     ))
 }
 
+#[cfg(feature = "postgres")]
+#[auto_type]
+fn postgres_functions() -> _ {
+    let bound: sql_types::RangeBound =
+        sql_types::RangeBound::LowerBoundExclusiveUpperBoundExclusive;
+    (
+        lower(pg_extras::range),
+        upper(pg_extras::range),
+        isempty(pg_extras::range),
+        lower_inc(pg_extras::range),
+        upper_inc(pg_extras::range),
+        lower_inf(pg_extras::range),
+        upper_inf(pg_extras::range),
+        range_merge(pg_extras::range, pg_extras::range),
+        int4range(users::id.nullable(), users::id.nullable(), bound),
+        int8range(users::bigint.nullable(), users::bigint.nullable(), bound),
+        numrange(users::numeric.nullable(), users::numeric.nullable(), bound),
+        daterange(users::date.nullable(), users::date.nullable(), bound),
+        tsrange(users::time.nullable(), users::time.nullable(), bound),
+        tstzrange(
+            pg_extras::timestamptz.nullable(),
+            pg_extras::timestamptz.nullable(),
+            bound,
+        ),
+        array_append(pg_extras::array, pg_extras::id),
+    )
+}
+
 #[auto_type]
 fn with_lifetime<'a>(name: &'a str) -> _ {
     users::table.filter(users::name.eq(name))