Home Explore Blog CI



postgresql

6th chunk of `doc/src/sgml/ref/create_table.sgml`
218bc81282a92128fb9ba99ed70d6f767e712ff7bdca74a50000000100000fa5
 type (that is,
      one created using <xref linkend="sql-createtype"/>) though it still
      produces a new composite type as well.  The table will have a
      dependency on the referenced type, meaning that cascaded alter and
      drop actions on that type will propagate to the table.
     </para>

     <para>
      A typed table always has the same column names and data types as the
      type it is derived from, so you cannot specify additional columns.
      But the <literal>CREATE TABLE</literal> command can add defaults
      and constraints to the table, as well as specify storage parameters.
     </para>
    </listitem>
   </varlistentry>

   <varlistentry id="sql-createtable-parms-column-name">
    <term><replaceable class="parameter">column_name</replaceable></term>
    <listitem>
     <para>
      The name of a column to be created in the new table.
     </para>
    </listitem>
   </varlistentry>

   <varlistentry id="sql-createtable-parms-data-type">
    <term><replaceable class="parameter">data_type</replaceable></term>
    <listitem>
     <para>
      The data type of the column. This can include array
      specifiers. For more information on the data types supported by
      <productname>PostgreSQL</productname>, refer to <xref
      linkend="datatype"/>.
     </para>
    </listitem>
   </varlistentry>

   <varlistentry id="sql-createtable-parms-collate">
    <term><literal>COLLATE <replaceable>collation</replaceable></literal></term>
    <listitem>
     <para>
      The <literal>COLLATE</literal> clause assigns a collation to
      the column (which must be of a collatable data type).
      If not specified, the column data type's default collation is used.
     </para>
    </listitem>
   </varlistentry>

   <varlistentry id="sql-createtable-parms-storage">
    <term>
     <literal>STORAGE { PLAIN | EXTERNAL | EXTENDED | MAIN | DEFAULT }</literal>
     <indexterm>
      <primary>TOAST</primary>
      <secondary>per-column storage settings</secondary>
     </indexterm>
    </term>
    <listitem>
     <para>
      This form sets the storage mode for the column. This controls whether this
      column is held inline or in a secondary <acronym>TOAST</acronym> table,
      and whether the data should be compressed or not. <literal>PLAIN</literal>
      must be used for fixed-length values such as <type>integer</type> and is
      inline, uncompressed. <literal>MAIN</literal> is for inline, compressible
      data. <literal>EXTERNAL</literal> is for external, uncompressed data, and
      <literal>EXTENDED</literal> is for external, compressed data.
      Writing <literal>DEFAULT</literal> sets the storage mode to the default
      mode for the column's data type.  <literal>EXTENDED</literal> is the
      default for most data types that support non-<literal>PLAIN</literal>
      storage.
      Use of <literal>EXTERNAL</literal> will make substring operations on
      very large <type>text</type> and <type>bytea</type> values run faster,
      at the penalty of increased storage space.
      See <xref linkend="storage-toast"/> for more information.
     </para>
    </listitem>
   </varlistentry>

   <varlistentry id="sql-createtable-parms-compression">
    <term><literal>COMPRESSION <replaceable class="parameter">compression_method</replaceable></literal></term>
    <listitem>
     <para>
      The <literal>COMPRESSION</literal> clause sets the compression method
      for the column.  Compression is supported only for variable-width data
      types, and is used only when the column's storage mode
      is <literal>main</literal> or <literal>extended</literal>.
      (See <xref linkend="sql-altertable"/> for information on
      column storage modes.) Setting this property for a partitioned table
      has no direct effect, because such tables have no storage of their own,
      but the configured value will be inherited by newly-created partitions.
      The supported compression methods are <literal>pglz</literal>

Title: CREATE TABLE Parameters: column_name, data_type, COLLATE, STORAGE and COMPRESSION
Summary
This section describes parameters related to defining columns in a CREATE TABLE statement. It covers column_name, data_type, COLLATE for assigning a collation to a column, STORAGE to specify the storage mode (PLAIN, EXTERNAL, EXTENDED, MAIN, DEFAULT) and TOAST considerations, and COMPRESSION to set the compression method for variable-width data types when storage mode is main or extended.