ddl.sgml

PostgreSQL7.4.6 for Linux

    initially has to contain null values.  But you can add a not-null
    constraint later.  Also, you cannot define a default value on a
    new column.  According to the SQL standard, this would have to
    fill the new columns in the existing rows with the default value,
    which is not implemented yet.  But you can adjust the column
    default later on.
   </para>
  </sect2>

  <sect2>
   <title>Removing a Column</title>

   <indexterm>
    <primary>column</primary>
    <secondary>removing</secondary>
   </indexterm>

   <para>
    To remove a column, use this command:
<programlisting>
ALTER TABLE products DROP COLUMN description;
</programlisting>
   </para>
  </sect2>

  <sect2>
   <title>Adding a Constraint</title>

   <indexterm>
    <primary>constraint</primary>
    <secondary>adding</secondary>
   </indexterm>

   <para>
    To add a constraint, the table constraint syntax is used.  For example:
<programlisting>
ALTER TABLE products ADD CHECK (name &lt;&gt; '');
ALTER TABLE products ADD CONSTRAINT some_name UNIQUE (product_no);
ALTER TABLE products ADD FOREIGN KEY (product_group_id) REFERENCES product_groups;
</programlisting>
    To add a not-null constraint, which cannot be written as a table
    constraint, use this syntax:
<programlisting>
ALTER TABLE products ALTER COLUMN product_no SET NOT NULL;
</programlisting>
   </para>

   <para>
    The constraint will be checked immediately, so the table data must
    satisfy the constraint before it can be added.
   </para>
  </sect2>

  <sect2>
   <title>Removing a Constraint</title>

   <indexterm>
    <primary>constraint</primary>
    <secondary>removing</secondary>
   </indexterm>

   <para>
    To remove a constraint you need to know its name.  If you gave it
    a name then that's easy.  Otherwise the system assigned a
    generated name, which you need to find out.  The
    <application>psql</application> command <literal>\d
    <replaceable>tablename</replaceable></literal> can be helpful
    here; other interfaces might also provide a way to inspect table
    details.  Then the command is:
<programlisting>
ALTER TABLE products DROP CONSTRAINT some_name;
</programlisting>
    (If you are dealing with a generated constraint name like <literal>$2</>,
    don't forget that you'll need to double-quote it to make it a valid
    identifier.)
   </para>

   <para>
    This works the same for all constraint types except not-null
    constraints. To drop a not null constraint use
<programlisting>
ALTER TABLE products ALTER COLUMN product_no DROP NOT NULL;
</programlisting>
    (Recall that not-null constraints do not have names.)
   </para>
  </sect2>

  <sect2>
   <title>Changing the Default</title>

   <indexterm>
    <primary>default value</primary>
    <secondary>changing</secondary>
   </indexterm>

   <para>
    To set a new default for a column, use a command like this:
<programlisting>
ALTER TABLE products ALTER COLUMN price SET DEFAULT 7.77;
</programlisting>
    To remove any default value, use
<programlisting>
ALTER TABLE products ALTER COLUMN price DROP DEFAULT;
</programlisting>
    This is equivalent to setting the default to null, at least in
    <productname>PostgreSQL</>.  As a consequence, it is not an error
    to drop a default where one hadn't been defined, because the
    default is implicitly the null value.
   </para>
  </sect2>

  <sect2>
   <title>Renaming a Column</title>

   <indexterm>
    <primary>column</primary>
    <secondary>renaming</secondary>
   </indexterm>

   <para>
    To rename a column:
<programlisting>
ALTER TABLE products RENAME COLUMN product_no TO product_number;
</programlisting>
   </para>
  </sect2>

  <sect2>
   <title>Renaming a Table</title>

   <indexterm>
    <primary>table</primary>
    <secondary>renaming</secondary>
   </indexterm>

   <para>
    To rename a table:
<programlisting>
ALTER TABLE products RENAME TO items;
</programlisting>
   </para>
  </sect2>
 </sect1>
 
 <sect1 id="ddl-priv">
  <title>Privileges</title>

  <indexterm zone="ddl-priv">
   <primary>privilege</primary>
  </indexterm>

  <indexterm>
   <primary>permission</primary>
   <see>privilege</see>
  </indexterm>

  <para>
   When you create a database object, you become its owner.  By
   default, only the owner of an object can do anything with the
   object. In order to allow other users to use it,
   <firstterm>privileges</firstterm> must be granted.  (There are also
   users that have the superuser privilege.  Those users can always
   access any object.)
  </para>

  <note>
   <para>
    To change the owner of a table, index, sequence, or view, use the
    <xref linkend="sql-altertable" endterm="sql-altertable-title">
    command.
   </para>
  </note>

  <para>
   There are several different privileges: <literal>SELECT</>,
   <literal>INSERT</>, <literal>UPDATE</>, <literal>DELETE</>,
   <literal>RULE</>, <literal>REFERENCES</>, <literal>TRIGGER</>,
   <literal>CREATE</>, <literal>TEMPORARY</>, <literal>EXECUTE</>,
   <literal>USAGE</>, and <literal>ALL PRIVILEGES</>. For complete
   information on the different types of privileges supported by
   <productname>PostgreSQL</productname>, refer to the
   <xref linkend="sql-grant" endterm="sql-grant-title">
   reference page.  The following sections
   and chapters will also show you how those privileges are used.
  </para>

  <para>
   The right to modify or destroy an object is always the privilege of
   the owner only.
  </para>

  <para>
   To assign privileges, the <command>GRANT</command> command is
   used. So, if <literal>joe</literal> is an existing user, and
   <literal>accounts</literal> is an existing table, the privilege to
   update the table can be granted with
<programlisting>
GRANT UPDATE ON accounts TO joe;
</programlisting>
   The user executing this command must be the owner of the table. To
   grant a privilege to a group, use
<programlisting>
GRANT SELECT ON accounts TO GROUP staff;
</programlisting>
   The special <quote>user</quote> name <literal>PUBLIC</literal> can
   be used to grant a privilege to every user on the system. Writing
   <literal>ALL</literal> in place of a specific privilege specifies that all
   privileges will be granted.
  </para>

  <para>
   To revoke a privilege, use the fittingly named
   <command>REVOKE</command> command:
<programlisting>
REVOKE ALL ON accounts FROM PUBLIC;
</programlisting>
   The special privileges of the table owner (i.e., the right to do
   <command>DROP</>, <command>GRANT</>, <command>REVOKE</>, etc.)
   are always implicit in being the owner,
   and cannot be granted or revoked.  But the table owner can choose
   to revoke his own ordinary privileges, for example to make a
   table read-only for himself as well as others.
  </para>
 </sect1>

 <sect1 id="ddl-schemas">
  <title>Schemas</title>

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

  <para>
   A <productname>PostgreSQL</productname> database cluster
   contains one or more named databases.  Users and groups of users are
   shared across the entire cluster, but no other data is shared across
   databases.  Any given client connection to the server can access
   only the data in a single database, the one specified in the connection
   request.
  </para>

  <note>
   <para>
    Users of a cluster do not necessarily have the privilege to access every
    database in the cluster.  Sharing of user names means that there
    cannot be different users named, say, <literal>joe</> in two databases
    in the same cluster; but the system can be configured to allow
    <literal>joe</> access to only some of the databases.
   </para>
  </note>

  <para>
   A database contains one or more named <firstterm>schemas</>, which
   in turn contain tables.  Schemas also contain other kinds of named
   objects, including data types, functions, and operators.  The same
   object name can be used in different schemas without conflict; for
   example, both <literal>schema1</> and <literal>myschema</> may
   contain tables named <literal>mytable</>.  Unlike databases,
   schemas are not rigidly separated: a user may access objects in any
   of the schemas in the database he is connected to, if he has
   privileges to do so.
  </para>

  <para>
   There are several reasons why one might want to use schemas:

   <itemizedlist>
    <listitem>
     <para>
      To allow many users to use one database without interfering with
      each other.
     </para>
    </listitem>

    <listitem>
     <para>
      To organize database objects into logical groups to make them
      more manageable.
     </para>
    </listitem>

    <listitem>
     <para>
      Third-party applications can be put into separate schemas so
      they cannot collide with the names of other objects.
     </para>
    </listitem>
   </itemizedlist>

   Schemas are analogous to directories at the operating system level,
   except that schemas cannot be nested.
  </para>

  <sect2 id="ddl-schemas-create">
   <title>Creating a Schema</title>

   <indexterm zone="ddl-schemas-create">
    <primary>schema</primary>
    <secondary>creating</secondary>
   </indexterm>

   <para>
    To create a separate schema, use the command <literal>CREATE
    SCHEMA</literal>.  Give the schema a name of your choice.  For
    example:
<programlisting>
CREATE SCHEMA myschema;
</programlisting>
   </para>

   <indexterm>
    <primary>qualified name</primary>
   </indexterm>

   <indexterm>
    <primary>name</primary>
    <secondary>qualified</secondary>
   </indexterm>

   <para>
    To create or access objects in a schema, write a
    <firstterm>qualified name</> consisting of the schema name and
    table name separated by a dot:
<synopsis>
<replaceable>schema</><literal>.</><replaceable>table</>
</synopsis>
    Actually, the even more general syntax
<synopsis>
<replaceable>database</><literal>.</><replaceable>schema</><literal>.</><replaceable>table</>
</synopsis>
    can be used too, but at present this is just for pro-forma compliance
    with the SQL standard; if you write a database name it must be the
    same as the database you are connected to.
   </para>

   <para>
    So to create a table in the new schema, use
<programlisting>
CREATE TABLE myschema.mytable (
 ...
);
</programlisting>
    This works anywhere a table name is expected, including the table
    modification commands and the data access commands discussed in
    the following chapters.
   </para>

   <indexterm>
    <primary>schema</primary>
    <secondary>removing</secondary>
   </indexterm>

   <para>
    To drop a schema if it's empty (all objects in it have been
    dropped), use
<programlisting>
DROP SCHEMA myschema;
</programlisting>
    To drop a schema including all contained objects, use
<programlisting>
DROP SCHEMA myschema CASCADE;
</programlisting>
    See <xref linkend="ddl-depend"> for a description of the general
    mechanism behind this.
   </para>

   <para>
    Often you will want to create a schema owned by someone else
    (since this is one of the ways to restrict the activities of your
    users to well-defined namespaces).  The syntax for that is:
<programlisting>
CREATE SCHEMA <replaceable>schemaname</replaceable> AUTHORIZATION <replaceable>username</replaceable>;
</programlisting>
    You can even omit the schema name, in which case the schema name
    will be the same as the user name.  See <xref
    linkend="ddl-schemas-patterns"> for how this can be useful.
   </para>

   <para>
    Schema names beginning with <literal>pg_</> are reserved for
    system purposes and may not be created by users.
   </para>
  </sect2>

  <sect2 id="ddl-schemas-public">
   <title>The Public Schema</title>

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

   <para>
    In the previous sections we created tables without specifying any
    schema names.  By default, such tables (and other objects) are
    automatically put into a schema named <quote>public</quote>.  Every new
    database contains such a schema.  Thus, the following are equivalent:
<programlisting>
CREATE TABLE products ( ... );
</programlisting>