Home • Explore • Blog • CI
☰



postgresql
2nd chunk of `doc/src/sgml/btree.sgml`
6858c16673055c1453584f7bac52fa036d952a6ee997cf9d0000000100000fa4
 <replaceable>B</replaceable>, <replaceable>C</replaceable> of the
    data type:

    <itemizedlist>
     <listitem>
      <para>
       <replaceable>A</replaceable> <literal>=</literal>
       <replaceable>A</replaceable> is true
       (<firstterm>reflexive law</firstterm>)
      </para>
     </listitem>
     <listitem>
      <para>
       if <replaceable>A</replaceable> <literal>=</literal>
       <replaceable>B</replaceable>,
       then <replaceable>B</replaceable> <literal>=</literal>
       <replaceable>A</replaceable>
       (<firstterm>symmetric law</firstterm>)
      </para>
     </listitem>
     <listitem>
      <para>
       if <replaceable>A</replaceable> <literal>=</literal>
       <replaceable>B</replaceable> and <replaceable>B</replaceable>
       <literal>=</literal> <replaceable>C</replaceable>,
       then <replaceable>A</replaceable> <literal>=</literal>
       <replaceable>C</replaceable>
       (<firstterm>transitive law</firstterm>)
      </para>
     </listitem>
    </itemizedlist>
   </para>
  </listitem>

  <listitem>
   <para>
    A <literal>&lt;</literal> operator must be a strong ordering relation;
    that is, for all non-null values <replaceable>A</replaceable>,
    <replaceable>B</replaceable>, <replaceable>C</replaceable>:

    <itemizedlist>
     <listitem>
      <para>
       <replaceable>A</replaceable> <literal>&lt;</literal>
       <replaceable>A</replaceable> is false
       (<firstterm>irreflexive law</firstterm>)
      </para>
     </listitem>
     <listitem>
      <para>
       if <replaceable>A</replaceable> <literal>&lt;</literal>
       <replaceable>B</replaceable>
       and <replaceable>B</replaceable> <literal>&lt;</literal>
       <replaceable>C</replaceable>,
       then <replaceable>A</replaceable> <literal>&lt;</literal>
       <replaceable>C</replaceable>
       (<firstterm>transitive law</firstterm>)
      </para>
     </listitem>
    </itemizedlist>
   </para>
  </listitem>

  <listitem>
   <para>
    Furthermore, the ordering is total; that is, for all non-null
    values <replaceable>A</replaceable>, <replaceable>B</replaceable>:

    <itemizedlist>
     <listitem>
      <para>
       exactly one of <replaceable>A</replaceable> <literal>&lt;</literal>
       <replaceable>B</replaceable>, <replaceable>A</replaceable>
       <literal>=</literal> <replaceable>B</replaceable>, and
       <replaceable>B</replaceable> <literal>&lt;</literal>
       <replaceable>A</replaceable> is true
       (<firstterm>trichotomy law</firstterm>)
      </para>
     </listitem>
    </itemizedlist>

    (The trichotomy law justifies the definition of the comparison support
    function, of course.)
   </para>
  </listitem>
 </itemizedlist>

 <para>
  The other three operators are defined in terms of <literal>=</literal>
  and <literal>&lt;</literal> in the obvious way, and must act consistently
  with them.
 </para>

 <para>
  For an operator family supporting multiple data types, the above laws must
  hold when <replaceable>A</replaceable>, <replaceable>B</replaceable>,
  <replaceable>C</replaceable> are taken from any data types in the family.
  The transitive laws are the trickiest to ensure, as in cross-type
  situations they represent statements that the behaviors of two or three
  different operators are consistent.
  As an example, it would not work to put <type>float8</type>
  and <type>numeric</type> into the same operator family, at least not with
  the current semantics that <type>numeric</type> values are converted
  to <type>float8</type> for comparison to a <type>float8</type>.  Because
  of the limited accuracy of <type>float8</type>, this means there are
  distinct <type>numeric</type> values that will compare equal to the
  same <type>float8</type> value, and thus the transitive law would fail.
 </para>

 <para>
  Another requirement for a multiple-data-type family is that any implicit
  or binary-coercion casts that are defined between data types included in
  the operator family
Title: B-Tree Indexes: Laws Governing Operator Families
Summary
This section details the laws governing B-Tree operator families in PostgreSQL. It focuses on the '<' operator as a strong ordering relation, emphasizing the irreflexive and transitive laws. It also describes the trichotomy law, which dictates the relationship between '<', '=', and '>' operators. The consistency requirements for operator families supporting multiple data types are also explained, particularly the transitive laws. The section also highlights the importance of consistent data type conversions within the family to maintain these laws.