| <!-- |
| $PostgreSQL: pgsql/doc/src/sgml/ref/alter_table.sgml,v 1.92 2006/10/23 18:10:32 petere Exp $ |
| PostgreSQL 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> |
| |
| <indexterm zone="sql-altertable"> |
| <primary>ALTER TABLE</primary> |
| </indexterm> |
| |
| <refsynopsisdiv> |
| <synopsis> |
| ALTER TABLE [ONLY] name RENAME [COLUMN] column TO new_column |
| |
| ALTER TABLE name RENAME TO new_name |
| |
| ALTER TABLE name SET SCHEMA new_schema |
| |
| ALTER TABLE [ONLY] name SET |
| DISTRIBUTED BY (column, [ ... ] ) |
| | DISTRIBUTED RANDOMLY |
| | WITH (REORGANIZE=true|false) |
| | WITH (storage_parameter=value [, ... ]) |
| |
| ALTER TABLE [ONLY] name action [, ... ] |
| |
| ALTER TABLE name |
| [ ALTER PARTITION { partition_name | FOR (RANK(number)) |
| | FOR (value) } partition_action [...] ] |
| partition_action |
| |
| where action is one of: |
| ADD [COLUMN] column_name type |
| [ ENCODING ( storage_directive [,...] ) ] |
| [column_constraint [ ... ]] |
| DROP [COLUMN] column [RESTRICT | CASCADE] |
| ALTER [COLUMN] column TYPE type [USING expression] |
| ALTER [COLUMN] column SET DEFAULT expression |
| ALTER [COLUMN] column DROP DEFAULT |
| ALTER [COLUMN] column { SET | DROP } NOT NULL |
| ALTER [COLUMN] column SET STATISTICS integer |
| ADD table_constraint |
| DROP CONSTRAINT constraint_name [RESTRICT | CASCADE] |
| DISABLE TRIGGER [trigger_name | ALL | USER] |
| ENABLE TRIGGER [trigger_name | ALL | USER] |
| CLUSTER ON index_name |
| SET WITHOUT CLUSTER |
| SET WITHOUT OIDS |
| SET (FILLFACTOR = value) |
| RESET (FILLFACTOR) |
| INHERIT parent_table |
| NO INHERIT parent_table |
| OWNER TO new_owner |
| SET TABLESPACE new_tablespace |
| |
| where partition_action is one of: |
| ALTER DEFAULT PARTITION |
| DROP DEFAULT PARTITION [IF EXISTS] |
| DROP PARTITION [IF EXISTS] { partition_name | |
| FOR (RANK(number)) | FOR (value) } [CASCADE] |
| TRUNCATE DEFAULT PARTITION |
| TRUNCATE PARTITION { partition_name | FOR (RANK(number)) | |
| FOR (value) } |
| RENAME DEFAULT PARTITION TO new_partition_name |
| RENAME PARTITION { partition_name | FOR (RANK(number)) | |
| FOR (value) } TO new_partition_name |
| ADD DEFAULT PARTITION name [ ( subpartition_spec ) ] |
| ADD PARTITION [name] partition_element |
| [ ( subpartition_spec ) ] |
| EXCHANGE PARTITION { partition_name | FOR (RANK(number)) | |
| FOR (value) } WITH TABLE table_name |
| [ WITH | WITHOUT VALIDATION ] |
| EXCHANGE DEFAULT PARTITION WITH TABLE table_name |
| [ WITH | WITHOUT VALIDATION ] |
| SET SUBPARTITION TEMPLATE (subpartition_spec) |
| SPLIT DEFAULT PARTITION |
| { AT (list_value) |
| | START([datatype] range_value) [INCLUSIVE | EXCLUSIVE] |
| END([datatype] range_value) [INCLUSIVE | EXCLUSIVE] } |
| [ INTO ( PARTITION new_partition_name, |
| PARTITION default_partition_name ) ] |
| SPLIT PARTITION { partition_name | FOR (RANK(number)) | |
| FOR (value) } AT (value) |
| [ INTO (PARTITION partition_name, PARTITION partition_name)] |
| |
| where partition_element is: |
| VALUES (list_value [,...] ) |
| | START ([datatype] 'start_value') [INCLUSIVE | EXCLUSIVE] |
| [ END ([datatype] 'end_value') [INCLUSIVE | EXCLUSIVE] ] |
| | END ([datatype] 'end_value') [INCLUSIVE | EXCLUSIVE] |
| [ WITH ( partition_storage_parameter=value [, ... ] ) ] |
| [ TABLESPACE tablespace ] |
| |
| where subpartition_spec is: |
| subpartition_element [, ...] |
| and subpartition_element is: |
| DEFAULT SUBPARTITION subpartition_name |
| | [SUBPARTITION subpartition_name] VALUES (list_value [,...] ) |
| | [SUBPARTITION subpartition_name] |
| START ([datatype] 'start_value') [INCLUSIVE | EXCLUSIVE] |
| [ END ([datatype] 'end_value') [INCLUSIVE | EXCLUSIVE] ] |
| [ EVERY ( [number | datatype] 'interval_value') ] |
| | [SUBPARTITION subpartition_name] |
| END ([datatype] 'end_value') [INCLUSIVE | EXCLUSIVE] |
| [ EVERY ( [number | datatype] 'interval_value') ] |
| [ WITH ( partition_storage_parameter=value [, ... ] ) ] |
| [ TABLESPACE tablespace ] |
| |
| where storage_parameter is: |
| APPENDONLY={TRUE|FALSE} |
| BLOCKSIZE={8192-2097152} |
| ORIENTATION={PARQUET|ROW} |
| COMPRESSTYPE={ZLIB|SNAPPY|GZIP|NONE} |
| COMPRESSLEVEL={0-9} |
| FILLFACTOR={10-100} |
| OIDS[=TRUE|FALSE] |
| |
| where storage_directive is: |
| COMPRESSTYPE={ZLIB|SNAPPY|GZIP|NONE} |
| | COMPRESSLEVEL={0-9} |
| | BLOCKSIZE={8192-2097152} |
| |
| Where column_reference_storage_directive is: |
| |
| COLUMN column_name ENCODING ( storage_directive [, ... ] ), ... |
| | DEFAULT COLUMN ENCODING ( storage_directive [, ... ] ) |
| |
| </synopsis> |
| </refsynopsisdiv> |
| |
| <refsect1> |
| <title>Description</title> |
| |
| <para> |
| <command>ALTER TABLE</command> changes the definition of an existing table. |
| There are several subforms: |
| |
| <variablelist> |
| <varlistentry> |
| <term><literal>ADD COLUMN</literal></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><literal>DROP COLUMN</literal></term> |
| <listitem> |
| <para> |
| This form drops a column from a table. Indexes and |
| table constraints involving the column will be automatically |
| dropped as well. You will need to say <literal>CASCADE</> if |
| anything outside the table depends on the column, for example, |
| foreign key references or views. |
| </para> |
| </listitem> |
| </varlistentry> |
| |
| <varlistentry> |
| <term><literal>ALTER COLUMN TYPE</literal></term> |
| <listitem> |
| <para> |
| This form changes the type of a column of a table. Indexes and |
| simple table constraints involving the column will be automatically |
| converted to use the new column type by reparsing the originally |
| supplied expression. The optional <literal>USING</literal> |
| clause specifies how to compute the new column value from the old; |
| if omitted, the default conversion is the same as an assignment |
| cast from old data type to new. A <literal>USING</literal> |
| clause must be provided if there is no implicit or assignment |
| cast from old to new type. |
| </para> |
| </listitem> |
| </varlistentry> |
| |
| <varlistentry> |
| <term><literal>SET</literal>/<literal>DROP DEFAULT</literal></term> |
| <listitem> |
| <para> |
| These forms set or remove the default value for a column. |
| The default values 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 <literal>ON INSERT</literal> rule is applied. |
| </para> |
| </listitem> |
| </varlistentry> |
| |
| <varlistentry> |
| <term><literal>SET</literal>/<literal>DROP NOT NULL</literal></term> |
| <listitem> |
| <para> |
| These forms change whether a column is marked to allow null |
| values or to reject null values. You can only use <literal>SET |
| NOT NULL</> when the column contains no null values. |
| </para> |
| </listitem> |
| </varlistentry> |
| |
| <varlistentry> |
| <term><literal>SET STATISTICS</literal></term> |
| <listitem> |
| <para> |
| This form |
| sets the per-column statistics-gathering target for subsequent |
| <xref linkend="sql-analyze" endterm="sql-analyze-title"> operations. |
| The target can be set in the range 0 to 1000; alternatively, set it |
| to -1 to revert to using the system default statistics |
| target (<xref linkend="guc-default-statistics-target">). |
| For more information on the use of statistics by the |
| <productname>PostgreSQL</productname> query planner, refer to |
| <xref linkend="planner-stats">. |
| </para> |
| </listitem> |
| </varlistentry> |
| |
| <varlistentry> |
| <indexterm> |
| <primary>TOAST</primary> |
| <secondary>per-column storage settings</secondary> |
| </indexterm> |
| |
| <term><literal>SET STORAGE</literal></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 <type>integer</type> 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 most |
| data types that support non-<literal>PLAIN</literal> storage. |
| Use of <literal>EXTERNAL</literal> will |
| make substring operations on <type>text</type> and <type>bytea</type> |
| columns faster, at the penalty of increased storage space. Note that |
| <literal>SET STORAGE</> doesn't itself change anything in the table, |
| it just sets the strategy to be pursued during future table updates. |
| See <xref linkend="storage-toast"> for more information. |
| </para> |
| </listitem> |
| </varlistentry> |
| |
| <varlistentry> |
| <term><literal>ADD <replaceable class="PARAMETER">table_constraint</replaceable></literal></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><literal>DROP CONSTRAINT</literal></term> |
| <listitem> |
| <para> |
| This form drops the specified constraint on a table. |
| </para> |
| </listitem> |
| </varlistentry> |
| |
| <varlistentry> |
| <term><literal>DISABLE</literal>/<literal>ENABLE TRIGGER</literal></term> |
| <listitem> |
| <para> |
| These forms disable or enable trigger(s) belonging to the table. |
| A disabled trigger is still known to the system, but is not executed |
| when its triggering event occurs. For a deferred trigger, the enable |
| status is checked when the event occurs, not when the trigger function |
| is actually executed. One may disable or enable a single |
| trigger specified by name, or all triggers on the table, or only |
| user triggers (this option excludes triggers that are used to implement |
| foreign key constraints). Disabling or enabling constraint triggers |
| requires superuser privileges; it should be done with caution since |
| of course the integrity of the constraint cannot be guaranteed if the |
| triggers are not executed. |
| </para> |
| </listitem> |
| </varlistentry> |
| |
| <varlistentry> |
| <term><literal>CLUSTER</literal></term> |
| <listitem> |
| <para> |
| This form selects the default index for future |
| <xref linkend="SQL-CLUSTER" endterm="sql-cluster-title"> |
| operations. It does not actually re-cluster the table. |
| </para> |
| </listitem> |
| </varlistentry> |
| |
| <varlistentry> |
| <term><literal>SET WITHOUT CLUSTER</literal></term> |
| <listitem> |
| <para> |
| This form removes the most recently used |
| <xref linkend="SQL-CLUSTER" endterm="sql-cluster-title"> |
| index specification from the table. This affects |
| future cluster operations that don't specify an index. |
| </para> |
| </listitem> |
| </varlistentry> |
| |
| <varlistentry> |
| <term><literal>SET WITHOUT OIDS</literal></term> |
| <listitem> |
| <para> |
| This form removes the <literal>oid</literal> system column from the |
| table. This is exactly equivalent to |
| <literal>DROP COLUMN oid RESTRICT</literal>, |
| except that it will not complain if there is already no |
| <literal>oid</literal> column. |
| </para> |
| |
| <para> |
| Note that there is no variant of <command>ALTER TABLE</command> |
| that allows OIDs to be restored to a table once they have been |
| removed. |
| </para> |
| </listitem> |
| </varlistentry> |
| |
| <varlistentry> |
| <term><literal>SET ( <replaceable class="PARAMETER">storage_parameter</replaceable> = <replaceable class="PARAMETER">value</replaceable> [, ... ] )</literal></term> |
| <listitem> |
| <para> |
| This form changes one or more storage parameters for the table. See |
| <xref linkend="SQL-CREATETABLE" endterm="sql-createtable-title"> |
| for details on the available parameters. Note that the table contents |
| will not be modified immediately by this command; depending on the |
| parameter you may need to rewrite the table to get the desired effects. |
| That can be done with <xref linkend="SQL-CLUSTER" |
| endterm="sql-cluster-title"> or one of the forms of <command>ALTER |
| TABLE</> that forces a table rewrite. |
| </para> |
| |
| <note> |
| <para> |
| While <command>CREATE TABLE</> allows <literal>OIDS</> to be specified |
| in the <literal>WITH (<replaceable |
| class="PARAMETER">storage_parameter</>)</literal> syntax, |
| <command>ALTER TABLE</> does not treat <literal>OIDS</> as a |
| storage parameter. |
| </para> |
| </note> |
| </listitem> |
| </varlistentry> |
| |
| <varlistentry> |
| <term><literal>RESET ( <replaceable class="PARAMETER">storage_parameter</replaceable> [, ... ] )</literal></term> |
| <listitem> |
| <para> |
| This form resets one or more storage parameters to their |
| defaults. As with <literal>SET</>, a table rewrite may be |
| needed to update the table entirely. |
| </para> |
| </listitem> |
| </varlistentry> |
| |
| <varlistentry> |
| <term><literal>INHERIT <replaceable class="PARAMETER">parent_table</replaceable></literal></term> |
| <listitem> |
| <para> |
| This form adds the target table as a new child of the specified parent |
| table. Subsequently, queries against the parent will include records |
| of the target table. To be added as a child, the target table must |
| already contain all the same columns as the parent (it could have |
| additional columns, too). The columns must have matching data types, |
| and if they have <literal>NOT NULL</literal> constraints in the parent |
| then they must also have <literal>NOT NULL</literal> constraints in the |
| child. |
| </para> |
| |
| <para> |
| There must also be matching child-table constraints for all |
| <literal>CHECK</literal> constraints of the parent. Currently |
| <literal>UNIQUE</literal>, <literal>PRIMARY KEY</literal>, and |
| <literal>FOREIGN KEY</literal> constraints are not considered, but |
| this may change in the future. |
| </para> |
| </listitem> |
| </varlistentry> |
| |
| <varlistentry> |
| <term><literal>NO INHERIT <replaceable class="PARAMETER">parent_table</replaceable></literal></term> |
| <listitem> |
| <para> |
| This form removes the target table from the list of children of the |
| specified parent table. |
| Queries against the parent table will no longer include records drawn |
| from the target table. |
| </para> |
| </listitem> |
| </varlistentry> |
| |
| <varlistentry> |
| <term><literal>OWNER</literal></term> |
| <listitem> |
| <para> |
| This form changes the owner of the table, sequence, or view to the |
| specified user. |
| </para> |
| </listitem> |
| </varlistentry> |
| |
| <varlistentry> |
| <term><literal>SET TABLESPACE</literal></term> |
| <listitem> |
| <para> |
| This form changes the table's tablespace to the specified tablespace and |
| moves the data file(s) associated with the table to the new tablespace. |
| Indexes on the table, if any, are not moved; but they can be moved |
| separately with additional <literal>SET TABLESPACE</literal> commands. |
| See also |
| <xref linkend="SQL-CREATETABLESPACE" endterm="sql-createtablespace-title">. |
| </para> |
| </listitem> |
| </varlistentry> |
| |
| <varlistentry> |
| <term><literal>RENAME</literal></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><literal>SET SCHEMA</literal></term> |
| <listitem> |
| <para> |
| This form moves the table into another schema. Associated indexes, |
| constraints, and sequences owned by table columns are moved as well. |
| </para> |
| </listitem> |
| </varlistentry> |
| |
| </variablelist> |
| </para> |
| |
| <para> |
| All the actions except <literal>RENAME</literal> and <literal>SET SCHEMA</> |
| can be combined into |
| a list of multiple alterations to apply in parallel. For example, it |
| is possible to add several columns and/or alter the type of several |
| columns in a single command. This is particularly useful with large |
| tables, since only one pass over the table need be made. |
| </para> |
| |
| <para> |
| Note: If you add a partition to a table that |
| has subpartition encodings, the new partition |
| inherits the storage directives for the subpartitions. |
| For more information, search for "Precedence of Compression Settings" |
| in the Greenplum Database Administrator Guide. |
| </para> |
| |
| <para> |
| You must own the table to use <command>ALTER TABLE</>. |
| To change the schema of a table, you must also have |
| <literal>CREATE</literal> privilege on the new schema. |
| To add the table as a new child of a parent table, you must own the |
| parent table as well. |
| To alter the owner, you must also be a direct or indirect member of the new |
| owning role, and that role must have <literal>CREATE</literal> privilege on |
| the table's schema. (These restrictions enforce that altering the owner |
| doesn't do anything you couldn't do by dropping and recreating the table. |
| However, a superuser can alter ownership of any table anyway.) |
| </para> |
| </refsect1> |
| |
| <refsect1> |
| <title>Parameters</title> |
| |
| <variablelist> |
| |
| <varlistentry> |
| <term><replaceable class="PARAMETER">name</replaceable></term> |
| <listitem> |
| <para> |
| The name (possibly schema-qualified) of an existing table to |
| alter. If <literal>ONLY</> is specified, only that table is |
| altered. If <literal>ONLY</> is not specified, the table and all |
| its descendant tables (if any) are updated. <literal>*</> can be |
| appended to the table name to indicate that descendant tables are |
| to be altered, but in the current version, this is the default |
| behavior. (In releases before 7.1, <literal>ONLY</> was the |
| default behavior. The default can be altered by changing the |
| configuration parameter <xref linkend="guc-sql-inheritance">.) |
| </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">new_column</replaceable></term> |
| <listitem> |
| <para> |
| New name for an existing column. |
| </para> |
| </listitem> |
| </varlistentry> |
| |
| <varlistentry> |
| <term><replaceable class="PARAMETER">new_name</replaceable></term> |
| <listitem> |
| <para> |
| New name for the table. |
| </para> |
| </listitem> |
| </varlistentry> |
| |
| <varlistentry> |
| <term><replaceable class="PARAMETER">type</replaceable></term> |
| <listitem> |
| <para> |
| Data type of the new column, or new data type for an existing |
| column. |
| </para> |
| </listitem> |
| </varlistentry> |
| |
| <varlistentry> |
| <term><replaceable class="PARAMETER">table_constraint</replaceable></term> |
| <listitem> |
| <para> |
| 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> |
| |
| <varlistentry> |
| <term><literal>CASCADE</literal></term> |
| <listitem> |
| <para> |
| Automatically drop objects that depend on the dropped column |
| or constraint (for example, views referencing the column). |
| </para> |
| </listitem> |
| </varlistentry> |
| |
| <varlistentry> |
| <term><literal>RESTRICT</literal></term> |
| <listitem> |
| <para> |
| Refuse to drop the column or constraint if there are any dependent |
| objects. This is the default behavior. |
| </para> |
| </listitem> |
| </varlistentry> |
| |
| <varlistentry> |
| <term><replaceable class="PARAMETER">trigger_name</replaceable></term> |
| <listitem> |
| <para> |
| Name of a single trigger to disable or enable. |
| </para> |
| </listitem> |
| </varlistentry> |
| |
| <varlistentry> |
| <term><literal>ALL</literal></term> |
| <listitem> |
| <para> |
| Disable or enable all triggers belonging to the table. |
| (This requires superuser privilege if any of the triggers are for |
| foreign key constraints.) |
| </para> |
| </listitem> |
| </varlistentry> |
| |
| <varlistentry> |
| <term><literal>USER</literal></term> |
| <listitem> |
| <para> |
| Disable or enable all triggers belonging to the table except for |
| foreign key constraint triggers. |
| </para> |
| </listitem> |
| </varlistentry> |
| |
| <varlistentry> |
| <term><replaceable class="PARAMETER">index_name</replaceable></term> |
| <listitem> |
| <para> |
| The index name on which the table should be marked for clustering. |
| </para> |
| </listitem> |
| </varlistentry> |
| |
| <varlistentry> |
| <term><replaceable class="PARAMETER">storage_parameter</replaceable></term> |
| <listitem> |
| <para> |
| The name of a table storage parameter. |
| </para> |
| </listitem> |
| </varlistentry> |
| |
| <varlistentry> |
| <term><replaceable class="PARAMETER">value</replaceable></term> |
| <listitem> |
| <para> |
| The new value for a table storage parameter. |
| This might be a number or a word depending on the parameter. |
| </para> |
| </listitem> |
| </varlistentry> |
| |
| <varlistentry> |
| <term><replaceable class="PARAMETER">parent_table</replaceable></term> |
| <listitem> |
| <para> |
| A parent table to associate or de-associate with this table. |
| </para> |
| </listitem> |
| </varlistentry> |
| |
| <varlistentry> |
| <term><replaceable class="PARAMETER">new_owner</replaceable></term> |
| <listitem> |
| <para> |
| The user name of the new owner of the table. |
| </para> |
| </listitem> |
| </varlistentry> |
| |
| <varlistentry> |
| <term><replaceable class="PARAMETER">new_tablespace</replaceable></term> |
| <listitem> |
| <para> |
| The name of the tablespace to which the table will be moved. |
| </para> |
| </listitem> |
| </varlistentry> |
| |
| <varlistentry> |
| <term><replaceable class="PARAMETER">new_schema</replaceable></term> |
| <listitem> |
| <para> |
| The name of the schema to which the table will be moved. |
| </para> |
| </listitem> |
| </varlistentry> |
| |
| </variablelist> |
| </refsect1> |
| |
| <refsect1> |
| <title>Notes</title> |
| |
| <para> |
| The key word <literal>COLUMN</literal> is noise and can be omitted. |
| </para> |
| |
| <para> |
| When a column is added with <literal>ADD COLUMN</literal>, all existing |
| rows in the table are initialized with the column's default value |
| (NULL if no <literal>DEFAULT</> clause is specified). |
| </para> |
| |
| <para> |
| Adding a column with a non-null default or changing the type of an |
| existing column will require the entire table to be rewritten. This |
| may take a significant amount of time for a large table; and it will |
| temporarily require double the disk space. |
| </para> |
| |
| <para> |
| Adding a <literal>CHECK</> or <literal>NOT NULL</> constraint requires |
| scanning the table to verify that existing rows meet the constraint. |
| </para> |
| |
| <para> |
| The main reason for providing the option to specify multiple changes |
| in a single <command>ALTER TABLE</> is that multiple table scans or |
| rewrites can thereby be combined into a single pass over the table. |
| </para> |
| |
| <para> |
| The <literal>DROP COLUMN</literal> form does not physically remove |
| the column, but simply makes it invisible to SQL operations. Subsequent |
| insert and update operations in the table will store a null value for the |
| column. Thus, dropping a column is quick but it will not immediately |
| reduce the on-disk size of your table, as the space occupied |
| by the dropped column is not reclaimed. The space will be |
| reclaimed over time as existing rows are updated. |
| </para> |
| |
| <para> |
| The fact that <literal>ALTER TYPE</> requires rewriting the whole table |
| is sometimes an advantage, because the rewriting process eliminates |
| any dead space in the table. For example, to reclaim the space occupied |
| by a dropped column immediately, the fastest way is |
| <programlisting> |
| ALTER TABLE table ALTER COLUMN anycol TYPE anytype; |
| </programlisting> |
| where <literal>anycol</> is any remaining table column and |
| <literal>anytype</> is the same type that column already has. |
| This results in no semantically-visible change in the table, |
| but the command forces rewriting, which gets rid of no-longer-useful |
| data. |
| </para> |
| |
| <para> |
| The <literal>USING</literal> option of <literal>ALTER TYPE</> can actually |
| specify any expression involving the old values of the row; that is, it |
| can refer to other columns as well as the one being converted. This allows |
| very general conversions to be done with the <literal>ALTER TYPE</> |
| syntax. Because of this flexibility, the <literal>USING</literal> |
| expression is not applied to the column's default value (if any); the |
| result might not be a constant expression as required for a default. |
| This means that when there is no implicit or assignment cast from old to |
| new type, <literal>ALTER TYPE</> may fail to convert the default even |
| though a <literal>USING</literal> clause is supplied. In such cases, |
| drop the default with <literal>DROP DEFAULT</>, perform the <literal>ALTER |
| TYPE</>, and then use <literal>SET DEFAULT</> to add a suitable new |
| default. Similar considerations apply to indexes and constraints involving |
| the column. |
| </para> |
| |
| <para> |
| If a table has any descendant tables, it is not permitted to add, |
| rename, or change the type of a column in the parent table without doing |
| the same to the descendants. That is, <command>ALTER TABLE ONLY</command> |
| will be rejected. This ensures that the descendants always have |
| columns matching the parent. |
| </para> |
| |
| <para> |
| A recursive <literal>DROP COLUMN</literal> operation will remove a |
| descendant table's column only if the descendant does not inherit |
| that column from any other parents and never had an independent |
| definition of the column. A nonrecursive <literal>DROP |
| COLUMN</literal> (i.e., <command>ALTER TABLE ONLY ... DROP |
| COLUMN</command>) never removes any descendant columns, but |
| instead marks them as independently defined rather than inherited. |
| </para> |
| |
| <para> |
| The <literal>TRIGGER</>, <literal>CLUSTER</>, <literal>OWNER</>, |
| and <literal>TABLESPACE</> actions never recurse to descendant tables; |
| that is, they always act as though <literal>ONLY</> were specified. |
| Adding a constraint can recurse only for <literal>CHECK</> constraints. |
| </para> |
| |
| <para> |
| Changing any part of a system catalog table is not permitted. |
| </para> |
| |
| <para> |
| Refer to <xref linkend="sql-createtable" |
| endterm="sql-createtable-title"> for a further description of valid |
| parameters. <xref linkend="ddl"> has further information on |
| inheritance. |
| </para> |
| </refsect1> |
| |
| <refsect1> |
| <title>Examples</title> |
| |
| <para> |
| To add a column of type <type>varchar</type> to a table: |
| <programlisting> |
| ALTER TABLE distributors ADD COLUMN address varchar(30); |
| </programlisting> |
| </para> |
| |
| <para> |
| To drop a column from a table: |
| <programlisting> |
| ALTER TABLE distributors DROP COLUMN address RESTRICT; |
| </programlisting> |
| </para> |
| |
| <para> |
| To change the types of two existing columns in one operation: |
| <programlisting> |
| ALTER TABLE distributors |
| ALTER COLUMN address TYPE varchar(80), |
| ALTER COLUMN name TYPE varchar(100); |
| </programlisting> |
| </para> |
| |
| <para> |
| To change an integer column containing UNIX timestamps to <type>timestamp |
| with time zone</type> via a <literal>USING</literal> clause: |
| <programlisting> |
| ALTER TABLE foo |
| ALTER COLUMN foo_timestamp TYPE timestamp with time zone |
| USING |
| timestamp with time zone 'epoch' + foo_timestamp * interval '1 second'; |
| </programlisting> |
| </para> |
| |
| <para> |
| The same, when the column has a default expression that won't automatically |
| cast to the new data type: |
| <programlisting> |
| ALTER TABLE foo |
| ALTER COLUMN foo_timestamp DROP DEFAULT, |
| ALTER COLUMN foo_timestamp TYPE timestamp with time zone |
| USING |
| timestamp with time zone 'epoch' + foo_timestamp * interval '1 second', |
| ALTER COLUMN foo_timestamp SET DEFAULT now(); |
| </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 not-null constraint to a column: |
| <programlisting> |
| ALTER TABLE distributors ALTER COLUMN street SET NOT NULL; |
| </programlisting> |
| To remove a not-null constraint from a column: |
| <programlisting> |
| ALTER TABLE distributors ALTER COLUMN street DROP NOT NULL; |
| </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; |
| </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 (multicolumn) unique constraint to a table: |
| <programlisting> |
| ALTER TABLE distributors ADD CONSTRAINT dist_id_zipcode_key UNIQUE (dist_id, zipcode); |
| </programlisting> |
| </para> |
| |
| <para> |
| To add an automatically named primary key constraint to a table, noting |
| that a table can only ever have one primary key: |
| <programlisting> |
| ALTER TABLE distributors ADD PRIMARY KEY (dist_id); |
| </programlisting> |
| </para> |
| |
| <para> |
| To move a table to a different tablespace: |
| <programlisting> |
| ALTER TABLE distributors SET TABLESPACE fasttablespace; |
| </programlisting> |
| </para> |
| |
| <para> |
| To move a table to a different schema: |
| <programlisting> |
| ALTER TABLE myschema.distributors SET SCHEMA yourschema; |
| </programlisting> |
| </para> |
| |
| </refsect1> |
| |
| <refsect1> |
| <title>Compatibility</title> |
| |
| <para> |
| The <literal>ADD</literal>, <literal>DROP</>, and <literal>SET DEFAULT</> |
| forms conform with the SQL standard. The other forms are |
| <productname>PostgreSQL</productname> extensions of the SQL standard. |
| Also, the ability to specify more than one manipulation in a single |
| <command>ALTER TABLE</> command is an extension. |
| </para> |
| |
| <para> |
| <command>ALTER TABLE DROP COLUMN</> can be used to drop the only |
| column of a table, leaving a zero-column table. This is an |
| extension of SQL, which disallows zero-column tables. |
| </para> |
| </refsect1> |
| </refentry> |
| |
| |