File: sql-altertable.html

package info (click to toggle)
mpsql 2.1-2
links: PTS
area: non-free
in suites: potato
size: 3,528 kB
ctags: 4,886
sloc: ansic: 35,184; makefile: 3,761; sh: 44
file content (547 lines) | stat: -rw-r--r-- 8,661 bytes
<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.0 Transitional//EN">
<HTML
><HEAD
><TITLE
>ALTER TABLE</TITLE
><META
NAME="GENERATOR"
CONTENT="Modular DocBook HTML Stylesheet version 1.19"><LINK
REL="HOME"
TITLE="PostgreSQL User's Guide"
HREF="user.html"><LINK
REL="UP"
TITLE="SQL Commands"
HREF="sql-commands.html"><LINK
REL="PREVIOUS"
TITLE="ABORT"
HREF="sql-abort.html"><LINK
REL="NEXT"
TITLE="     ALTER USER
    "
HREF="sql-alteruser.html"></HEAD
><BODY
BGCOLOR="#FFFFFF"
TEXT="#000000"
><DIV
CLASS="NAVHEADER"
><TABLE
WIDTH="100%"
BORDER="0"
CELLPADDING="0"
CELLSPACING="0"
><TR
><TH
COLSPAN="3"
ALIGN="center"
>PostgreSQL User's Guide</TH
></TR
><TR
><TD
WIDTH="10%"
ALIGN="left"
VALIGN="bottom"
><A
HREF="sql-abort.html"
>Prev</A
></TD
><TD
WIDTH="80%"
ALIGN="center"
VALIGN="bottom"
></TD
><TD
WIDTH="10%"
ALIGN="right"
VALIGN="bottom"
><A
HREF="sql-alteruser.html"
>Next</A
></TD
></TR
></TABLE
><HR
ALIGN="LEFT"
WIDTH="100%"></DIV
><H1
>ALTER TABLE</H1
><DIV
CLASS="REFNAMEDIV"
><H2
>Name</H2
>ALTER TABLE &#8212; Modifies table properties</DIV
><DIV
CLASS="REFSYNOPSISDIV"
><PRE
CLASS="SYNOPSIS"
>ALTER TABLE <TT
CLASS="REPLACEABLE"
><I
>table</I
></TT
>
    [ * ] ADD [ COLUMN ] <TT
CLASS="REPLACEABLE"
><I
>column</I
></TT
> <TT
CLASS="REPLACEABLE"
><I
>type</I
></TT
>
ALTER TABLE <TT
CLASS="REPLACEABLE"
><I
>table</I
></TT
>
    [ * ] RENAME [ COLUMN ] <TT
CLASS="REPLACEABLE"
><I
>column</I
></TT
> TO <TT
CLASS="REPLACEABLE"
><I
>newcolumn</I
></TT
>
ALTER TABLE <TT
CLASS="REPLACEABLE"
><I
>table</I
></TT
>
    RENAME TO <TT
CLASS="REPLACEABLE"
><I
>newtable</I
></TT
></PRE
><DIV
CLASS="REFSECT2"
><H3
>Inputs</H3
><P
><P
></P
></P><DL
><DT
><TT
CLASS="REPLACEABLE"
><I
> table </I
></TT
></DT
><DD
><P
>          The name of an existing table to alter.</P
></DD
><DT
><TT
CLASS="REPLACEABLE"
><I
> column </I
></TT
></DT
><DD
><P
>          Name of a new or existing column.</P
></DD
><DT
><TT
CLASS="REPLACEABLE"
><I
> type </I
></TT
></DT
><DD
><P
>          Type of the new column.</P
></DD
><DT
><TT
CLASS="REPLACEABLE"
><I
> newcolumn </I
></TT
></DT
><DD
><P
>          New name for an existing column.</P
></DD
><DT
><TT
CLASS="REPLACEABLE"
><I
> newtable </I
></TT
></DT
><DD
><P
>          New name for an existing column.</P
></DD
></DL
><P>&#13;</P
></DIV
><DIV
CLASS="REFSECT2"
><H3
>Outputs</H3
><P
>&#13;<P
></P
></P><DL
><DT
><SPAN
CLASS="RETURNVALUE"
>ALTER</SPAN
></DT
><DD
><P
>          Message returned from column or table renaming.</P
></DD
><DT
><SPAN
CLASS="RETURNVALUE"
>NEW</SPAN
></DT
><DD
><P
>          Message returned from column addition.</P
></DD
><DT
><SPAN
CLASS="RETURNVALUE"
>ERROR</SPAN
></DT
><DD
><P
>          Message returned if table or column is not available.&#13;</P
></DD
></DL
><P>&#13;</P
></DIV
></DIV
><DIV
CLASS="REFSECT1"
><H2
>Description</H2
><P
>   <B
CLASS="COMMAND"
>ALTER TABLE</B
> changes the definition of an existing table.
   The new columns and their types are specified in the same style
   and with the the same restrictions as in <B
CLASS="COMMAND"
>CREATE TABLE</B
>.
   The RENAME clause causes the name of a table or column
   to change without changing any of the data contained in
   the affected table. Thus, the table or column will
   remain of the same type and size after this command is
   executed.</P
><P
>   You must own the table in order to change its schema.</P
><DIV
CLASS="REFSECT2"
><H3
>Notes</H3
><P
>The keyword COLUMN is noise and can be omitted.&#13;</P
><P
>&#8220;[*]&#8221; following a name of a table indicates that statement
     should be run over that table and all tables below it in the
     inheritance hierarchy.
    The <I
CLASS="CITETITLE"
>PostgreSQL User's Guide</I
> has further
     information on inheritance.&#13;</P
><P
>   Refer to CREATE TABLE for a further description
     of valid arguments.&#13;</P
></DIV
></DIV
><DIV
CLASS="REFSECT1"
><H2
>Usage</H2
><P
>   To add a column of type VARCHAR to a table:
<PRE
CLASS="PROGRAMLISTING"
>    ALTER TABLE distributors ADD COLUMN address VARCHAR(30);</PRE
>&#13;</P
><P
>   To rename an existing column:
<PRE
CLASS="PROGRAMLISTING"
>    ALTER TABLE distributors RENAME COLUMN address TO city;</PRE
>&#13;</P
><P
>   To rename an existing table:
<PRE
CLASS="PROGRAMLISTING"
>    ALTER TABLE distributors RENAME TO suppliers;</PRE
>&#13;</P
></DIV
><DIV
CLASS="REFSECT1"
><H2
>Compatibility</H2
><DIV
CLASS="REFSECT2"
><H3
>SQL92</H3
><P
><B
CLASS="COMMAND"
>ALTER TABLE/RENAME</B
>
 is a <SPAN
CLASS="PRODUCTNAME"
>Postgres</SPAN
> language extension.&#13;</P
><P
>   SQL92 specifies some additional capabilities for <B
CLASS="COMMAND"
>ALTER TABLE</B
>
   statement which are not yet directly supported by
 <SPAN
CLASS="PRODUCTNAME"
>Postgres</SPAN
>:

<P
></P
></P><DL
><DT
><PRE
CLASS="SYNOPSIS"
>ALTER TABLE <TT
CLASS="REPLACEABLE"
><I
>table</I
></TT
> ALTER [ COLUMN ] <TT
CLASS="REPLACEABLE"
><I
>column</I
></TT
>
    SET DEFAULT <TT
CLASS="REPLACEABLE"
><I
>default</I
></TT
>

ALTER TABLE <TT
CLASS="REPLACEABLE"
><I
>table</I
></TT
> ALTER [ COLUMN ] <TT
CLASS="REPLACEABLE"
><I
>column</I
></TT
>
    ADD [ CONSTRAINT <TT
CLASS="REPLACEABLE"
><I
>constraint</I
></TT
> ] <TT
CLASS="REPLACEABLE"
><I
>table-constraint</I
></TT
></PRE
></DT
><DD
><P
>          Puts the default value or constraint specified into the
          definition of column in the table.
 See <B
CLASS="COMMAND"
>CREATE TABLE</B
> for the
          syntax of the default and table-constraint clauses.
          If a default clause already exists, it will be replaced by
          the new definition. If any constraints on this column already
          exist, they will be retained using a boolean AND with the new
          constraint.&#13;</P
><P
>Currently, to set new default constraints on an existing column
          the table must be recreated and reloaded:

<PRE
CLASS="PROGRAMLISTING"
>CREATE TABLE temp AS SELECT * FROM distributors;
DROP TABLE distributors;
CREATE TABLE distributors (
    did      DECIMAL(3) DEFAULT 1,
    name     VARCHAR(40) NOT NULL,
    city     VARCHAR(30)
);
INSERT INTO distributors SELECT * FROM temp;
DROP TABLE temp;</PRE
></P
></DD
><DT
><PRE
CLASS="SYNOPSIS"
>ALTER TABLE <TT
CLASS="REPLACEABLE"
><I
>table</I
></TT
>
    DROP DEFAULT <TT
CLASS="REPLACEABLE"
><I
>default</I
></TT
>
ALTER TABLE <TT
CLASS="REPLACEABLE"
><I
>table</I
></TT
>
    DROP CONSTRAINT <TT
CLASS="REPLACEABLE"
><I
>constraint</I
></TT
> { RESTRICT | CASCADE }</PRE
></DT
><DD
><P
>          Removes the default value specified by default or the rule
          specified by constraint from the definition of a table. 
          If RESTRICT is specified only a constraint with no dependent
          constraints can be destroyed.
          If CASCADE is specified, Any constraints that are dependent on
          this constraint are also dropped.&#13;</P
><P
>Currently, to remove a default value or constraints on an
          existing column the table must be recreated and reloaded:

<PRE
CLASS="PROGRAMLISTING"
>CREATE TABLE temp AS SELECT * FROM distributors;
DROP TABLE distributors;
CREATE TABLE distributors AS SELECT * FROM temp;
DROP TABLE temp;</PRE
>
   </P
></DD
><DT
><PRE
CLASS="SYNOPSIS"
>ALTER TABLE <TT
CLASS="REPLACEABLE"
><I
>table</I
></TT
>
    DROP [ COLUMN ] <TT
CLASS="REPLACEABLE"
><I
>column</I
></TT
> { RESTRICT | CASCADE }</PRE
></DT
><DD
><P
>          Removes a column from a table.
          If RESTRICT is specified only a column with no dependent
          objects can be destroyed.
          If CASCADE is specified, all objects that are dependent on
          this column are also dropped.&#13;</P
><P
>Currently, to remove an existing column the table must be
          recreated and reloaded:

<PRE
CLASS="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;</PRE
></P
></DD
></DL
><P></P
></DIV
></DIV
><DIV
CLASS="NAVFOOTER"
><HR
ALIGN="LEFT"
WIDTH="100%"><TABLE
WIDTH="100%"
BORDER="0"
CELLPADDING="0"
CELLSPACING="0"
><TR
><TD
WIDTH="33%"
ALIGN="left"
VALIGN="top"
><A
HREF="sql-abort.html"
>Prev</A
></TD
><TD
WIDTH="34%"
ALIGN="center"
VALIGN="top"
><A
HREF="user.html"
>Home</A
></TD
><TD
WIDTH="33%"
ALIGN="right"
VALIGN="top"
><A
HREF="sql-alteruser.html"
>Next</A
></TD
></TR
><TR
><TD
WIDTH="33%"
ALIGN="left"
VALIGN="top"
>ABORT</TD
><TD
WIDTH="34%"
ALIGN="center"
VALIGN="top"
><A
HREF="sql-commands.html"
>Up</A
></TD
><TD
WIDTH="33%"
ALIGN="right"
VALIGN="top"
>ALTER USER</TD
></TR
></TABLE
></DIV
></BODY
></HTML
>