-
Bruce Momjian authoredBruce Momjian authored
alter_table.sgml 11.20 KiB
<!--
$Header: /cvsroot/pgsql/doc/src/sgml/ref/alter_table.sgml,v 1.33 2001/11/22 03:42:32 momjian Exp $
Postgres documentation
-->
<refentry id="SQL-ALTERTABLE">
<refmeta>
<refentrytitle id="sql-altertable-title">
ALTER TABLE
</refentrytitle>
<refmiscinfo>SQL - Language Statements</refmiscinfo>
</refmeta>
<refnamediv>
<refname>
ALTER TABLE
</refname>
<refpurpose>
change the definition of a table
</refpurpose>
</refnamediv>
<refsynopsisdiv>
<refsynopsisdivinfo>
<date>1999-07-20</date>
</refsynopsisdivinfo>
<synopsis>
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 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> [ * ]
RENAME [ COLUMN ] <replaceable class="PARAMETER">column</replaceable> TO <replaceable
class="PARAMETER">newcolumn</replaceable>
ALTER TABLE <replaceable class="PARAMETER">table</replaceable>
RENAME TO <replaceable class="PARAMETER">newtable</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 }
ALTER TABLE <replaceable class="PARAMETER">table</replaceable>
OWNER TO <replaceable class="PARAMETER">new owner</replaceable>
</synopsis>
<refsect2 id="R2-SQL-ALTERTABLE-1">
<refsect2info>
<date>1998-04-15</date>
</refsect2info>
<title>
Inputs
</title>
<para>
<variablelist>
<varlistentry>
<term><replaceable class="PARAMETER"> table </replaceable></term>
<listitem>
<para>
The name of an existing table to alter.
</para>
</listitem>
</varlistentry>
<varlistentry>
<term><replaceable class="PARAMETER"> column </replaceable></term>
<listitem>
<para>
Name of a new or existing column.
</para>
</listitem> </varlistentry>
<varlistentry>
<term><replaceable class="PARAMETER"> type </replaceable></term>
<listitem>
<para>
Type of the new column.
</para>
</listitem>
</varlistentry>
<varlistentry>
<term><replaceable class="PARAMETER"> newcolumn </replaceable></term>
<listitem>
<para>
New name for an existing column.
</para>
</listitem>
</varlistentry>
<varlistentry>
<term><replaceable class="PARAMETER"> newtable </replaceable></term>
<listitem>
<para>
New name for the table.
</para>
</listitem>
</varlistentry>
<varlistentry>
<term><replaceable class="PARAMETER"> table constraint definition </replaceable></term>
<listitem>
<para>
New table constraint for the table
</para>
</listitem>
</varlistentry>
<varlistentry>
<term><replaceable class="PARAMETER">New user </replaceable></term>
<listitem>
<para>
The user name of the new owner of the table.
</para>
</listitem>
</varlistentry>
</variablelist>
</para>
</refsect2>
<refsect2 id="R2-SQL-ALTERTABLE-2">
<refsect2info>
<date>1998-04-15</date>
</refsect2info>
<title>
Outputs
</title>
<para>
<variablelist>
<varlistentry>
<term><computeroutput>ALTER</computeroutput></term>
<listitem>
<para>
Message returned from column or table renaming.
</para>
</listitem>
</varlistentry>
<varlistentry>
<term><computeroutput>ERROR</computeroutput></term>
<listitem>
<para>
Message returned if table or column is not available.
</para>
</listitem>
</varlistentry>
</variablelist>
</para>
</refsect2>
</refsynopsisdiv>
<refsect1 id="R1-SQL-ALTERTABLE-1">
<refsect1info>
<date>1998-04-15</date>
</refsect1info>
<title>
Description
</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 STATISTICS</literal> form allows you to
set the statistics-gathering target for subsequent
<xref linkend="sql-analyze" endterm="sql-analyze-title"> operations.
The <literal>RENAME</literal> clause causes the name of a table,
column, index, or sequence 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 to the user <replaceable class="PARAMETER">
new user</replaceable>.
</para>
<para>
You must own the table in order to change its schema.
</para>
<refsect2 id="R2-SQL-ALTERTABLE-3">
<refsect2info>
<date>1998-04-15</date>
</refsect2info>
<title>
Notes
</title>
<para>
The keyword <literal>COLUMN</literal> is noise and can be omitted.
</para>
<para>
In the current implementation of <literal>ADD COLUMN</literal>,
default and NOT NULL clauses for the new column are not supported.
You can use the <literal>SET DEFAULT</literal> form
of <command>ALTER TABLE</command> to set the default later.
(You may also want to update the already existing rows to the
new default value, using <xref linkend="sql-update" endterm="sql-update-title">.)
</para>
<para>
In DROP CONSTRAINT, the RESTRICT keyword is required, although dependencies are not yet checked. The CASCADE option is unsupported.
Currently DROP CONSTRAINT drops only 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
and reload the table, using other parameters to the
<xref linkend="SQL-CREATETABLE" endterm="sql-createtable-title"> command.
</para>
<para>
For example, to drop all constraints on a table <literal>distributors</literal>:
<programlisting>
CREATE TABLE temp AS SELECT * FROM distributors;
DROP TABLE distributors;
CREATE TABLE distributors AS SELECT * FROM temp;
DROP TABLE temp;
</programlisting>
</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.
</para>
</refsect2>
</refsect1>
<refsect1 id="R1-SQL-ALTERTABLE-2">
<title>
Usage
</title>
<para>
To add a column of type VARCHAR to a table:
<programlisting>
ALTER TABLE distributors ADD COLUMN address VARCHAR(30);
</programlisting>
</para>
<para>
To rename an existing column:
<programlisting>
ALTER TABLE distributors RENAME COLUMN address TO city;
</programlisting>
</para>
<para>
To rename an existing table:
<programlisting>
ALTER TABLE distributors RENAME TO suppliers;
</programlisting>
</para>
<para>
To add a check constraint to a table:
<programlisting>
ALTER TABLE distributors ADD CONSTRAINT zipchk CHECK (char_length(zipcode) = 5);
</programlisting>
</para>
<para>
To remove a check constraint from a table and all its children:
<programlisting>
ALTER TABLE distributors DROP CONSTRAINT zipchk RESTRICT;
</programlisting> </para>
<para>
To add a foreign key constraint to a table:
<programlisting>
ALTER TABLE distributors ADD CONSTRAINT distfk FOREIGN KEY (address) REFERENCES addresses(address) MATCH FULL;
</programlisting>
</para>
<para>
To add a (multi-column) unique constraint to a table:
<programlisting>
ALTER TABLE distributors ADD CONSTRAINT dist_id_zipcode_key UNIQUE (dist_id, zipcode);
</programlisting>
</para>
</refsect1>
<refsect1 id="R1-SQL-ALTERTABLE-3">
<title>
Compatibility
</title>
<refsect2 id="R2-SQL-ALTERTABLE-4">
<refsect2info>
<date>1998-04-15</date>
</refsect2info>
<title>SQL92</title>
<para>
The <literal>ADD COLUMN</literal> form is compliant with the exception that
it does not support defaults and NOT NULL constraints, as explained above.
The <literal>ALTER COLUMN</literal> form is in full compliance.
</para>
<para>
SQL92 specifies some additional capabilities for <command>ALTER TABLE</command>
statement which are not yet directly supported by <productname>Postgres</productname>:
<variablelist>
<varlistentry>
<term>
<synopsis>
ALTER TABLE <replaceable class="PARAMETER">table</replaceable> DROP [ COLUMN ] <replaceable class="PARAMETER">column</replaceable> { RESTRICT | CASCADE }
</synopsis>
</term>
<listitem>
<para>
Removes a column from a table.
Currently, to remove an existing column the table must be
recreated and reloaded:
<programlisting>
CREATE TABLE temp AS SELECT did, city FROM distributors;
DROP TABLE distributors;
CREATE TABLE distributors (
did DECIMAL(3) DEFAULT 1,
name VARCHAR(40) NOT NULL
);
INSERT INTO distributors SELECT * FROM temp;
DROP TABLE temp;
</programlisting>
</para>
</listitem>
</varlistentry>
</variablelist>
</para>
<para>
The clauses to rename tables, columns, indexes, and sequences are
<productname>Postgres</productname> extensions from SQL92.
</para>
</refsect2>
</refsect1>
</refentry>
<!-- Keep this comment at the end of the file
Local variables:
mode: sgml
sgml-omittag:nil
sgml-shorttag:t
sgml-minimize-attributes:nil
sgml-always-quote-attributes:t
sgml-indent-step:1
sgml-indent-data:t
sgml-parent-document:nil
sgml-default-dtd-file:"../reference.ced"
sgml-exposed-tags:nil
sgml-local-catalogs:"/usr/lib/sgml/catalog"
sgml-local-ecat-files:nil
End:
-->