Home • Explore • Blog • CI
☰



postgresql
4th chunk of `doc/src/sgml/func.sgml`
4c1aeb21a8c80b978c778974d33af7d3e728ccd7d8b751960000000100000fa6
 shown above, all comparison operators are binary operators that
    return values of type <type>boolean</type>.  Thus, expressions like
    <literal>1 &lt; 2 &lt; 3</literal> are not valid (because there is
    no <literal>&lt;</literal> operator to compare a Boolean value with
    <literal>3</literal>).  Use the <literal>BETWEEN</literal> predicates
    shown below to perform range tests.
   </para>

   <para>
    There are also some comparison predicates, as shown in <xref
    linkend="functions-comparison-pred-table"/>.  These behave much like
    operators, but have special syntax mandated by the SQL standard.
   </para>

   <table id="functions-comparison-pred-table">
    <title>Comparison Predicates</title>
    <tgroup cols="1">
     <thead>
      <row>
       <entry role="func_table_entry"><para role="func_signature">
        Predicate
       </para>
       <para>
        Description
       </para>
       <para>
        Example(s)
       </para></entry>
      </row>
     </thead>

     <tbody>
      <row>
       <entry role="func_table_entry"><para role="func_signature">
        <replaceable>datatype</replaceable> <literal>BETWEEN</literal> <replaceable>datatype</replaceable> <literal>AND</literal> <replaceable>datatype</replaceable>
        <returnvalue>boolean</returnvalue>
       </para>
       <para>
        Between (inclusive of the range endpoints).
       </para>
       <para>
        <literal>2 BETWEEN 1 AND 3</literal>
        <returnvalue>t</returnvalue>
       </para>
       <para>
        <literal>2 BETWEEN 3 AND 1</literal>
        <returnvalue>f</returnvalue>
       </para></entry>
      </row>

      <row>
       <entry role="func_table_entry"><para role="func_signature">
        <replaceable>datatype</replaceable> <literal>NOT BETWEEN</literal> <replaceable>datatype</replaceable> <literal>AND</literal> <replaceable>datatype</replaceable>
        <returnvalue>boolean</returnvalue>
       </para>
       <para>
        Not between (the negation of <literal>BETWEEN</literal>).
       </para>
       <para>
        <literal>2 NOT BETWEEN 1 AND 3</literal>
        <returnvalue>f</returnvalue>
       </para></entry>
      </row>

      <row>
       <entry role="func_table_entry"><para role="func_signature">
        <replaceable>datatype</replaceable> <literal>BETWEEN SYMMETRIC</literal> <replaceable>datatype</replaceable> <literal>AND</literal> <replaceable>datatype</replaceable>
        <returnvalue>boolean</returnvalue>
       </para>
       <para>
        Between, after sorting the two endpoint values.
       </para>
       <para>
        <literal>2 BETWEEN SYMMETRIC 3 AND 1</literal>
        <returnvalue>t</returnvalue>
       </para></entry>
      </row>

      <row>
       <entry role="func_table_entry"><para role="func_signature">
        <replaceable>datatype</replaceable> <literal>NOT BETWEEN SYMMETRIC</literal> <replaceable>datatype</replaceable> <literal>AND</literal> <replaceable>datatype</replaceable>
        <returnvalue>boolean</returnvalue>
       </para>
       <para>
        Not between, after sorting the two endpoint values.
       </para>
       <para>
        <literal>2 NOT BETWEEN SYMMETRIC 3 AND 1</literal>
        <returnvalue>f</returnvalue>
       </para></entry>
      </row>

      <row>
       <entry role="func_table_entry"><para role="func_signature">
        <replaceable>datatype</replaceable> <literal>IS DISTINCT FROM</literal> <replaceable>datatype</replaceable>
        <returnvalue>boolean</returnvalue>
       </para>
       <para>
        Not equal, treating null as a comparable value.
       </para>
       <para>
        <literal>1 IS DISTINCT FROM NULL</literal>
        <returnvalue>t</returnvalue> (rather than <literal>NULL</literal>)
       </para>
       <para>
        <literal>NULL IS DISTINCT FROM NULL</literal>
        <returnvalue>f</returnvalue> (rather than <literal>NULL</literal>)
       </para></entry>
      </row>

      <row>
       <entry role="func_table_entry"><para
Title: Comparison Predicates: BETWEEN, IS DISTINCT FROM
Summary
This section details comparison predicates in PostgreSQL, which are similar to operators but have SQL-standard-mandated syntax. It includes a table of comparison predicates, focusing on BETWEEN (inclusive range), NOT BETWEEN (negation of BETWEEN), BETWEEN SYMMETRIC (sorts endpoint values before comparison), NOT BETWEEN SYMMETRIC (negation of BETWEEN SYMMETRIC), and IS DISTINCT FROM (not equal, treating null as a comparable value).