ddl.sgml

PostgreSQL7.4.6 for Linux

    and
<programlisting>
CREATE TABLE public.products ( ... );
</programlisting>
   </para>
  </sect2>

  <sect2 id="ddl-schemas-path">
   <title>The Schema Search Path</title>

   <indexterm>
    <primary>search path</primary>
   </indexterm>

   <indexterm>
    <primary>unqualified name</primary>
   </indexterm>

   <indexterm>
    <primary>name</primary>
    <secondary>unqualified</secondary>
   </indexterm>

   <para>
    Qualified names are tedious to write, and it's often best not to
    wire a particular schema name into applications anyway.  Therefore
    tables are often referred to by <firstterm>unqualified names</>,
    which consist of just the table name.  The system determines which table
    is meant by following a <firstterm>search path</>, which is a list
    of schemas to look in.  The first matching table in the search path
    is taken to be the one wanted.  If there is no match in the search
    path, an error is reported, even if matching table names exist
    in other schemas in the database.
   </para>

   <indexterm>
    <primary>schema</primary>
    <secondary>current</secondary>
   </indexterm>

   <para>
    The first schema named in the search path is called the current schema.
    Aside from being the first schema searched, it is also the schema in
    which new tables will be created if the <command>CREATE TABLE</>
    command does not specify a schema name.
   </para>

   <indexterm>
    <primary>search_path</primary>
   </indexterm>

   <para>
    To show the current search path, use the following command:
<programlisting>
SHOW search_path;
</programlisting>
    In the default setup this returns:
<screen>
 search_path
--------------
 $user,public
</screen>
    The first element specifies that a schema with the same name as
    the current user is to be searched.  If no such schema exists,
    the entry is ignored.  The second element refers to the
    public schema that we have seen already.
   </para>

   <para>
    The first schema in the search path that exists is the default
    location for creating new objects.  That is the reason that by
    default objects are created in the public schema.  When objects
    are referenced in any other context without schema qualification
    (table modification, data modification, or query commands) the
    search path is traversed until a matching object is found.
    Therefore, in the default configuration, any unqualified access
    again can only refer to the public schema.
   </para>

   <para>
    To put our new schema in the path, we use
<programlisting>
SET search_path TO myschema,public;
</programlisting>
    (We omit the <literal>$user</literal> here because we have no
    immediate need for it.)  And then we can access the table without
    schema qualification:
<programlisting>
DROP TABLE mytable;
</programlisting>
    Also, since <literal>myschema</literal> is the first element in
    the path, new objects would by default be created in it.
   </para>

   <para>
    We could also have written
<programlisting>
SET search_path TO myschema;
</programlisting>
    Then we no longer have access to the public schema without
    explicit qualification.  There is nothing special about the public
    schema except that it exists by default.  It can be dropped, too.
   </para>

   <para>
    See also <xref linkend="functions-misc"> for other ways to access
    the schema search path.
   </para>

   <para>
    The search path works in the same way for data type names, function names,
    and operator names as it does for table names.  Data type and function
    names can be qualified in exactly the same way as table names.  If you
    need to write a qualified operator name in an expression, there is a
    special provision: you must write
<synopsis>
<literal>OPERATOR(</><replaceable>schema</><literal>.</><replaceable>operator</><literal>)</>
</synopsis>
    This is needed to avoid syntactic ambiguity.  An example is
<programlisting>
SELECT 3 OPERATOR(pg_catalog.+) 4;
</programlisting>
    In practice one usually relies on the search path for operators,
    so as not to have to write anything so ugly as that.
   </para>
  </sect2>

  <sect2 id="ddl-schemas-priv">
   <title>Schemas and Privileges</title>

   <indexterm zone="ddl-schemas-priv">
    <primary>privilege</primary>
    <secondary sortas="schemas">for schemas</secondary>
   </indexterm>

   <para>
    By default, users cannot access any objects in schemas they do not
    own.  To allow that, the owner of the schema needs to grant the
    <literal>USAGE</literal> privilege on the schema.  To allow users
    to make use of the objects in the schema, additional privileges
    may need to be granted, as appropriate for the object.
   </para>

   <para>
    A user can also be allowed to create objects in someone else's
    schema.  To allow that, the <literal>CREATE</literal> privilege on
    the schema needs to be granted.  Note that by default, everyone
    has <literal>CREATE</literal> and <literal>USAGE</literal> privileges on
    the schema 
    <literal>public</literal>.  This allows all users that are able to
    connect to a given database to create objects in its
    <literal>public</literal> schema.  If you do
    not want to allow that, you can revoke that privilege:
<programlisting>
REVOKE CREATE ON SCHEMA public FROM PUBLIC;
</programlisting>
    (The first <quote>public</quote> is the schema, the second
    <quote>public</quote> means <quote>every user</quote>.  In the
    first sense it is an identifier, in the second sense it is a
    reserved word, hence the different capitalization; recall the
    guidelines from <xref linkend="sql-syntax-identifiers">.)
   </para>
  </sect2>

  <sect2 id="ddl-schemas-catalog">
   <title>The System Catalog Schema</title>

   <indexterm zone="ddl-schemas-catalog">
    <primary>system catalog</primary>
    <secondary>schema</secondary>
   </indexterm>

   <para>
    In addition to <literal>public</> and user-created schemas, each
    database contains a <literal>pg_catalog</> schema, which contains
    the system tables and all the built-in data types, functions, and
    operators.  <literal>pg_catalog</> is always effectively part of
    the search path.  If it is not named explicitly in the path then
    it is implicitly searched <emphasis>before</> searching the path's
    schemas.  This ensures that built-in names will always be
    findable.  However, you may explicitly place
    <literal>pg_catalog</> at the end of your search path if you
    prefer to have user-defined names override built-in names.
   </para>

   <para>
    In <productname>PostgreSQL</productname> versions before 7.3,
    table names beginning with <literal>pg_</> were reserved.  This is
    no longer true: you may create such a table name if you wish, in
    any non-system schema.  However, it's best to continue to avoid
    such names, to ensure that you won't suffer a conflict if some
    future version defines a system table named the same as your
    table.  (With the default search path, an unqualified reference to
    your table name would be resolved as the system table instead.)
    System tables will continue to follow the convention of having
    names beginning with <literal>pg_</>, so that they will not
    conflict with unqualified user-table names so long as users avoid
    the <literal>pg_</> prefix.
   </para>
  </sect2>

  <sect2 id="ddl-schemas-patterns">
   <title>Usage Patterns</title>

   <para>
    Schemas can be used to organize your data in many ways.  There are
    a few usage patterns that are recommended and are easily supported by
    the default configuration:
    <itemizedlist>
     <listitem>
      <para>
       If you do not create any schemas then all users access the
       public schema implicitly.  This simulates the situation where
       schemas are not available at all.  This setup is mainly
       recommended when there is only a single user or a few cooperating
       users in a database.  This setup also allows smooth transition
       from the non-schema-aware world.
      </para>
     </listitem>

     <listitem>
      <para>
       You can create a schema for each user with the same name as
       that user.  Recall that the default search path starts with
       <literal>$user</literal>, which resolves to the user name.
       Therefore, if each user has a separate schema, they access their
       own schemas by default.
      </para>

      <para>
       If you use this setup then you might also want to revoke access
       to the public schema (or drop it altogether), so users are
       truly constrained to their own schemas.
      </para>
     </listitem>

     <listitem>
      <para>
       To install shared applications (tables to be used by everyone,
       additional functions provided by third parties, etc.), put them
       into separate schemas.  Remember to grant appropriate
       privileges to allow the other users to access them.  Users can
       then refer to these additional objects by qualifying the names
       with a schema name, or they can put the additional schemas into
       their path, as they choose.
      </para>
     </listitem>
    </itemizedlist>
   </para>
  </sect2>

  <sect2 id="ddl-schemas-portability">
   <title>Portability</title>

   <para>
    In the SQL standard, the notion of objects in the same schema
    being owned by different users does not exist.  Moreover, some
    implementations do not allow you to create schemas that have a
    different name than their owner.  In fact, the concepts of schema
    and user are nearly equivalent in a database system that
    implements only the basic schema support specified in the
    standard.  Therefore, many users consider qualified names to
    really consist of
    <literal><replaceable>username</>.<replaceable>tablename</></literal>.
    This is how <productname>PostgreSQL</productname> will effectively
    behave if you create a per-user schema for every user.
   </para>

   <para>
    Also, there is no concept of a <literal>public</> schema in the
    SQL standard.  For maximum conformance to the standard, you should
    not use (perhaps even remove) the <literal>public</> schema.
   </para>

   <para>
    Of course, some SQL database systems might not implement schemas
    at all, or provide namespace support by allowing (possibly
    limited) cross-database access.  If you need to work with those
    systems, then maximum portability would be achieved by not using
    schemas at all.
   </para>
  </sect2>
 </sect1>

 <sect1 id="ddl-others">
  <title>Other Database Objects</title>

  <para>
   Tables are the central objects in a relational database structure,
   because they hold your data.  But they are not the only objects
   that exist in a database.  Many other kinds of objects can be
   created to make the use and management of the data more efficient
   or convenient.  They are not discussed in this chapter, but we give
   you a list here so that you are aware of what is possible.
  </para>

  <itemizedlist>
   <listitem>
    <para>
     Views
    </para>
   </listitem>

   <listitem>
    <para>
     Functions, operators, data types, domains
    </para>
   </listitem>

   <listitem>
    <para>
     Triggers and rewrite rules
    </para>
   </listitem>
  </itemizedlist>

  <para>
   Detailed information on
   these topics appears in <xref linkend="server-programming">.
  </para>
 </sect1>

 <sect1 id="ddl-depend">
  <title>Dependency Tracking</title>

  <indexterm zone="ddl-depend">
   <primary>CASCADE</primary>
   <secondary sortas="DROP">with DROP</secondary>
  </indexterm>

  <indexterm zone="ddl-depend">
   <primary>RESTRICT</primary>
   <secondary sortas="DROP">with DROP</secondary>
  </indexterm>

  <para>
   When you create complex database structures involving many tables
   with foreign key constraints, views, triggers, functions, etc. you
   will implicitly create a net of dependencies between the objects.
   For instance, a table with a foreign key constraint depends on the
   table it references.
  </para>

  <para>
   To ensure the integrity of the entire database structure,
   <productname>PostgreSQL</productname> makes sure that you cannot
   drop objects that other objects still depend on.  For example,
   attempting to drop the products table we had considered in <xref
   linkend="ddl-constraints-fk">, with the orders table depending on
   it, would result in an error message such as this:
<screen>
DROP TABLE products;

NOTICE:  constraint $1 on table orders depends on table products
ERROR:  cannot drop table products because other objects depend on it
HINT:  Use DROP ... CASCADE to drop the dependent objects too.
</screen>
   The error message contains a useful hint: if you do not want to
   bother deleting all the dependent objects individually, you can run
<screen>
DROP TABLE products CASCADE;
</screen>
   and all the dependent objects will be removed.  In this case, it
   doesn't remove the orders table, it only removes the foreign key
   constraint.  (If you want to check what <literal>DROP ... CASCADE</> will do,
   run <command>DROP</> without <literal>CASCADE</> and read the <literal>NOTICE</> messages.)
  </para>

  <para>
   All drop commands in <productname>PostgreSQL</productname> support
   specifying <literal>CASCADE</literal>.  Of course, the nature of
   the possible dependencies varies with the type of the object.  You
   can also write <literal>RESTRICT</literal> instead of
   <literal>CASCADE</literal> to get the default behavior, which is to
   prevent drops of objects that other objects depend on.
  </para>

  <note>
   <para>
    According to the SQL standard, specifying either
    <literal>RESTRICT</literal> or <literal>CASCADE</literal> is
    required.  No database system actually implements it that way, but
    whether the default behavior is <literal>RESTRICT</literal> or
    <literal>CASCADE</literal> varies across systems.
   </para>
  </note>

  <note>
   <para>
    Foreign key constraint dependencies and serial column dependencies
    from <productname>PostgreSQL</productname> versions prior to 7.3
    are <emphasis>not</emphasis> maintained or created during the
    upgrade process.  All other dependency types will be properly
    created during an upgrade.
   </para>
  </note>
 </sect1>

</chapter>