<!--

$Header: /cvsroot/pgsql/doc/src/sgml/ref/alter_database.sgml,v 1.1 2002/03/01 22:45:07 petere Exp $

$Header: /cvsroot/pgsql/doc/src/sgml/ref/alter_database.sgml,v 1.2 2002/04/23 02:07:15 tgl Exp $

PostgreSQL documentation

-->

<command>ALTER DATABASE</command> is used to change the session

default of a run-time configuration variable for a

<productname>PostgreSQL</productname> database. Whenever a new

session is subsequently started, <literal>SET

session is subsequently started in that database, <literal>SET

<replaceable>variable</replaceable> TO

<replaceable>value</replaceable></literal> is effectively executed

before the start of the session.

before the start of the session. The database-specific default

overrides whatever setting is present in <filename>postgresql.conf</>

or has been received from the postmaster.

</para>

<para>

Only a database owner can change the session defaults for a

database. Superusers can change the session defaults of any

Only a superuser or the database owner can change the session defaults for a

database.

</para>

<para>

Using <xref linkend="sql-alteruser" endterm="sql-alteruser-title">,

it is also possible to tie a session default to a specific user

rather than a database.

rather than a database. User-specific settings override database-specific

ones if there is a conflict.

</para>

</refsect1>

<!--

$Header: /cvsroot/pgsql/doc/src/sgml/ref/alter_table.sgml,v 1.42 2002/04/21 19:02:39 thomas Exp $

$Header: /cvsroot/pgsql/doc/src/sgml/ref/alter_table.sgml,v 1.43 2002/04/2302:07:15 tgl Exp $

PostgreSQL documentation

-->

ALTER TABLE [ ONLY ] <replaceable class="PARAMETER">table</replaceable> [ * ]

ADD [ COLUMN ] <replaceable class="PARAMETER">column</replaceable> <replaceable class="PARAMETER">type</replaceable> [ <replaceable class="PARAMETER">column_constraint</replaceable> [ ... ] ]

ALTER TABLE [ ONLY ] <replaceable class="PARAMETER">table</replaceable> [ * ]

ALTER [ COLUMN ] <replaceable class="PARAMETER">column</replaceable> { SET DEFAULT <replaceable

class="PARAMETER">value</replaceable> | DROP DEFAULT }

ALTER [ COLUMN ] <replaceable class="PARAMETER">column</replaceable> { SET DEFAULT <replaceable class="PARAMETER">value</replaceable> | DROP DEFAULT }

ALTER TABLE [ ONLY ] <replaceable class="PARAMETER">table</replaceable> [ * ]

ALTER [ COLUMN ] <replaceable class="PARAMETER">column</replaceable> { SET | DROP } NOT NULL

ALTER TABLE [ ONLY ] <replaceable class="PARAMETER">table</replaceable> [ * ]

ALTER [ COLUMN ] <replaceable class="PARAMETER">column</replaceable> SET STATISTICS <replaceable class="PARAMETER">integer</replaceable>

ALTER TABLE [ ONLY ] <replaceable class="PARAMETER">table</replaceable> [ * ]

ALTER [ COLUMN ] <replaceable class="PARAMETER">column</replaceable> SET STORAGE {PLAIN | EXTERNAL | EXTENDED | MAIN}

ALTER [ COLUMN ] <replaceable class="PARAMETER">column</replaceable> SET STORAGE {PLAIN | EXTERNAL | EXTENDED | MAIN}

ALTER TABLE [ ONLY ] <replaceable class="PARAMETER">table</replaceable> [ * ]

RENAME [ COLUMN ] <replaceable class="PARAMETER">column</replaceable> TO <replaceable

class="PARAMETER">newcolumn</replaceable>

class="PARAMETER">new_column</replaceable>

ALTER TABLE <replaceable class="PARAMETER">table</replaceable>

RENAME TO <replaceable class="PARAMETER">new_table</replaceable>

ALTER TABLE <replaceable class="PARAMETER">table</replaceable>

ADD <replaceable class="PARAMETER">table_constraint_definition</replaceable>

ALTER TABLE [ ONLY ] <replaceable class="PARAMETER">table</replaceable>

DROP CONSTRAINT <replaceable class="PARAMETER">constraint</replaceable> { RESTRICT | CASCADE }

DROP CONSTRAINT <replaceable class="PARAMETER">constraint_name</replaceable> { RESTRICT | CASCADE }

ALTER TABLE <replaceable class="PARAMETER">table</replaceable>

OWNER TO <replaceable class="PARAMETER">new_owner</replaceable>

</synopsis>

<term><replaceable class="PARAMETER"> table </replaceable></term>

<listitem>

<para>

The name of an existing table to alter.

The name(possibly schema-qualified)of an existing table to alter.

</para>

</listitem>

</varlistentry>

</varlistentry>

<varlistentry>

<term><replaceable class="PARAMETER">newcolumn </replaceable></term>

<term><replaceable class="PARAMETER">new_column </replaceable></term>

<listitem>

<para>

New name for an existing column.

<term><replaceable class="PARAMETER"> table_constraint_definition </replaceable></term>

<listitem>

<para>

New table constraint for the table

New table constraint for the table.

</para>

</listitem>

</varlistentry>

<varlistentry>

<term><replaceable class="PARAMETER"> constraint_name </replaceable></term>

<listitem>

<para>

Name of an existing constraint to drop.

</para>

</listitem>

</varlistentry>

</title>

<para>

<command>ALTER TABLE</command> changes the definition of an existing table.

The <literal>ADD COLUMN</literal> form adds a new column to the table

using the same syntax as <xref linkend="SQL-CREATETABLE" endterm="SQL-CREATETABLE-TITLE">.

The <literal>ALTER COLUMN SET/DROP DEFAULT</literal> forms

allow you to set or remove the default for the column. Note that defaults

only apply to subsequent <command>INSERT</command> commands; they do not

cause rows already in the table to change.

The <literal>ALTER COLUMN SET/DROP NOT NULL</literal> forms allow you to

change whether a column is marked to allow NULL values or to reject NULL

values.

The <literal>ALTER COLUMN SET STATISTICS</literal> form allows you to

set the statistics-gathering target for subsequent

<xref linkend="sql-analyze" endterm="sql-analyze-title"> operations.

The <literal>ALTER COLUMN SET STORAGE</literal> form allows the

column storage mode to be set. This controls whether this column is

held inline or in a supplementary table, and whether the data

should be compressed or not. <literal>PLAIN</literal> must be used

for fixed-length values such as <literal>INTEGER</literal> 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. The use of <literal>EXTERNAL</literal> will make

substring operations on a column faster, at the penalty of

increased storage space.

The <literal>RENAME</literal> clause causes the name of a table,

column, index, sequence or view to change without changing any of the

data. The data will remain of the same type and size after the

command is executed.

The ADD <replaceable class="PARAMETER">table_constraint_definition</replaceable> clause

adds a new constraint to the table using the same syntax as <xref

linkend="SQL-CREATETABLE" endterm="SQL-CREATETABLE-TITLE">.

The DROP CONSTRAINT <replaceable class="PARAMETER">constraint</replaceable> clause

drops all constraints on the table (and its children) that match <replaceable class="PARAMETER">constraint</replaceable>.

The OWNER clause changes the owner of the table, index, sequence or view to the

user <replaceable class="PARAMETER">new user</replaceable>.

There are several sub-forms:

</para>

<variablelist>

<varlistentry>

<term>ADD COLUMN</term>

<listitem>

<para>

This form adds a new column to the table using the same syntax as

<xref linkend="SQL-CREATETABLE" endterm="SQL-CREATETABLE-TITLE">.

</para>

</listitem>

</varlistentry>

<varlistentry>

<term>SET/DROP DEFAULT</term>

<listitem>

<para>

These forms set or remove the default value for a column. Note

that defaults only apply to subsequent <command>INSERT</command>

commands; they do not cause rows already in the table to change.

Defaults may also be created for views, in which case they are

inserted into <command>INSERT</> statements on the view before

the view's ON INSERT rule is applied.

</para>

</listitem>

</varlistentry>

<varlistentry>

<term>SET/DROP NOT NULL</term>

<listitem>

<para>

These forms change whether a column is marked to allow NULL

values or to reject NULL values. You may only <literal>SET NOT NULL</>

when the table contains no NULLs in the column.

</para>

</listitem>

</varlistentry>

<varlistentry>

<term>SET STATISTICS</term>

<listitem>

<para>

This form

sets the per-column statistics-gathering target for subsequent

<xref linkend="sql-analyze" endterm="sql-analyze-title"> operations.

</para>

</listitem>

</varlistentry>

<varlistentry>

<term>SET STORAGE</term>

<listitem>

<para>

This form sets the storage mode for a column. This controls whether this

column is held inline or in a supplementary table, and whether the data

should be compressed or not. <literal>PLAIN</literal> must be used

for fixed-length values such as <literal>INTEGER</literal> 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. <literal>EXTENDED</literal> is the default for all

datatypes that support it. The use of <literal>EXTERNAL</literal> will

make substring operations on a TEXT column faster, at the penalty of

increased storage space.

</para>

</listitem>

</varlistentry>

<varlistentry>

<term>RENAME</term>

<listitem>

<para>

The <literal>RENAME</literal> forms change the name of a table

(or an index, sequence, or view) or the name of an individual column in

a table. There is no effect on the stored data.

</para>

</listitem>

</varlistentry>

<varlistentry>

<term>ADD <replaceable class="PARAMETER">table_constraint_definition</replaceable></term>

<listitem>

<para>

This form adds a new constraint to a table using the same syntax as

<xref linkend="SQL-CREATETABLE" endterm="SQL-CREATETABLE-TITLE">.

</para>

</listitem>

</varlistentry>

<varlistentry>

<term>DROP CONSTRAINT</term>

<listitem>

<para>

This form drops constraints on a table (and its children).

Currently, constraints on tables are not required to have unique

names, so there may be more than one constraint matching the specified

name. All such constraints will be dropped.

</para>

</listitem>

</varlistentry>

<varlistentry>

<term>OWNER</term>

<listitem>

<para>

This form changes the owner of the table, index, sequence or view to the

specified user.

</para>

</listitem>

</varlistentry>

</variablelist>

<para>

You must own the table in order to change its schema.

You must own the table to use <command>ALTER TABLE</>; except for

<command>ALTER TABLE OWNER</>, which may only be executed by a superuser.

</para>

<refsect2 id="R2-SQL-ALTERTABLE-3">

<para>

In the current implementation of <literal>ADD COLUMN</literal>,

default and NOT NULL clauses for the new column are not supported.

The new column always comes into being with all values NULL.

You can use the <literal>SET DEFAULT</literal> form

of <command>ALTER TABLE</command> to set the defaultlater.

of <command>ALTER TABLE</command> to set the defaultafterwards.

(You may also want to update the already existing rows to the

new default value, using <xref linkend="sql-update" endterm="sql-update-title">.)

new default value, using

<xref linkend="sql-update" endterm="sql-update-title">.)

If you want to mark the column non-null, use the <literal>SET NOT NULL</>

form after you've entered non-null values for the column in all rows.

</para>

<para>

In DROP CONSTRAINT, the RESTRICT keyword is required, although

dependencies are not yet checked. The CASCADE option is unsupported.

Currently DROP CONSTRAINTdropsonly CHECK constraints.

Currently DROP CONSTRAINT only handles CHECK constraints.

To remove a PRIMARY or UNIQUE constraint, drop the

relevant index using the <xref linkend="SQL-DROPINDEX" endterm="sql-dropindex-title"> command.

To remove FOREIGN KEY constraints you need to recreate

</para>

<para>

You must own the table in order to change it.

Changing any part of the schema of a system

catalog is not permitted.

The <citetitle>PostgreSQL User's Guide</citetitle> has further

information on inheritance.

</para>

<para>

Refer to <command>CREATE TABLE</command> for a further description

of valid arguments.

The <citetitle>PostgreSQL User's Guide</citetitle> has further

information on inheritance.

</para>

</refsect2>

</refsect1>

<!--

$Header: /cvsroot/pgsql/doc/src/sgml/ref/analyze.sgml,v 1.7 2002/04/21 19:02:39 thomas Exp $

$Header: /cvsroot/pgsql/doc/src/sgml/ref/analyze.sgml,v 1.8 2002/04/2302:07:15 tgl Exp $

PostgreSQL documentation

-->

<term><replaceable class="PARAMETER">table</replaceable></term>

<listitem>

<para>

The name of a specific table to analyze. Defaults to all tables.

The name (possibly schema-qualified) of a specific table to

analyze. Defaults to all tables in the current database.

</para>

</listitem>

</varlistentry>

<!--

$Header: /cvsroot/pgsql/doc/src/sgml/ref/cluster.sgml,v 1.15 2002/04/21 19:02:39 thomas Exp $

$Header: /cvsroot/pgsql/doc/src/sgml/ref/cluster.sgml,v 1.16 2002/04/2302:07:15 tgl Exp $

PostgreSQL documentation

-->

<term><replaceable class="PARAMETER">table</replaceable></term>

<listitem>

<para>

The name of a table.

The name(possibly schema-qualified)of a table.

</para>

</listitem>

</varlistentry>

<!--

$Header: /cvsroot/pgsql/doc/src/sgml/ref/comment.sgml,v 1.17 2002/04/21 19:02:39 thomas Exp $

$Header: /cvsroot/pgsql/doc/src/sgml/ref/comment.sgml,v 1.18 2002/04/2302:07:15 tgl Exp $

PostgreSQL documentation

-->

<variablelist>

<varlistentry>

<term><replaceable class="PARAMETER">object_name, table_name,

column_name, agg_name, func_name, op, rule_name, trigger_name</replaceable></term>

<term><replaceable class="PARAMETER">object_name,

table_name.column_name, agg_name, func_name, op, rule_name, trigger_name</replaceable></term>

<listitem>

<para>

The name of the object to be be commented.

The name of the object to be be commented. Names of tables,

indexes, sequences, views, types, domains, functions, aggregates,

and operators may be schema-qualified.

</para>

</listitem>

</varlistentry>

<!--

$Header: /cvsroot/pgsql/doc/src/sgml/ref/copy.sgml,v 1.29 2002/03/22 19:20:37 petere Exp $

$Header: /cvsroot/pgsql/doc/src/sgml/ref/copy.sgml,v 1.30 2002/04/23 02:07:15 tgl Exp $

PostgreSQL documentation

-->

<term><replaceable class="parameter">table</replaceable></term>

<listitem>

<para>

The name of an existing table.

The name(possibly schema-qualified)of an existing table.

</para>

</listitem>

</varlistentry>