information_schema.sgml

PostgreSQL7.4.6 for Linux

  <para>
   Since data types can be defined in a variety of ways in SQL, and
   <productname>PostgreSQL</productname> contains additional ways to
   define data types, their representation in the information schema
   can be somewhat difficult.  The column <literal>data_type</literal>
   is supposed to identify the underlying built-in type of the column.
   In <productname>PostgreSQL</productname>, this means that the type
   is defined in the system catalog schema
   <literal>pg_catalog</literal>.  This column may be useful if the
   application can handle the well-known built-in types specially (for
   example, format the numeric types differently or use the data in
   the precision columns).  The columns <literal>udt_name</literal>,
   <literal>udt_schema</literal>, and <literal>udt_catalog</literal>
   always identify the underlying data type of the column, even if the
   column is based on a domain.  (Since
   <productname>PostgreSQL</productname> treats built-in types like
   user-defined types, built-in types appear here as well.  This is an
   extension of the SQL standard.)  These columns should be used if an
   application wants to process data differently according to the
   type, because in that case it wouldn't matter if the column is
   really based on a domain.  If the column is based on a domain, the
   identity of the domain is stored in the columns
   <literal>domain_name</literal>, <literal>domain_schema</literal>,
   and <literal>domain_catalog</literal>.  If you want to pair up
   columns with their associated data types and treat domains as
   separate types, you could write <literal>coalesce(domain_name,
   udt_name)</literal>, etc.
  </para>
 </sect1>

 <sect1 id="infoschema-constraint-column-usage">
  <title><literal>constraint_column_usage</literal></title>

  <para>
   The view <literal>constraint_column_usage</literal> identifies all
   columns in the current database that are used by some constraint.
   Only those columns are shown that are contained in a table owned
   the current user.  For a check constraint, this view identifies the
   columns that are used in the check expression.  For a foreign key
   constraint, this view identifies the columns that the foreign key
   references.  For a unique or primary key constraint, this view
   identifies the constrained columns.
  </para>

  <table>
   <title><literal>constraint_column_usage</literal> Columns</title>

   <tgroup cols="3">
    <thead>
     <row>
      <entry>Name</entry>
      <entry>Data Type</entry>
      <entry>Description</entry>
     </row>
    </thead>

    <tbody>
     <row>
      <entry><literal>table_catalog</literal></entry>
      <entry><type>sql_identifier</type></entry>
      <entry>
       Name of the database that contains the table that contains the
       column that is used by some constraint (always the current
       database)
      </entry>
     </row>

     <row>
      <entry><literal>table_schema</literal</entry>
      <entry><type>sql_identifier</type></entry>
      <entry>
       Name of the schema that contains the table that contains the
       column that is used by some constraint
      </entry>
     </row>

     <row>
      <entry><literal>table_name</literal</entry>
      <entry><type>sql_identifier</type></entry>
      <entry>
       Name of the table that contains the column that is used by some
       constraint
      </entry>
     </row>

     <row>
      <entry><literal>column_name</literal</entry>
      <entry><type>sql_identifier</type></entry>
      <entry>
       Name of the column that is used by some constraint
      </entry>
     </row>

     <row>
      <entry><literal>constraint_catalog</literal></entry>
      <entry><type>sql_identifier</type></entry>
      <entry>Name of the database that contains the constraint (always the current database)</entry>
     </row>

     <row>
      <entry><literal>constraint_schema</literal</entry>
      <entry><type>sql_identifier</type></entry>
      <entry>Name of the schema that contains the constraint</entry>
     </row>

     <row>
      <entry><literal>constraint_name</literal</entry>
      <entry><type>sql_identifier</type></entry>
      <entry>Name of the constraint</entry>
     </row>
    </tbody>
   </tgroup>
  </table>
 </sect1>

 <sect1 id="infoschema-constraint-table-usage">
  <title><literal>constraint_table_usage</literal></title>

  <para>
   The view <literal>constraint_table_usage</literal> identifies all
   tables in the current database that are used by some constraint and
   are owned by the current user.  (This is different from the view
   <literal>table_constraints</literal>, which identifies all table
   constraints along with the table they are defined on.)  For a
   foreign key constraint, this view identifies the table that the
   foreign key references.  For a unique or primary key constraint,
   this view simply identifies the table the constraint belongs to.
   Check constraints and not-null constraints are not included in this
   view.
  </para>

  <table>
   <title><literal>constraint_table_usage</literal> Columns</title>

   <tgroup cols="3">
    <thead>
     <row>
      <entry>Name</entry>
      <entry>Data Type</entry>
      <entry>Description</entry>
     </row>
    </thead>

    <tbody>
     <row>
      <entry><literal>table_catalog</literal></entry>
      <entry><type>sql_identifier</type></entry>
      <entry>
       Name of the database that contains the table that is used by
       some constraint (always the current database)
      </entry>
     </row>

     <row>
      <entry><literal>table_schema</literal</entry>
      <entry><type>sql_identifier</type></entry>
      <entry>
       Name of the schema that contains the table that is used by some
       constraint
      </entry>
     </row>

     <row>
      <entry><literal>table_name</literal</entry>
      <entry><type>sql_identifier</type></entry>
      <entry>Name of the table that is used by some constraint</entry>
     </row>

     <row>
      <entry><literal>constraint_catalog</literal></entry>
      <entry><type>sql_identifier</type></entry>
      <entry>Name of the database that contains the constraint (always the current database)</entry>
     </row>

     <row>
      <entry><literal>constraint_schema</literal</entry>
      <entry><type>sql_identifier</type></entry>
      <entry>Name of the schema that contains the constraint</entry>
     </row>

     <row>
      <entry><literal>constraint_name</literal</entry>
      <entry><type>sql_identifier</type></entry>
      <entry>Name of the constraint</entry>
     </row>
    </tbody>
   </tgroup>
  </table>
 </sect1>

 <sect1 id="infoschema-data-type-privileges">
  <title><literal>data_type_privileges</literal></title>

  <para>
   The view <literal>data_type_privileges</literal> identifies all
   data type descriptors that the current user has access to, by way
   of being the owner of the described object or having some privilege
   for it.  A data type descriptor is generated whenever a data type
   is used in the definition of a table column, a domain, or a
   function (as parameter or return type) and stores some information
   about how the data type is used in that instance (for example, the
   declared maximum length, if applicable).  Each data type
   descriptors is assigned an arbitrary identifier that is unique
   among the data type descriptor identifiers assigned for one object
   (table, domain, function).  This view is probably not useful for
   applications, but it is used to define some other views in the
   information schema.
  </para>

  <table>
   <title><literal>data_type_privileges</literal> Columns</title>

   <tgroup cols="3">
    <thead>
     <row>
      <entry>Name</entry>
      <entry>Data Type</entry>
      <entry>Description</entry>
     </row>
    </thead>

    <tbody>
     <row>
      <entry><literal>object_catalog</literal></entry>
      <entry><type>sql_identifier</type></entry>
      <entry>Name of the database that contains the described object (always the current database)</entry>
     </row>

     <row>
      <entry><literal>object_schema</literal</entry>
      <entry><type>sql_identifier</type></entry>
      <entry>Name of the schema that contains the described object</entry>
     </row>

     <row>
      <entry><literal>object_name</literal</entry>
      <entry><type>sql_identifier</type></entry>
      <entry>Name of the described object</entry>
     </row>

     <row>
      <entry><literal>object_type</literal</entry>
      <entry><type>character_data</type></entry>
      <entry>
       The type of the described object: one of
       <literal>TABLE</literal> (the data type descriptor pertains to
       a column of that table), <literal>DOMAIN</literal> (the data
       type descriptors pertains to that domain),
       <literal>ROUTINE</literal> (the data type descriptor pertains
       to a parameter or the return data type of that function).
      </entry>
     </row>

     <row>
      <entry><literal>dtd_identifier</literal</entry>
      <entry><type>sql_identifier</type></entry>
      <entry>
       The identifier of the data type descriptor, which is unique
       among the data type descriptors for that same object.
      </entry>
     </row>
    </tbody>
   </tgroup>
  </table>
 </sect1>

 <sect1 id="infoschema-domain-constraints">
  <title><literal>domain_constraints</literal></title>

  <para>
   The view <literal>domain_constraints</literal> contains all
   constraints belonging to domains owned by the current user.
  </para>

  <table>
   <title><literal>domain_constraints</literal> Columns</title>

   <tgroup cols="3">
    <thead>
     <row>
      <entry>Name</entry>
      <entry>Data Type</entry>
      <entry>Description</entry>
     </row>
    </thead>

    <tbody>
     <row>
      <entry><literal>constraint_catalog</literal></entry>
      <entry><type>sql_identifier</type></entry>
      <entry>Name of the database that contains the constraint (always the current database)</entry>
     </row>

     <row>
      <entry><literal>constraint_schema</literal</entry>
      <entry><type>sql_identifier</type></entry>
      <entry>Name of the schema that contains the constraint</entry>
     </row>

     <row>
      <entry><literal>constraint_name</literal</entry>
      <entry><type>sql_identifier</type></entry>
      <entry>Name of the constraint</entry>
     </row>

     <row>
      <entry><literal>domain_catalog</literal></entry>
      <entry><type>sql_identifier</type></entry>
      <entry>Name of the database that contains the domain (always the current database)</entry>
     </row>

     <row>
      <entry><literal>domain_schema</literal</entry>
      <entry><type>sql_identifier</type></entry>
      <entry>Name of the schema that contains the domain</entry>
     </row>

     <row>
      <entry><literal>domain_name</literal</entry>
      <entry><type>sql_identifier</type></entry>
      <entry>Name of the domain</entry>
     </row>

     <row>
      <entry><literal>is_deferrable</literal></entry>
      <entry><type>character_data</type></entry>
      <entry><literal>YES</literal> if the constraint is deferrable, <literal>NO</literal> if not</entry>
     </row>

     <row>
      <entry><literal>initially_deferred</literal></entry>
      <entry><type>character_data</type></entry>
      <entry><literal>YES</literal> if the constraint is deferrable and initially deferred, <literal>NO</literal> if not</entry>
     </row>
    </tbody>
   </tgroup>
  </table>
 </sect1>

 <sect1 id="infoschema-domain-udt-usage">
  <title><literal>domain_udt_usage</literal></title>

  <para>
   The view <literal>domain_udt_usage</literal> identifies all columns
   that use data types owned by the current user. Note that in
   <productname>PostgreSQL</productname>, built-in data types behave
   like user-defined types, so they are included here as well.
  </para>

  <table>
   <title><literal>domain_udt_usage</literal> Columns</title>

   <tgroup cols="3">
    <thead>
     <row>
      <entry>Name</entry>
      <entry>Data Type</entry>
      <entry>Description</entry>
     </row>
    </thead>

    <tbody>
     <row>
      <entry><literal>udt_catalog</literal></entry>
      <entry><type>sql_identifier</type></entry>
      <entry>Name of the database that the domain data type is defined in (always the current database)</entry>
     </row>

     <row>
      <entry><literal>udt_schema</literal></entry>
      <entry><type>sql_identifier</type></entry>
      <entry>Name of the schema that the domain data type is defined in</entry>
     </row>

     <row>
      <entry><literal>udt_name</literal></entry>
      <entry><type>sql_identifier</type></entry>
      <entry>Name of the domain data type</entry>
     </row>

     <row>
      <entry><literal>domain_catalog</literal></entry>
      <entry><type>sql_identifier</type></entry>
      <entry>Name of the database that contains the domain (always the current database)</entry>
     </row>

     <row>
      <entry><literal>domain_schema</literal</entry>
      <entry><type>sql_identifier</type></entry>
      <entry>Name of the schema that contains the domain</entry>
     </row>

     <row>
      <entry><literal>domain_name</literal</entry>
      <entry><type>sql_identifier</type></entry>
      <entry>Name of the domain</entry>
     </row>
    </tbody>
   </tgroup>
  </table>