Home • Explore • Blog • Models • CI
☰



postgresql
10th chunk of `doc/src/sgml/ref/create_table.sgml`
df602cbc867aa1bcec8ecafb882724680071e49d970f0dd40000000100000fa6
 <literal>TO</literal> list are
      not.  Note that this statement must be understood according to the
      rules of row-wise comparison (<xref linkend="row-wise-comparison"/>).
      For example, given <literal>PARTITION BY RANGE (x,y)</literal>, a partition
      bound <literal>FROM (1, 2) TO (3, 4)</literal>
      allows <literal>x=1</literal> with any <literal>y&gt;=2</literal>,
      <literal>x=2</literal> with any non-null <literal>y</literal>,
      and <literal>x=3</literal> with any <literal>y&lt;4</literal>.
     </para>

     <para>
      The special values <literal>MINVALUE</literal> and <literal>MAXVALUE</literal>
      may be used when creating a range partition to indicate that there
      is no lower or upper bound on the column's value. For example, a
      partition defined using <literal>FROM (MINVALUE) TO (10)</literal> allows
      any values less than 10, and a partition defined using
      <literal>FROM (10) TO (MAXVALUE)</literal> allows any values greater than
      or equal to 10.
     </para>

     <para>
      When creating a range partition involving more than one column, it
      can also make sense to use <literal>MAXVALUE</literal> as part of the lower
      bound, and <literal>MINVALUE</literal> as part of the upper bound. For
      example, a partition defined using
      <literal>FROM (0, MAXVALUE) TO (10, MAXVALUE)</literal> allows any rows
      where the first partition key column is greater than 0 and less than
      or equal to 10. Similarly, a partition defined using
      <literal>FROM ('a', MINVALUE) TO ('b', MINVALUE)</literal> allows any rows
      where the first partition key column starts with "a".
     </para>

     <para>
      Note that if <literal>MINVALUE</literal> or <literal>MAXVALUE</literal> is used for
      one column of a partitioning bound, the same value must be used for all
      subsequent columns.  For example, <literal>(10, MINVALUE, 0)</literal> is not
      a valid bound; you should write <literal>(10, MINVALUE, MINVALUE)</literal>.
     </para>

     <para>
      Also note that some element types, such as <literal>timestamp</literal>,
      have a notion of "infinity", which is just another value that can
      be stored. This is different from <literal>MINVALUE</literal> and
      <literal>MAXVALUE</literal>, which are not real values that can be stored,
      but rather they are ways of saying that the value is unbounded.
      <literal>MAXVALUE</literal> can be thought of as being greater than any
      other value, including "infinity" and <literal>MINVALUE</literal> as being
      less than any other value, including "minus infinity". Thus the range
      <literal>FROM ('infinity') TO (MAXVALUE)</literal> is not an empty range; it
      allows precisely one value to be stored &mdash; "infinity".
     </para>

     <para>
      If <literal>DEFAULT</literal> is specified, the table will be
      created as the default partition of the parent table.  This option
      is not available for hash-partitioned tables.  A partition key value
      not fitting into any other partition of the given parent will be
      routed to the default partition.
     </para>

     <para>
      When a table has an existing <literal>DEFAULT</literal> partition and
      a new partition is added to it, the default partition must
      be scanned to verify that it does not contain any rows which properly
      belong in the new partition.  If the default partition contains a
      large number of rows, this may be slow.  The scan will be skipped if
      the default partition is a foreign table or if it has a constraint which
      proves that it cannot contain rows which should be placed in the new
      partition.
     </para>

     <para>
      When creating a hash partition, a modulus and remainder must be specified.
      The modulus must be a positive integer, and the remainder must be a
      non-negative integer less than the modulus.  Typically, when initially
      setting
Title: CREATE TABLE Parameters: PARTITION OF (continued)
Summary
This section continues the explanation of the PARTITION OF clause. It further clarifies the use of MINVALUE and MAXVALUE in multi-column range partitions, explaining how they can be used for partial bounding. It emphasizes that if MINVALUE or MAXVALUE is used for one column in a partitioning bound, the same value must be used for subsequent columns. It distinguishes MINVALUE/MAXVALUE from element type infinities. The section then discusses the DEFAULT partition option, which is not available for hash-partitioned tables, and explains that values not fitting into other partitions are routed to it. It details the scan that occurs when adding a new partition to a table with a default partition to ensure data integrity, and how the scan can be skipped under certain conditions. Finally, it introduces the requirement for specifying a modulus and remainder when creating hash partitions.