summaryrefslogtreecommitdiffstats
path: root/doc/src/sgml/html/sql-altertable.html
diff options
context:
space:
mode:
authorDaniel Baumann <daniel.baumann@progress-linux.org>2024-05-04 12:19:15 +0000
committerDaniel Baumann <daniel.baumann@progress-linux.org>2024-05-04 12:19:15 +0000
commit6eb9c5a5657d1fe77b55cc261450f3538d35a94d (patch)
tree657d8194422a5daccecfd42d654b8a245ef7b4c8 /doc/src/sgml/html/sql-altertable.html
parentInitial commit. (diff)
downloadpostgresql-13-upstream.tar.xz
postgresql-13-upstream.zip
Adding upstream version 13.4.upstream/13.4upstream
Signed-off-by: Daniel Baumann <daniel.baumann@progress-linux.org>
Diffstat (limited to '')
-rw-r--r--doc/src/sgml/html/sql-altertable.html1011
1 files changed, 1011 insertions, 0 deletions
diff --git a/doc/src/sgml/html/sql-altertable.html b/doc/src/sgml/html/sql-altertable.html
new file mode 100644
index 0000000..36ef486
--- /dev/null
+++ b/doc/src/sgml/html/sql-altertable.html
@@ -0,0 +1,1011 @@
+<?xml version="1.0" encoding="UTF-8" standalone="no"?>
+<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd"><html xmlns="http://www.w3.org/1999/xhtml"><head><meta http-equiv="Content-Type" content="text/html; charset=UTF-8" /><title>ALTER TABLE</title><link rel="stylesheet" type="text/css" href="stylesheet.css" /><link rev="made" href="pgsql-docs@lists.postgresql.org" /><meta name="generator" content="DocBook XSL Stylesheets V1.79.1" /><link rel="prev" href="sql-altersystem.html" title="ALTER SYSTEM" /><link rel="next" href="sql-altertablespace.html" title="ALTER TABLESPACE" /></head><body id="docContent" class="container-fluid col-10"><div xmlns="http://www.w3.org/TR/xhtml1/transitional" class="navheader"><table width="100%" summary="Navigation header"><tr><th colspan="5" align="center">ALTER TABLE</th></tr><tr><td width="10%" align="left"><a accesskey="p" href="sql-altersystem.html" title="ALTER SYSTEM">Prev</a> </td><td width="10%" align="left"><a accesskey="u" href="sql-commands.html" title="SQL Commands">Up</a></td><th width="60%" align="center">SQL Commands</th><td width="10%" align="right"><a accesskey="h" href="index.html" title="PostgreSQL 13.4 Documentation">Home</a></td><td width="10%" align="right"> <a accesskey="n" href="sql-altertablespace.html" title="ALTER TABLESPACE">Next</a></td></tr></table><hr></hr></div><div class="refentry" id="SQL-ALTERTABLE"><div class="titlepage"></div><a id="id-1.9.3.35.1" class="indexterm"></a><div class="refnamediv"><h2><span class="refentrytitle">ALTER TABLE</span></h2><p>ALTER TABLE — change the definition of a table</p></div><div class="refsynopsisdiv"><h2>Synopsis</h2><pre class="synopsis">
+ALTER TABLE [ IF EXISTS ] [ ONLY ] <em class="replaceable"><code>name</code></em> [ * ]
+ <em class="replaceable"><code>action</code></em> [, ... ]
+ALTER TABLE [ IF EXISTS ] [ ONLY ] <em class="replaceable"><code>name</code></em> [ * ]
+ RENAME [ COLUMN ] <em class="replaceable"><code>column_name</code></em> TO <em class="replaceable"><code>new_column_name</code></em>
+ALTER TABLE [ IF EXISTS ] [ ONLY ] <em class="replaceable"><code>name</code></em> [ * ]
+ RENAME CONSTRAINT <em class="replaceable"><code>constraint_name</code></em> TO <em class="replaceable"><code>new_constraint_name</code></em>
+ALTER TABLE [ IF EXISTS ] <em class="replaceable"><code>name</code></em>
+ RENAME TO <em class="replaceable"><code>new_name</code></em>
+ALTER TABLE [ IF EXISTS ] <em class="replaceable"><code>name</code></em>
+ SET SCHEMA <em class="replaceable"><code>new_schema</code></em>
+ALTER TABLE ALL IN TABLESPACE <em class="replaceable"><code>name</code></em> [ OWNED BY <em class="replaceable"><code>role_name</code></em> [, ... ] ]
+ SET TABLESPACE <em class="replaceable"><code>new_tablespace</code></em> [ NOWAIT ]
+ALTER TABLE [ IF EXISTS ] <em class="replaceable"><code>name</code></em>
+ ATTACH PARTITION <em class="replaceable"><code>partition_name</code></em> { FOR VALUES <em class="replaceable"><code>partition_bound_spec</code></em> | DEFAULT }
+ALTER TABLE [ IF EXISTS ] <em class="replaceable"><code>name</code></em>
+ DETACH PARTITION <em class="replaceable"><code>partition_name</code></em>
+
+<span class="phrase">where <em class="replaceable"><code>action</code></em> is one of:</span>
+
+ ADD [ COLUMN ] [ IF NOT EXISTS ] <em class="replaceable"><code>column_name</code></em> <em class="replaceable"><code>data_type</code></em> [ COLLATE <em class="replaceable"><code>collation</code></em> ] [ <em class="replaceable"><code>column_constraint</code></em> [ ... ] ]
+ DROP [ COLUMN ] [ IF EXISTS ] <em class="replaceable"><code>column_name</code></em> [ RESTRICT | CASCADE ]
+ ALTER [ COLUMN ] <em class="replaceable"><code>column_name</code></em> [ SET DATA ] TYPE <em class="replaceable"><code>data_type</code></em> [ COLLATE <em class="replaceable"><code>collation</code></em> ] [ USING <em class="replaceable"><code>expression</code></em> ]
+ ALTER [ COLUMN ] <em class="replaceable"><code>column_name</code></em> SET DEFAULT <em class="replaceable"><code>expression</code></em>
+ ALTER [ COLUMN ] <em class="replaceable"><code>column_name</code></em> DROP DEFAULT
+ ALTER [ COLUMN ] <em class="replaceable"><code>column_name</code></em> { SET | DROP } NOT NULL
+ ALTER [ COLUMN ] <em class="replaceable"><code>column_name</code></em> DROP EXPRESSION [ IF EXISTS ]
+ ALTER [ COLUMN ] <em class="replaceable"><code>column_name</code></em> ADD GENERATED { ALWAYS | BY DEFAULT } AS IDENTITY [ ( <em class="replaceable"><code>sequence_options</code></em> ) ]
+ ALTER [ COLUMN ] <em class="replaceable"><code>column_name</code></em> { SET GENERATED { ALWAYS | BY DEFAULT } | SET <em class="replaceable"><code>sequence_option</code></em> | RESTART [ [ WITH ] <em class="replaceable"><code>restart</code></em> ] } [...]
+ ALTER [ COLUMN ] <em class="replaceable"><code>column_name</code></em> DROP IDENTITY [ IF EXISTS ]
+ ALTER [ COLUMN ] <em class="replaceable"><code>column_name</code></em> SET STATISTICS <em class="replaceable"><code>integer</code></em>
+ ALTER [ COLUMN ] <em class="replaceable"><code>column_name</code></em> SET ( <em class="replaceable"><code>attribute_option</code></em> = <em class="replaceable"><code>value</code></em> [, ... ] )
+ ALTER [ COLUMN ] <em class="replaceable"><code>column_name</code></em> RESET ( <em class="replaceable"><code>attribute_option</code></em> [, ... ] )
+ ALTER [ COLUMN ] <em class="replaceable"><code>column_name</code></em> SET STORAGE { PLAIN | EXTERNAL | EXTENDED | MAIN }
+ ADD <em class="replaceable"><code>table_constraint</code></em> [ NOT VALID ]
+ ADD <em class="replaceable"><code>table_constraint_using_index</code></em>
+ ALTER CONSTRAINT <em class="replaceable"><code>constraint_name</code></em> [ DEFERRABLE | NOT DEFERRABLE ] [ INITIALLY DEFERRED | INITIALLY IMMEDIATE ]
+ VALIDATE CONSTRAINT <em class="replaceable"><code>constraint_name</code></em>
+ DROP CONSTRAINT [ IF EXISTS ] <em class="replaceable"><code>constraint_name</code></em> [ RESTRICT | CASCADE ]
+ DISABLE TRIGGER [ <em class="replaceable"><code>trigger_name</code></em> | ALL | USER ]
+ ENABLE TRIGGER [ <em class="replaceable"><code>trigger_name</code></em> | ALL | USER ]
+ ENABLE REPLICA TRIGGER <em class="replaceable"><code>trigger_name</code></em>
+ ENABLE ALWAYS TRIGGER <em class="replaceable"><code>trigger_name</code></em>
+ DISABLE RULE <em class="replaceable"><code>rewrite_rule_name</code></em>
+ ENABLE RULE <em class="replaceable"><code>rewrite_rule_name</code></em>
+ ENABLE REPLICA RULE <em class="replaceable"><code>rewrite_rule_name</code></em>
+ ENABLE ALWAYS RULE <em class="replaceable"><code>rewrite_rule_name</code></em>
+ DISABLE ROW LEVEL SECURITY
+ ENABLE ROW LEVEL SECURITY
+ FORCE ROW LEVEL SECURITY
+ NO FORCE ROW LEVEL SECURITY
+ CLUSTER ON <em class="replaceable"><code>index_name</code></em>
+ SET WITHOUT CLUSTER
+ SET WITHOUT OIDS
+ SET TABLESPACE <em class="replaceable"><code>new_tablespace</code></em>
+ SET { LOGGED | UNLOGGED }
+ SET ( <em class="replaceable"><code>storage_parameter</code></em> [= <em class="replaceable"><code>value</code></em>] [, ... ] )
+ RESET ( <em class="replaceable"><code>storage_parameter</code></em> [, ... ] )
+ INHERIT <em class="replaceable"><code>parent_table</code></em>
+ NO INHERIT <em class="replaceable"><code>parent_table</code></em>
+ OF <em class="replaceable"><code>type_name</code></em>
+ NOT OF
+ OWNER TO { <em class="replaceable"><code>new_owner</code></em> | CURRENT_USER | SESSION_USER }
+ REPLICA IDENTITY { DEFAULT | USING INDEX <em class="replaceable"><code>index_name</code></em> | FULL | NOTHING }
+
+<span class="phrase">and <em class="replaceable"><code>partition_bound_spec</code></em> is:</span>
+
+IN ( <em class="replaceable"><code>partition_bound_expr</code></em> [, ...] ) |
+FROM ( { <em class="replaceable"><code>partition_bound_expr</code></em> | MINVALUE | MAXVALUE } [, ...] )
+ TO ( { <em class="replaceable"><code>partition_bound_expr</code></em> | MINVALUE | MAXVALUE } [, ...] ) |
+WITH ( MODULUS <em class="replaceable"><code>numeric_literal</code></em>, REMAINDER <em class="replaceable"><code>numeric_literal</code></em> )
+
+<span class="phrase">and <em class="replaceable"><code>column_constraint</code></em> is:</span>
+
+[ CONSTRAINT <em class="replaceable"><code>constraint_name</code></em> ]
+{ NOT NULL |
+ NULL |
+ CHECK ( <em class="replaceable"><code>expression</code></em> ) [ NO INHERIT ] |
+ DEFAULT <em class="replaceable"><code>default_expr</code></em> |
+ GENERATED ALWAYS AS ( <em class="replaceable"><code>generation_expr</code></em> ) STORED |
+ GENERATED { ALWAYS | BY DEFAULT } AS IDENTITY [ ( <em class="replaceable"><code>sequence_options</code></em> ) ] |
+ UNIQUE <em class="replaceable"><code>index_parameters</code></em> |
+ PRIMARY KEY <em class="replaceable"><code>index_parameters</code></em> |
+ REFERENCES <em class="replaceable"><code>reftable</code></em> [ ( <em class="replaceable"><code>refcolumn</code></em> ) ] [ MATCH FULL | MATCH PARTIAL | MATCH SIMPLE ]
+ [ ON DELETE <em class="replaceable"><code>referential_action</code></em> ] [ ON UPDATE <em class="replaceable"><code>referential_action</code></em> ] }
+[ DEFERRABLE | NOT DEFERRABLE ] [ INITIALLY DEFERRED | INITIALLY IMMEDIATE ]
+
+<span class="phrase">and <em class="replaceable"><code>table_constraint</code></em> is:</span>
+
+[ CONSTRAINT <em class="replaceable"><code>constraint_name</code></em> ]
+{ CHECK ( <em class="replaceable"><code>expression</code></em> ) [ NO INHERIT ] |
+ UNIQUE ( <em class="replaceable"><code>column_name</code></em> [, ... ] ) <em class="replaceable"><code>index_parameters</code></em> |
+ PRIMARY KEY ( <em class="replaceable"><code>column_name</code></em> [, ... ] ) <em class="replaceable"><code>index_parameters</code></em> |
+ EXCLUDE [ USING <em class="replaceable"><code>index_method</code></em> ] ( <em class="replaceable"><code>exclude_element</code></em> WITH <em class="replaceable"><code>operator</code></em> [, ... ] ) <em class="replaceable"><code>index_parameters</code></em> [ WHERE ( <em class="replaceable"><code>predicate</code></em> ) ] |
+ FOREIGN KEY ( <em class="replaceable"><code>column_name</code></em> [, ... ] ) REFERENCES <em class="replaceable"><code>reftable</code></em> [ ( <em class="replaceable"><code>refcolumn</code></em> [, ... ] ) ]
+ [ MATCH FULL | MATCH PARTIAL | MATCH SIMPLE ] [ ON DELETE <em class="replaceable"><code>referential_action</code></em> ] [ ON UPDATE <em class="replaceable"><code>referential_action</code></em> ] }
+[ DEFERRABLE | NOT DEFERRABLE ] [ INITIALLY DEFERRED | INITIALLY IMMEDIATE ]
+
+<span class="phrase">and <em class="replaceable"><code>table_constraint_using_index</code></em> is:</span>
+
+ [ CONSTRAINT <em class="replaceable"><code>constraint_name</code></em> ]
+ { UNIQUE | PRIMARY KEY } USING INDEX <em class="replaceable"><code>index_name</code></em>
+ [ DEFERRABLE | NOT DEFERRABLE ] [ INITIALLY DEFERRED | INITIALLY IMMEDIATE ]
+
+<span class="phrase"><em class="replaceable"><code>index_parameters</code></em> in <code class="literal">UNIQUE</code>, <code class="literal">PRIMARY KEY</code>, and <code class="literal">EXCLUDE</code> constraints are:</span>
+
+[ INCLUDE ( <em class="replaceable"><code>column_name</code></em> [, ... ] ) ]
+[ WITH ( <em class="replaceable"><code>storage_parameter</code></em> [= <em class="replaceable"><code>value</code></em>] [, ... ] ) ]
+[ USING INDEX TABLESPACE <em class="replaceable"><code>tablespace_name</code></em> ]
+
+<span class="phrase"><em class="replaceable"><code>exclude_element</code></em> in an <code class="literal">EXCLUDE</code> constraint is:</span>
+
+{ <em class="replaceable"><code>column_name</code></em> | ( <em class="replaceable"><code>expression</code></em> ) } [ <em class="replaceable"><code>opclass</code></em> ] [ ASC | DESC ] [ NULLS { FIRST | LAST } ]
+</pre></div><div class="refsect1" id="id-1.9.3.35.5"><h2>Description</h2><p>
+ <code class="command">ALTER TABLE</code> changes the definition of an existing table.
+ There are several subforms described below. Note that the lock level required
+ may differ for each subform. An <code class="literal">ACCESS EXCLUSIVE</code> lock is
+ acquired unless explicitly noted. When multiple subcommands are given, the
+ lock acquired will be the strictest one required by any subcommand.
+
+ </p><div class="variablelist"><dl class="variablelist"><dt><span class="term"><code class="literal">ADD COLUMN [ IF NOT EXISTS ]</code></span></dt><dd><p>
+ This form adds a new column to the table, using the same syntax as
+ <a class="xref" href="sql-createtable.html" title="CREATE TABLE"><span class="refentrytitle">CREATE TABLE</span></a>. If <code class="literal">IF NOT EXISTS</code>
+ is specified and a column already exists with this name,
+ no error is thrown.
+ </p></dd><dt><span class="term"><code class="literal">DROP COLUMN [ IF EXISTS ]</code></span></dt><dd><p>
+ This form drops a column from a table. Indexes and
+ table constraints involving the column will be automatically
+ dropped as well.
+ Multivariate statistics referencing the dropped column will also be
+ removed if the removal of the column would cause the statistics to
+ contain data for only a single column.
+ You will need to say <code class="literal">CASCADE</code> if anything outside the table
+ depends on the column, for example, foreign key references or views.
+ If <code class="literal">IF EXISTS</code> is specified and the column
+ does not exist, no error is thrown. In this case a notice
+ is issued instead.
+ </p></dd><dt><span class="term"><code class="literal">SET DATA TYPE</code></span></dt><dd><p>
+ 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 <code class="literal">COLLATE</code> clause specifies a collation
+ for the new column; if omitted, the collation is the default for the
+ new column type.
+ The optional <code class="literal">USING</code>
+ 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 <code class="literal">USING</code>
+ clause must be provided if there is no implicit or assignment
+ cast from old to new type.
+ </p></dd><dt><span class="term"><code class="literal">SET</code>/<code class="literal">DROP DEFAULT</code></span></dt><dd><p>
+ These forms set or remove the default value for a column (where
+ removal is equivalent to setting the default value to NULL). The new
+ default value will only apply in subsequent <code class="command">INSERT</code>
+ or <code class="command">UPDATE</code> commands; it does not cause rows already
+ in the table to change.
+ </p></dd><dt><span class="term"><code class="literal">SET</code>/<code class="literal">DROP NOT NULL</code></span></dt><dd><p>
+ These forms change whether a column is marked to allow null
+ values or to reject null values.
+ </p><p>
+ <code class="literal">SET NOT NULL</code> may only be applied to a column
+ provided none of the records in the table contain a
+ <code class="literal">NULL</code> value for the column. Ordinarily this is
+ checked during the <code class="literal">ALTER TABLE</code> by scanning the
+ entire table; however, if a valid <code class="literal">CHECK</code> constraint is
+ found which proves no <code class="literal">NULL</code> can exist, then the
+ table scan is skipped.
+ </p><p>
+ If this table is a partition, one cannot perform <code class="literal">DROP NOT NULL</code>
+ on a column if it is marked <code class="literal">NOT NULL</code> in the parent
+ table. To drop the <code class="literal">NOT NULL</code> constraint from all the
+ partitions, perform <code class="literal">DROP NOT NULL</code> on the parent
+ table. Even if there is no <code class="literal">NOT NULL</code> constraint on the
+ parent, such a constraint can still be added to individual partitions,
+ if desired; that is, the children can disallow nulls even if the parent
+ allows them, but not the other way around.
+ </p></dd><dt><span class="term"><code class="literal">DROP EXPRESSION [ IF EXISTS ]</code></span></dt><dd><p>
+ This form turns a stored generated column into a normal base column.
+ Existing data in the columns is retained, but future changes will no
+ longer apply the generation expression.
+ </p><p>
+ If <code class="literal">DROP EXPRESSION IF EXISTS</code> is specified and the
+ column is not a stored generated column, no error is thrown. In this
+ case a notice is issued instead.
+ </p></dd><dt><span class="term"><code class="literal">ADD GENERATED { ALWAYS | BY DEFAULT } AS IDENTITY</code><br /></span><span class="term"><code class="literal">SET GENERATED { ALWAYS | BY DEFAULT }</code><br /></span><span class="term"><code class="literal">DROP IDENTITY [ IF EXISTS ]</code></span></dt><dd><p>
+ These forms change whether a column is an identity column or change the
+ generation attribute of an existing identity column.
+ See <a class="xref" href="sql-createtable.html" title="CREATE TABLE"><span class="refentrytitle">CREATE TABLE</span></a> for details.
+ Like <code class="literal">SET DEFAULT</code>, these forms only affect the
+ behavior of subsequent <code class="command">INSERT</code>
+ and <code class="command">UPDATE</code> commands; they do not cause rows
+ already in the table to change.
+ </p><p>
+ If <code class="literal">DROP IDENTITY IF EXISTS</code> is specified and the
+ column is not an identity column, no error is thrown. In this case a
+ notice is issued instead.
+ </p></dd><dt><span class="term"><code class="literal">SET <em class="replaceable"><code>sequence_option</code></em></code><br /></span><span class="term"><code class="literal">RESTART</code></span></dt><dd><p>
+ These forms alter the sequence that underlies an existing identity
+ column. <em class="replaceable"><code>sequence_option</code></em> is an option
+ supported by <a class="xref" href="sql-altersequence.html" title="ALTER SEQUENCE"><span class="refentrytitle">ALTER SEQUENCE</span></a> such
+ as <code class="literal">INCREMENT BY</code>.
+ </p></dd><dt><span class="term"><code class="literal">SET STATISTICS</code></span></dt><dd><p>
+ This form
+ sets the per-column statistics-gathering target for subsequent
+ <a class="xref" href="sql-analyze.html" title="ANALYZE"><span class="refentrytitle">ANALYZE</span></a> operations.
+ The target can be set in the range 0 to 10000; alternatively, set it
+ to -1 to revert to using the system default statistics
+ target (<a class="xref" href="runtime-config-query.html#GUC-DEFAULT-STATISTICS-TARGET">default_statistics_target</a>).
+ For more information on the use of statistics by the
+ <span class="productname">PostgreSQL</span> query planner, refer to
+ <a class="xref" href="planner-stats.html" title="14.2. Statistics Used by the Planner">Section 14.2</a>.
+ </p><p>
+ <code class="literal">SET STATISTICS</code> acquires a
+ <code class="literal">SHARE UPDATE EXCLUSIVE</code> lock.
+ </p></dd><dt><span class="term"><code class="literal">SET ( <em class="replaceable"><code>attribute_option</code></em> = <em class="replaceable"><code>value</code></em> [, ... ] )</code><br /></span><span class="term"><code class="literal">RESET ( <em class="replaceable"><code>attribute_option</code></em> [, ... ] )</code></span></dt><dd><p>
+ This form sets or resets per-attribute options. Currently, the only
+ defined per-attribute options are <code class="literal">n_distinct</code> and
+ <code class="literal">n_distinct_inherited</code>, which override the
+ number-of-distinct-values estimates made by subsequent
+ <a class="xref" href="sql-analyze.html" title="ANALYZE"><span class="refentrytitle">ANALYZE</span></a>
+ operations. <code class="literal">n_distinct</code> affects the statistics for the table
+ itself, while <code class="literal">n_distinct_inherited</code> affects the statistics
+ gathered for the table plus its inheritance children. When set to a
+ positive value, <code class="command">ANALYZE</code> will assume that the column contains
+ exactly the specified number of distinct nonnull values. When set to a
+ negative value, which must be greater
+ than or equal to -1, <code class="command">ANALYZE</code> will assume that the number of
+ distinct nonnull values in the column is linear in the size of the
+ table; the exact count is to be computed by multiplying the estimated
+ table size by the absolute value of the given number. For example,
+ a value of -1 implies that all values in the column are distinct, while
+ a value of -0.5 implies that each value appears twice on the average.
+ This can be useful when the size of the table changes over time, since
+ the multiplication by the number of rows in the table is not performed
+ until query planning time. Specify a value of 0 to revert to estimating
+ the number of distinct values normally. For more information on the use
+ of statistics by the <span class="productname">PostgreSQL</span> query
+ planner, refer to <a class="xref" href="planner-stats.html" title="14.2. Statistics Used by the Planner">Section 14.2</a>.
+ </p><p>
+ Changing per-attribute options acquires a
+ <code class="literal">SHARE UPDATE EXCLUSIVE</code> lock.
+ </p></dd><dt><span class="term">
+ <code class="literal">SET STORAGE</code>
+ <a id="id-1.9.3.35.5.2.3.11.1.2" class="indexterm"></a>
+ </span></dt><dd><p>
+ This form sets the storage mode for a column. This controls whether this
+ column is held inline or in a secondary <acronym class="acronym">TOAST</acronym> table, and
+ whether the data
+ should be compressed or not. <code class="literal">PLAIN</code> must be used
+ for fixed-length values such as <code class="type">integer</code> and is
+ inline, uncompressed. <code class="literal">MAIN</code> is for inline,
+ compressible data. <code class="literal">EXTERNAL</code> is for external,
+ uncompressed data, and <code class="literal">EXTENDED</code> is for external,
+ compressed data. <code class="literal">EXTENDED</code> is the default for most
+ data types that support non-<code class="literal">PLAIN</code> storage.
+ Use of <code class="literal">EXTERNAL</code> will make substring operations on
+ very large <code class="type">text</code> and <code class="type">bytea</code> values run faster,
+ at the penalty of increased storage space. Note that
+ <code class="literal">SET STORAGE</code> doesn't itself change anything in the table,
+ it just sets the strategy to be pursued during future table updates.
+ See <a class="xref" href="storage-toast.html" title="69.2. TOAST">Section 69.2</a> for more information.
+ </p></dd><dt><span class="term"><code class="literal">ADD <em class="replaceable"><code>table_constraint</code></em> [ NOT VALID ]</code></span></dt><dd><p>
+ This form adds a new constraint to a table using the same constraint
+ syntax as <a class="xref" href="sql-createtable.html" title="CREATE TABLE"><span class="refentrytitle">CREATE TABLE</span></a>, plus the option <code class="literal">NOT
+ VALID</code>, which is currently only allowed for foreign key
+ and CHECK constraints.
+ </p><p>
+ Normally, this form will cause a scan of the table to verify that all
+ existing rows in the table satisfy the new constraint. But if
+ the <code class="literal">NOT VALID</code> option is used, this
+ potentially-lengthy scan is skipped. The constraint will still be
+ enforced against subsequent inserts or updates (that is, they'll fail
+ unless there is a matching row in the referenced table, in the case
+ of foreign keys, or they'll fail unless the new row matches the
+ specified check condition). But the
+ database will not assume that the constraint holds for all rows in
+ the table, until it is validated by using the <code class="literal">VALIDATE
+ CONSTRAINT</code> option.
+ See <a class="xref" href="sql-altertable.html#SQL-ALTERTABLE-NOTES" title="Notes">Notes</a> below for more information
+ about using the <code class="literal">NOT VALID</code> option.
+ </p><p>
+ Although most forms of <code class="literal">ADD
+ <em class="replaceable"><code>table_constraint</code></em></code>
+ require an <code class="literal">ACCESS EXCLUSIVE</code> lock, <code class="literal">ADD
+ FOREIGN KEY</code> requires only a <code class="literal">SHARE ROW
+ EXCLUSIVE</code> lock. Note that <code class="literal">ADD FOREIGN KEY</code>
+ also acquires a <code class="literal">SHARE ROW EXCLUSIVE</code> lock on the
+ referenced table, in addition to the lock on the table on which the
+ constraint is declared.
+ </p><p>
+ Additional restrictions apply when unique or primary key constraints
+ are added to partitioned tables; see <a class="xref" href="sql-createtable.html" title="CREATE TABLE"><span class="refentrytitle">CREATE TABLE</span></a>.
+ Also, foreign key constraints on partitioned
+ tables may not be declared <code class="literal">NOT VALID</code> at present.
+ </p></dd><dt><span class="term"><code class="literal">ADD <em class="replaceable"><code>table_constraint_using_index</code></em></code></span></dt><dd><p>
+ This form adds a new <code class="literal">PRIMARY KEY</code> or <code class="literal">UNIQUE</code>
+ constraint to a table based on an existing unique index. All the
+ columns of the index will be included in the constraint.
+ </p><p>
+ The index cannot have expression columns nor be a partial index.
+ Also, it must be a b-tree index with default sort ordering. These
+ restrictions ensure that the index is equivalent to one that would be
+ built by a regular <code class="literal">ADD PRIMARY KEY</code> or <code class="literal">ADD UNIQUE</code>
+ command.
+ </p><p>
+ If <code class="literal">PRIMARY KEY</code> is specified, and the index's columns are not
+ already marked <code class="literal">NOT NULL</code>, then this command will attempt to
+ do <code class="literal">ALTER COLUMN SET NOT NULL</code> against each such column.
+ That requires a full table scan to verify the column(s) contain no
+ nulls. In all other cases, this is a fast operation.
+ </p><p>
+ If a constraint name is provided then the index will be renamed to match
+ the constraint name. Otherwise the constraint will be named the same as
+ the index.
+ </p><p>
+ After this command is executed, the index is <span class="quote">“<span class="quote">owned</span>”</span> by the
+ constraint, in the same way as if the index had been built by
+ a regular <code class="literal">ADD PRIMARY KEY</code> or <code class="literal">ADD UNIQUE</code>
+ command. In particular, dropping the constraint will make the index
+ disappear too.
+ </p><p>
+ This form is not currently supported on partitioned tables.
+ </p><div class="note"><h3 class="title">Note</h3><p>
+ Adding a constraint using an existing index can be helpful in
+ situations where a new constraint needs to be added without blocking
+ table updates for a long time. To do that, create the index using
+ <code class="command">CREATE INDEX CONCURRENTLY</code>, and then install it as an
+ official constraint using this syntax. See the example below.
+ </p></div></dd><dt><span class="term"><code class="literal">ALTER CONSTRAINT</code></span></dt><dd><p>
+ This form alters the attributes of a constraint that was previously
+ created. Currently only foreign key constraints may be altered.
+ </p></dd><dt><span class="term"><code class="literal">VALIDATE CONSTRAINT</code></span></dt><dd><p>
+ This form validates a foreign key or check constraint that was
+ previously created as <code class="literal">NOT VALID</code>, by scanning the
+ table to ensure there are no rows for which the constraint is not
+ satisfied. Nothing happens if the constraint is already marked valid.
+ (See <a class="xref" href="sql-altertable.html#SQL-ALTERTABLE-NOTES" title="Notes">Notes</a> below for an explanation
+ of the usefulness of this command.)
+ </p><p>
+ This command acquires a <code class="literal">SHARE UPDATE EXCLUSIVE</code> lock.
+ </p></dd><dt><span class="term"><code class="literal">DROP CONSTRAINT [ IF EXISTS ]</code></span></dt><dd><p>
+ This form drops the specified constraint on a table, along with
+ any index underlying the constraint.
+ If <code class="literal">IF EXISTS</code> is specified and the constraint
+ does not exist, no error is thrown. In this case a notice is issued instead.
+ </p></dd><dt><span class="term"><code class="literal">DISABLE</code>/<code class="literal">ENABLE [ REPLICA | ALWAYS ] TRIGGER</code></span></dt><dd><p>
+ These forms configure the firing of 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 can disable or enable a single
+ trigger specified by name, or all triggers on the table, or only
+ user triggers (this option excludes internally generated constraint
+ triggers such as those that are used to implement foreign key
+ constraints or deferrable uniqueness and exclusion constraints).
+ Disabling or enabling internally generated 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.
+ </p><p>
+ The trigger firing mechanism is also affected by the configuration
+ variable <a class="xref" href="runtime-config-client.html#GUC-SESSION-REPLICATION-ROLE">session_replication_role</a>. Simply enabled
+ triggers (the default) will fire when the replication role is <span class="quote">“<span class="quote">origin</span>”</span>
+ (the default) or <span class="quote">“<span class="quote">local</span>”</span>. Triggers configured as <code class="literal">ENABLE
+ REPLICA</code> will only fire if the session is in <span class="quote">“<span class="quote">replica</span>”</span>
+ mode, and triggers configured as <code class="literal">ENABLE ALWAYS</code> will
+ fire regardless of the current replication role.
+ </p><p>
+ The effect of this mechanism is that in the default configuration,
+ triggers do not fire on replicas. This is useful because if a trigger
+ is used on the origin to propagate data between tables, then the
+ replication system will also replicate the propagated data, and the
+ trigger should not fire a second time on the replica, because that would
+ lead to duplication. However, if a trigger is used for another purpose
+ such as creating external alerts, then it might be appropriate to set it
+ to <code class="literal">ENABLE ALWAYS</code> so that it is also fired on
+ replicas.
+ </p><p>
+ This command acquires a <code class="literal">SHARE ROW EXCLUSIVE</code> lock.
+ </p></dd><dt><span class="term"><code class="literal">DISABLE</code>/<code class="literal">ENABLE [ REPLICA | ALWAYS ] RULE</code></span></dt><dd><p>
+ These forms configure the firing of rewrite rules belonging to the table.
+ A disabled rule is still known to the system, but is not applied
+ during query rewriting. The semantics are as for disabled/enabled
+ triggers. This configuration is ignored for <code class="literal">ON SELECT</code> rules, which
+ are always applied in order to keep views working even if the current
+ session is in a non-default replication role.
+ </p><p>
+ The rule firing mechanism is also affected by the configuration variable
+ <a class="xref" href="runtime-config-client.html#GUC-SESSION-REPLICATION-ROLE">session_replication_role</a>, analogous to triggers as
+ described above.
+ </p></dd><dt><span class="term"><code class="literal">DISABLE</code>/<code class="literal">ENABLE ROW LEVEL SECURITY</code></span></dt><dd><p>
+ These forms control the application of row security policies belonging
+ to the table. If enabled and no policies exist for the table, then a
+ default-deny policy is applied. Note that policies can exist for a table
+ even if row level security is disabled. In this case, the policies will
+ <span class="emphasis"><em>not</em></span> be applied and the policies will be ignored.
+ See also
+ <a class="xref" href="sql-createpolicy.html" title="CREATE POLICY"><span class="refentrytitle">CREATE POLICY</span></a>.
+ </p></dd><dt><span class="term"><code class="literal">NO FORCE</code>/<code class="literal">FORCE ROW LEVEL SECURITY</code></span></dt><dd><p>
+ These forms control the application of row security policies belonging
+ to the table when the user is the table owner. If enabled, row level
+ security policies will be applied when the user is the table owner. If
+ disabled (the default) then row level security will not be applied when
+ the user is the table owner.
+ See also
+ <a class="xref" href="sql-createpolicy.html" title="CREATE POLICY"><span class="refentrytitle">CREATE POLICY</span></a>.
+ </p></dd><dt><span class="term"><code class="literal">CLUSTER ON</code></span></dt><dd><p>
+ This form selects the default index for future
+ <a class="xref" href="sql-cluster.html" title="CLUSTER"><span class="refentrytitle">CLUSTER</span></a>
+ operations. It does not actually re-cluster the table.
+ </p><p>
+ Changing cluster options acquires a <code class="literal">SHARE UPDATE EXCLUSIVE</code> lock.
+ </p></dd><dt><span class="term"><code class="literal">SET WITHOUT CLUSTER</code></span></dt><dd><p>
+ This form removes the most recently used
+ <a class="xref" href="sql-cluster.html" title="CLUSTER"><span class="refentrytitle">CLUSTER</span></a>
+ index specification from the table. This affects
+ future cluster operations that don't specify an index.
+ </p><p>
+ Changing cluster options acquires a <code class="literal">SHARE UPDATE EXCLUSIVE</code> lock.
+ </p></dd><dt><span class="term"><code class="literal">SET WITHOUT OIDS</code></span></dt><dd><p>
+ Backward-compatible syntax for removing the <code class="literal">oid</code>
+ system column. As <code class="literal">oid</code> system columns cannot be
+ added anymore, this never has an effect.
+ </p></dd><dt><span class="term"><code class="literal">SET TABLESPACE</code></span></dt><dd><p>
+ 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 <code class="literal">SET TABLESPACE</code> commands.
+ When applied to a partitioned table, nothing is moved, but any
+ partitions created afterwards with
+ <code class="command">CREATE TABLE PARTITION OF</code> will use that tablespace,
+ unless overridden by a <code class="literal">TABLESPACE</code> clause.
+ </p><p>
+ All tables in the current database in a tablespace can be moved by using
+ the <code class="literal">ALL IN TABLESPACE</code> form, which will lock all tables
+ to be moved first and then move each one. This form also supports
+ <code class="literal">OWNED BY</code>, which will only move tables owned by the
+ roles specified. If the <code class="literal">NOWAIT</code> option is specified
+ then the command will fail if it is unable to acquire all of the locks
+ required immediately. Note that system catalogs are not moved by this
+ command; use <code class="command">ALTER DATABASE</code> or explicit
+ <code class="command">ALTER TABLE</code> invocations instead if desired. The
+ <code class="literal">information_schema</code> relations are not considered part
+ of the system catalogs and will be moved.
+ See also
+ <a class="xref" href="sql-createtablespace.html" title="CREATE TABLESPACE"><span class="refentrytitle">CREATE TABLESPACE</span></a>.
+ </p></dd><dt><span class="term"><code class="literal">SET { LOGGED | UNLOGGED }</code></span></dt><dd><p>
+ This form changes the table from unlogged to logged or vice-versa
+ (see <a class="xref" href="sql-createtable.html#SQL-CREATETABLE-UNLOGGED"><code class="literal">UNLOGGED</code></a>). It cannot be applied
+ to a temporary table.
+ </p></dd><dt><span class="term"><code class="literal">SET ( <em class="replaceable"><code>storage_parameter</code></em> [= <em class="replaceable"><code>value</code></em>] [, ... ] )</code></span></dt><dd><p>
+ This form changes one or more storage parameters for the table. See
+ <a class="xref" href="sql-createtable.html#SQL-CREATETABLE-STORAGE-PARAMETERS" title="Storage Parameters">Storage Parameters</a> in the
+ <a class="xref" href="sql-createtable.html" title="CREATE TABLE"><span class="refentrytitle">CREATE TABLE</span></a> documentation
+ for details on the available parameters. Note that the table contents
+ will not be modified immediately by this command; depending on the
+ parameter you might need to rewrite the table to get the desired effects.
+ That can be done with <a class="link" href="sql-vacuum.html" title="VACUUM">VACUUM
+ FULL</a>, <a class="xref" href="sql-cluster.html" title="CLUSTER"><span class="refentrytitle">CLUSTER</span></a> or one of the forms
+ of <code class="command">ALTER TABLE</code> that forces a table rewrite.
+ For planner related parameters, changes will take effect from the next
+ time the table is locked so currently executing queries will not be
+ affected.
+ </p><p>
+ <code class="literal">SHARE UPDATE EXCLUSIVE</code> lock will be taken for
+ fillfactor, toast and autovacuum storage parameters, as well as the
+ planner parameter <code class="varname">parallel_workers</code>.
+ </p></dd><dt><span class="term"><code class="literal">RESET ( <em class="replaceable"><code>storage_parameter</code></em> [, ... ] )</code></span></dt><dd><p>
+ This form resets one or more storage parameters to their
+ defaults. As with <code class="literal">SET</code>, a table rewrite might be
+ needed to update the table entirely.
+ </p></dd><dt><span class="term"><code class="literal">INHERIT <em class="replaceable"><code>parent_table</code></em></code></span></dt><dd><p>
+ 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 <code class="literal">NOT NULL</code> constraints in the parent
+ then they must also have <code class="literal">NOT NULL</code> constraints in the
+ child.
+ </p><p>
+ There must also be matching child-table constraints for all
+ <code class="literal">CHECK</code> constraints of the parent, except those
+ marked non-inheritable (that is, created with <code class="literal">ALTER TABLE ... ADD CONSTRAINT ... NO INHERIT</code>)
+ in the parent, which are ignored; all child-table constraints matched
+ must not be marked non-inheritable.
+ Currently
+ <code class="literal">UNIQUE</code>, <code class="literal">PRIMARY KEY</code>, and
+ <code class="literal">FOREIGN KEY</code> constraints are not considered, but
+ this might change in the future.
+ </p></dd><dt><span class="term"><code class="literal">NO INHERIT <em class="replaceable"><code>parent_table</code></em></code></span></dt><dd><p>
+ 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.
+ </p></dd><dt><span class="term"><code class="literal">OF <em class="replaceable"><code>type_name</code></em></code></span></dt><dd><p>
+ This form links the table to a composite type as though <code class="command">CREATE
+ TABLE OF</code> had formed it. The table's list of column names and types
+ must precisely match that of the composite type. The table must
+ not inherit from any other table. These restrictions ensure
+ that <code class="command">CREATE TABLE OF</code> would permit an equivalent table
+ definition.
+ </p></dd><dt><span class="term"><code class="literal">NOT OF</code></span></dt><dd><p>
+ This form dissociates a typed table from its type.
+ </p></dd><dt><span class="term"><code class="literal">OWNER TO</code></span></dt><dd><p>
+ This form changes the owner of the table, sequence, view, materialized view,
+ or foreign table to the specified user.
+ </p></dd><dt id="SQL-CREATETABLE-REPLICA-IDENTITY"><span class="term"><code class="literal">REPLICA IDENTITY</code></span></dt><dd><p>
+ This form changes the information which is written to the write-ahead log
+ to identify rows which are updated or deleted. This option has no effect
+ except when logical replication is in use. <code class="literal">DEFAULT</code>
+ (the default for non-system tables) records the
+ old values of the columns of the primary key, if any. <code class="literal">USING INDEX</code>
+ records the old values of the columns covered by the named index, which
+ must be unique, not partial, not deferrable, and include only columns marked
+ <code class="literal">NOT NULL</code>. <code class="literal">FULL</code> records the old values of all columns
+ in the row. <code class="literal">NOTHING</code> records no information about the old row.
+ (This is the default for system tables.)
+ In all cases, no old values are logged unless at least one of the columns
+ that would be logged differs between the old and new versions of the row.
+ </p></dd><dt><span class="term"><code class="literal">RENAME</code></span></dt><dd><p>
+ The <code class="literal">RENAME</code> forms change the name of a table
+ (or an index, sequence, view, materialized view, or foreign table), the
+ name of an individual column in a table, or the name of a constraint of
+ the table. When renaming a constraint that has an underlying index,
+ the index is renamed as well.
+ There is no effect on the stored data.
+ </p></dd><dt><span class="term"><code class="literal">SET SCHEMA</code></span></dt><dd><p>
+ This form moves the table into another schema. Associated indexes,
+ constraints, and sequences owned by table columns are moved as well.
+ </p></dd><dt id="SQL-ALTERTABLE-ATTACH-PARTITION"><span class="term"><code class="literal">ATTACH PARTITION <em class="replaceable"><code>partition_name</code></em> { FOR VALUES <em class="replaceable"><code>partition_bound_spec</code></em> | DEFAULT }</code></span></dt><dd><p>
+ This form attaches an existing table (which might itself be partitioned)
+ as a partition of the target table. The table can be attached
+ as a partition for specific values using <code class="literal">FOR VALUES</code>
+ or as a default partition by using <code class="literal">DEFAULT</code>.
+ For each index in the target table, a corresponding
+ one will be created in the attached table; or, if an equivalent
+ index already exists, it will be attached to the target table's index,
+ as if <code class="command">ALTER INDEX ATTACH PARTITION</code> had been executed.
+ Note that if the existing table is a foreign table, it is currently not
+ allowed to attach the table as a partition of the target table if there
+ are <code class="literal">UNIQUE</code> indexes on the target table. (See also
+ <a class="xref" href="sql-createforeigntable.html" title="CREATE FOREIGN TABLE"><span class="refentrytitle">CREATE FOREIGN TABLE</span></a>.) For each user-defined
+ row-level trigger that exists in the target table, a corresponding one
+ is created in the attached table.
+ </p><p>
+ A partition using <code class="literal">FOR VALUES</code> uses same syntax for
+ <em class="replaceable"><code>partition_bound_spec</code></em> as
+ <a class="xref" href="sql-createtable.html" title="CREATE TABLE"><span class="refentrytitle">CREATE TABLE</span></a>. The partition bound specification
+ must correspond to the partitioning strategy and partition key of the
+ target table. The table to be attached must have all the same columns
+ as the target table and no more; moreover, the column types must also
+ match. Also, it must have all the <code class="literal">NOT NULL</code> and
+ <code class="literal">CHECK</code> constraints of the target table. Currently
+ <code class="literal">FOREIGN KEY</code> constraints are not considered.
+ <code class="literal">UNIQUE</code> and <code class="literal">PRIMARY KEY</code> constraints
+ from the parent table will be created in the partition, if they don't
+ already exist.
+ If any of the <code class="literal">CHECK</code> constraints of the table being
+ attached are marked <code class="literal">NO INHERIT</code>, the command will fail;
+ such constraints must be recreated without the
+ <code class="literal">NO INHERIT</code> clause.
+ </p><p>
+ If the new partition is a regular table, a full table scan is performed
+ to check that existing rows in the table do not violate the partition
+ constraint. It is possible to avoid this scan by adding a valid
+ <code class="literal">CHECK</code> constraint to the table that allows only
+ rows satisfying the desired partition constraint before running this
+ command. The <code class="literal">CHECK</code> constraint will be used to
+ determine that the table need not be scanned to validate the partition
+ constraint. This does not work, however, if any of the partition keys
+ is an expression and the partition does not accept
+ <code class="literal">NULL</code> values. If attaching a list partition that will
+ not accept <code class="literal">NULL</code> values, also add
+ <code class="literal">NOT NULL</code> constraint to the partition key column,
+ unless it's an expression.
+ </p><p>
+ If the new partition is a foreign table, nothing is done to verify
+ that all the rows in the foreign table obey the partition constraint.
+ (See the discussion in <a class="xref" href="sql-createforeigntable.html" title="CREATE FOREIGN TABLE"><span class="refentrytitle">CREATE FOREIGN TABLE</span></a> about
+ constraints on the foreign table.)
+ </p><p>
+ When a table has a default partition, defining a new partition changes
+ the partition constraint for the default partition. The default
+ partition can't contain any rows that would need to be moved to the new
+ partition, and will be scanned to verify that none are present. This
+ scan, like the scan of the new partition, can be avoided if an
+ appropriate <code class="literal">CHECK</code> constraint is present. Also like
+ the scan of the new partition, it is always skipped when the default
+ partition is a foreign table.
+ </p><p>
+ Attaching a partition acquires a
+ <code class="literal">SHARE UPDATE EXCLUSIVE</code> lock on the parent table,
+ in addition to the <code class="literal">ACCESS EXCLUSIVE</code> locks on the table
+ being attached and on the default partition (if any).
+ </p><p>
+ Further locks must also be held on all sub-partitions if the table being
+ attached is itself a partitioned table. Likewise if the default
+ partition is itself a partitioned table. The locking of the
+ sub-partitions can be avoided by adding a <code class="literal">CHECK</code>
+ constraint as described in
+ <a class="xref" href="ddl-partitioning.html#DDL-PARTITIONING-DECLARATIVE-MAINTENANCE" title="5.11.2.2. Partition Maintenance">Section 5.11.2.2</a>.
+ </p></dd><dt><span class="term"><code class="literal">DETACH PARTITION</code> <em class="replaceable"><code>partition_name</code></em></span></dt><dd><p>
+ This form detaches the specified partition of the target table. The detached
+ partition continues to exist as a standalone table, but no longer has any
+ ties to the table from which it was detached. Any indexes that were
+ attached to the target table's indexes are detached. Any triggers that
+ were created as clones of those in the target table are removed.
+ <code class="literal">SHARE</code> lock is obtained on any tables that reference
+ this partitioned table in foreign key constraints.
+ </p></dd></dl></div><p>
+ </p><p>
+ All the forms of ALTER TABLE that act on a single table, except
+ <code class="literal">RENAME</code>, <code class="literal">SET SCHEMA</code>,
+ <code class="literal">ATTACH PARTITION</code>, and
+ <code class="literal">DETACH PARTITION</code> can be combined into
+ a list of multiple alterations to be applied together. 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.
+ </p><p>
+ You must own the table to use <code class="command">ALTER TABLE</code>.
+ To change the schema or tablespace of a table, you must also have
+ <code class="literal">CREATE</code> privilege on the new schema or tablespace.
+ To add the table as a new child of a parent table, you must own the parent
+ table as well. Also, to attach a table as a new partition of the table,
+ you must own the table being attached.
+ To alter the owner, you must also be a direct or indirect member of the new
+ owning role, and that role must have <code class="literal">CREATE</code> 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.)
+ To add a column or alter a column type or use the <code class="literal">OF</code>
+ clause, you must also have <code class="literal">USAGE</code> privilege on the data
+ type.
+ </p></div><div class="refsect1" id="id-1.9.3.35.6"><h2>Parameters</h2><div class="variablelist"><dl class="variablelist"><dt><span class="term"><code class="literal">IF EXISTS</code></span></dt><dd><p>
+ Do not throw an error if the table does not exist. A notice is issued
+ in this case.
+ </p></dd><dt><span class="term"><em class="replaceable"><code>name</code></em></span></dt><dd><p>
+ The name (optionally schema-qualified) of an existing table to
+ alter. If <code class="literal">ONLY</code> is specified before the table name, only
+ that table is altered. If <code class="literal">ONLY</code> is not specified, the table
+ and all its descendant tables (if any) are altered. Optionally,
+ <code class="literal">*</code> can be specified after the table name to explicitly
+ indicate that descendant tables are included.
+ </p></dd><dt><span class="term"><em class="replaceable"><code>column_name</code></em></span></dt><dd><p>
+ Name of a new or existing column.
+ </p></dd><dt><span class="term"><em class="replaceable"><code>new_column_name</code></em></span></dt><dd><p>
+ New name for an existing column.
+ </p></dd><dt><span class="term"><em class="replaceable"><code>new_name</code></em></span></dt><dd><p>
+ New name for the table.
+ </p></dd><dt><span class="term"><em class="replaceable"><code>data_type</code></em></span></dt><dd><p>
+ Data type of the new column, or new data type for an existing
+ column.
+ </p></dd><dt><span class="term"><em class="replaceable"><code>table_constraint</code></em></span></dt><dd><p>
+ New table constraint for the table.
+ </p></dd><dt><span class="term"><em class="replaceable"><code>constraint_name</code></em></span></dt><dd><p>
+ Name of a new or existing constraint.
+ </p></dd><dt><span class="term"><code class="literal">CASCADE</code></span></dt><dd><p>
+ Automatically drop objects that depend on the dropped column
+ or constraint (for example, views referencing the column),
+ and in turn all objects that depend on those objects
+ (see <a class="xref" href="ddl-depend.html" title="5.14. Dependency Tracking">Section 5.14</a>).
+ </p></dd><dt><span class="term"><code class="literal">RESTRICT</code></span></dt><dd><p>
+ Refuse to drop the column or constraint if there are any dependent
+ objects. This is the default behavior.
+ </p></dd><dt><span class="term"><em class="replaceable"><code>trigger_name</code></em></span></dt><dd><p>
+ Name of a single trigger to disable or enable.
+ </p></dd><dt><span class="term"><code class="literal">ALL</code></span></dt><dd><p>
+ Disable or enable all triggers belonging to the table.
+ (This requires superuser privilege if any of the triggers are
+ internally generated constraint triggers such as those that are used
+ to implement foreign key constraints or deferrable uniqueness and
+ exclusion constraints.)
+ </p></dd><dt><span class="term"><code class="literal">USER</code></span></dt><dd><p>
+ Disable or enable all triggers belonging to the table except for
+ internally generated constraint triggers such as those that are used
+ to implement foreign key constraints or deferrable uniqueness and
+ exclusion constraints.
+ </p></dd><dt><span class="term"><em class="replaceable"><code>index_name</code></em></span></dt><dd><p>
+ The name of an existing index.
+ </p></dd><dt><span class="term"><em class="replaceable"><code>storage_parameter</code></em></span></dt><dd><p>
+ The name of a table storage parameter.
+ </p></dd><dt><span class="term"><em class="replaceable"><code>value</code></em></span></dt><dd><p>
+ The new value for a table storage parameter.
+ This might be a number or a word depending on the parameter.
+ </p></dd><dt><span class="term"><em class="replaceable"><code>parent_table</code></em></span></dt><dd><p>
+ A parent table to associate or de-associate with this table.
+ </p></dd><dt><span class="term"><em class="replaceable"><code>new_owner</code></em></span></dt><dd><p>
+ The user name of the new owner of the table.
+ </p></dd><dt><span class="term"><em class="replaceable"><code>new_tablespace</code></em></span></dt><dd><p>
+ The name of the tablespace to which the table will be moved.
+ </p></dd><dt><span class="term"><em class="replaceable"><code>new_schema</code></em></span></dt><dd><p>
+ The name of the schema to which the table will be moved.
+ </p></dd><dt><span class="term"><em class="replaceable"><code>partition_name</code></em></span></dt><dd><p>
+ The name of the table to attach as a new partition or to detach from this table.
+ </p></dd><dt><span class="term"><em class="replaceable"><code>partition_bound_spec</code></em></span></dt><dd><p>
+ The partition bound specification for a new partition. Refer to
+ <a class="xref" href="sql-createtable.html" title="CREATE TABLE"><span class="refentrytitle">CREATE TABLE</span></a> for more details on the syntax of the same.
+ </p></dd></dl></div></div><div class="refsect1" id="SQL-ALTERTABLE-NOTES"><h2>Notes</h2><p>
+ The key word <code class="literal">COLUMN</code> is noise and can be omitted.
+ </p><p>
+ When a column is added with <code class="literal">ADD COLUMN</code> and a
+ non-volatile <code class="literal">DEFAULT</code> is specified, the default is
+ evaluated at the time of the statement and the result stored in the
+ table's metadata. That value will be used for the column for all existing
+ rows. If no <code class="literal">DEFAULT</code> is specified, NULL is used. In
+ neither case is a rewrite of the table required.
+ </p><p>
+ Adding a column with a volatile <code class="literal">DEFAULT</code> or
+ changing the type of an existing column will require the entire table and
+ its indexes to be rewritten. As an exception, when changing the type of an
+ existing column, if the <code class="literal">USING</code> clause does not change
+ the column contents and the old type is either binary coercible to the new
+ type or an unconstrained domain over the new type, a table rewrite is not
+ needed; but any indexes on the affected columns must still be rebuilt.
+ Table and/or index rebuilds may take a
+ significant amount of time for a large table; and will temporarily require
+ as much as double the disk space.
+ </p><p>
+ Adding a <code class="literal">CHECK</code> or <code class="literal">NOT NULL</code> constraint requires
+ scanning the table to verify that existing rows meet the constraint,
+ but does not require a table rewrite.
+ </p><p>
+ Similarly, when attaching a new partition it may be scanned to verify that
+ existing rows meet the partition constraint.
+ </p><p>
+ The main reason for providing the option to specify multiple changes
+ in a single <code class="command">ALTER TABLE</code> is that multiple table scans or
+ rewrites can thereby be combined into a single pass over the table.
+ </p><p>
+ Scanning a large table to verify a new foreign key or check constraint
+ can take a long time, and other updates to the table are locked out
+ until the <code class="command">ALTER TABLE ADD CONSTRAINT</code> command is
+ committed. The main purpose of the <code class="literal">NOT VALID</code>
+ constraint option is to reduce the impact of adding a constraint on
+ concurrent updates. With <code class="literal">NOT VALID</code>,
+ the <code class="command">ADD CONSTRAINT</code> command does not scan the table
+ and can be committed immediately. After that, a <code class="literal">VALIDATE
+ CONSTRAINT</code> command can be issued to verify that existing rows
+ satisfy the constraint. The validation step does not need to lock out
+ concurrent updates, since it knows that other transactions will be
+ enforcing the constraint for rows that they insert or update; only
+ pre-existing rows need to be checked. Hence, validation acquires only
+ a <code class="literal">SHARE UPDATE EXCLUSIVE</code> lock on the table being
+ altered. (If the constraint is a foreign key then a <code class="literal">ROW
+ SHARE</code> lock is also required on the table referenced by the
+ constraint.) In addition to improving concurrency, it can be useful to
+ use <code class="literal">NOT VALID</code> and <code class="literal">VALIDATE
+ CONSTRAINT</code> in cases where the table is known to contain
+ pre-existing violations. Once the constraint is in place, no new
+ violations can be inserted, and the existing problems can be corrected
+ at leisure until <code class="literal">VALIDATE CONSTRAINT</code> finally
+ succeeds.
+ </p><p>
+ The <code class="literal">DROP COLUMN</code> 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.
+ </p><p>
+ To force immediate reclamation of space occupied by a dropped column,
+ you can execute one of the forms of <code class="command">ALTER TABLE</code> that
+ performs a rewrite of the whole table. This results in reconstructing
+ each row with the dropped column replaced by a null value.
+ </p><p>
+ The rewriting forms of <code class="command">ALTER TABLE</code> are not MVCC-safe.
+ After a table rewrite, the table will appear empty to concurrent
+ transactions, if they are using a snapshot taken before the rewrite
+ occurred. See <a class="xref" href="mvcc-caveats.html" title="13.5. Caveats">Section 13.5</a> for more details.
+ </p><p>
+ The <code class="literal">USING</code> option of <code class="literal">SET DATA TYPE</code> 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 <code class="literal">SET DATA TYPE</code>
+ syntax. Because of this flexibility, the <code class="literal">USING</code>
+ 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, <code class="literal">SET DATA TYPE</code> might fail to convert the default even
+ though a <code class="literal">USING</code> clause is supplied. In such cases,
+ drop the default with <code class="literal">DROP DEFAULT</code>, perform the <code class="literal">ALTER
+ TYPE</code>, and then use <code class="literal">SET DEFAULT</code> to add a suitable new
+ default. Similar considerations apply to indexes and constraints involving
+ the column.
+ </p><p>
+ 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. This ensures that the descendants always
+ have columns matching the parent. Similarly, a <code class="literal">CHECK</code>
+ constraint cannot be renamed in the parent without also renaming it in
+ all descendants, so that <code class="literal">CHECK</code> constraints also match
+ between the parent and its descendants. (That restriction does not apply
+ to index-based constraints, however.)
+ Also, because selecting from the parent also selects from its descendants,
+ a constraint on the parent cannot be marked valid unless it is also marked
+ valid for those descendants. In all of these cases, <code class="command">ALTER TABLE
+ ONLY</code> will be rejected.
+ </p><p>
+ A recursive <code class="literal">DROP COLUMN</code> 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 <code class="literal">DROP
+ COLUMN</code> (i.e., <code class="command">ALTER TABLE ONLY ... DROP
+ COLUMN</code>) never removes any descendant columns, but
+ instead marks them as independently defined rather than inherited.
+ A nonrecursive <code class="literal">DROP COLUMN</code> command will fail for a
+ partitioned table, because all partitions of a table must have the same
+ columns as the partitioning root.
+ </p><p>
+ The actions for identity columns (<code class="literal">ADD
+ GENERATED</code>, <code class="literal">SET</code> etc., <code class="literal">DROP
+ IDENTITY</code>), as well as the actions
+ <code class="literal">TRIGGER</code>, <code class="literal">CLUSTER</code>, <code class="literal">OWNER</code>,
+ and <code class="literal">TABLESPACE</code> never recurse to descendant tables;
+ that is, they always act as though <code class="literal">ONLY</code> were specified.
+ Adding a constraint recurses only for <code class="literal">CHECK</code> constraints
+ that are not marked <code class="literal">NO INHERIT</code>.
+ </p><p>
+ Changing any part of a system catalog table is not permitted.
+ </p><p>
+ Refer to <a class="xref" href="sql-createtable.html" title="CREATE TABLE"><span class="refentrytitle">CREATE TABLE</span></a> for a further description of valid
+ parameters. <a class="xref" href="ddl.html" title="Chapter 5. Data Definition">Chapter 5</a> has further information on
+ inheritance.
+ </p></div><div class="refsect1" id="id-1.9.3.35.8"><h2>Examples</h2><p>
+ To add a column of type <code class="type">varchar</code> to a table:
+</p><pre class="programlisting">
+ALTER TABLE distributors ADD COLUMN address varchar(30);
+</pre><p>
+ That will cause all existing rows in the table to be filled with null
+ values for the new column.
+ </p><p>
+ To add a column with a non-null default:
+</p><pre class="programlisting">
+ALTER TABLE measurements
+ ADD COLUMN mtime timestamp with time zone DEFAULT now();
+</pre><p>
+ Existing rows will be filled with the current time as the value of the
+ new column, and then new rows will receive the time of their insertion.
+ </p><p>
+ To add a column and fill it with a value different from the default to
+ be used later:
+</p><pre class="programlisting">
+ALTER TABLE transactions
+ ADD COLUMN status varchar(30) DEFAULT 'old',
+ ALTER COLUMN status SET default 'current';
+</pre><p>
+ Existing rows will be filled with <code class="literal">old</code>, but then
+ the default for subsequent commands will be <code class="literal">current</code>.
+ The effects are the same as if the two sub-commands had been issued
+ in separate <code class="command">ALTER TABLE</code> commands.
+ </p><p>
+ To drop a column from a table:
+</p><pre class="programlisting">
+ALTER TABLE distributors DROP COLUMN address RESTRICT;
+</pre><p>
+ </p><p>
+ To change the types of two existing columns in one operation:
+</p><pre class="programlisting">
+ALTER TABLE distributors
+ ALTER COLUMN address TYPE varchar(80),
+ ALTER COLUMN name TYPE varchar(100);
+</pre><p>
+ </p><p>
+ To change an integer column containing Unix timestamps to <code class="type">timestamp
+ with time zone</code> via a <code class="literal">USING</code> clause:
+</p><pre class="programlisting">
+ALTER TABLE foo
+ ALTER COLUMN foo_timestamp SET DATA TYPE timestamp with time zone
+ USING
+ timestamp with time zone 'epoch' + foo_timestamp * interval '1 second';
+</pre><p>
+ </p><p>
+ The same, when the column has a default expression that won't automatically
+ cast to the new data type:
+</p><pre class="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();
+</pre><p>
+ </p><p>
+ To rename an existing column:
+</p><pre class="programlisting">
+ALTER TABLE distributors RENAME COLUMN address TO city;
+</pre><p>
+ </p><p>
+ To rename an existing table:
+</p><pre class="programlisting">
+ALTER TABLE distributors RENAME TO suppliers;
+</pre><p>
+ </p><p>
+ To rename an existing constraint:
+</p><pre class="programlisting">
+ALTER TABLE distributors RENAME CONSTRAINT zipchk TO zip_check;
+</pre><p>
+ </p><p>
+ To add a not-null constraint to a column:
+</p><pre class="programlisting">
+ALTER TABLE distributors ALTER COLUMN street SET NOT NULL;
+</pre><p>
+ To remove a not-null constraint from a column:
+</p><pre class="programlisting">
+ALTER TABLE distributors ALTER COLUMN street DROP NOT NULL;
+</pre><p>
+ </p><p>
+ To add a check constraint to a table and all its children:
+</p><pre class="programlisting">
+ALTER TABLE distributors ADD CONSTRAINT zipchk CHECK (char_length(zipcode) = 5);
+</pre><p>
+ </p><p>
+ To add a check constraint only to a table and not to its children:
+</p><pre class="programlisting">
+ALTER TABLE distributors ADD CONSTRAINT zipchk CHECK (char_length(zipcode) = 5) NO INHERIT;
+</pre><p>
+ (The check constraint will not be inherited by future children, either.)
+ </p><p>
+ To remove a check constraint from a table and all its children:
+</p><pre class="programlisting">
+ALTER TABLE distributors DROP CONSTRAINT zipchk;
+</pre><p>
+ </p><p>
+ To remove a check constraint from one table only:
+</p><pre class="programlisting">
+ALTER TABLE ONLY distributors DROP CONSTRAINT zipchk;
+</pre><p>
+ (The check constraint remains in place for any child tables.)
+ </p><p>
+ To add a foreign key constraint to a table:
+</p><pre class="programlisting">
+ALTER TABLE distributors ADD CONSTRAINT distfk FOREIGN KEY (address) REFERENCES addresses (address);
+</pre><p>
+ </p><p>
+ To add a foreign key constraint to a table with the least impact on other work:
+</p><pre class="programlisting">
+ALTER TABLE distributors ADD CONSTRAINT distfk FOREIGN KEY (address) REFERENCES addresses (address) NOT VALID;
+ALTER TABLE distributors VALIDATE CONSTRAINT distfk;
+</pre><p>
+ </p><p>
+ To add a (multicolumn) unique constraint to a table:
+</p><pre class="programlisting">
+ALTER TABLE distributors ADD CONSTRAINT dist_id_zipcode_key UNIQUE (dist_id, zipcode);
+</pre><p>
+ </p><p>
+ To add an automatically named primary key constraint to a table, noting
+ that a table can only ever have one primary key:
+</p><pre class="programlisting">
+ALTER TABLE distributors ADD PRIMARY KEY (dist_id);
+</pre><p>
+ </p><p>
+ To move a table to a different tablespace:
+</p><pre class="programlisting">
+ALTER TABLE distributors SET TABLESPACE fasttablespace;
+</pre><p>
+ </p><p>
+ To move a table to a different schema:
+</p><pre class="programlisting">
+ALTER TABLE myschema.distributors SET SCHEMA yourschema;
+</pre><p>
+ </p><p>
+ To recreate a primary key constraint, without blocking updates while the
+ index is rebuilt:
+</p><pre class="programlisting">
+CREATE UNIQUE INDEX CONCURRENTLY dist_id_temp_idx ON distributors (dist_id);
+ALTER TABLE distributors DROP CONSTRAINT distributors_pkey,
+ ADD CONSTRAINT distributors_pkey PRIMARY KEY USING INDEX dist_id_temp_idx;
+</pre><p>
+ To attach a partition to a range-partitioned table:
+</p><pre class="programlisting">
+ALTER TABLE measurement
+ ATTACH PARTITION measurement_y2016m07 FOR VALUES FROM ('2016-07-01') TO ('2016-08-01');
+</pre><p>
+ To attach a partition to a list-partitioned table:
+</p><pre class="programlisting">
+ALTER TABLE cities
+ ATTACH PARTITION cities_ab FOR VALUES IN ('a', 'b');
+</pre><p>
+ To attach a partition to a hash-partitioned table:
+</p><pre class="programlisting">
+ALTER TABLE orders
+ ATTACH PARTITION orders_p4 FOR VALUES WITH (MODULUS 4, REMAINDER 3);
+</pre><p>
+ To attach a default partition to a partitioned table:
+</p><pre class="programlisting">
+ALTER TABLE cities
+ ATTACH PARTITION cities_partdef DEFAULT;
+</pre><p>
+ To detach a partition from a partitioned table:
+</p><pre class="programlisting">
+ALTER TABLE measurement
+ DETACH PARTITION measurement_y2015m12;
+</pre></div><div class="refsect1" id="id-1.9.3.35.9"><h2>Compatibility</h2><p>
+ The forms <code class="literal">ADD</code> (without <code class="literal">USING INDEX</code>),
+ <code class="literal">DROP [COLUMN]</code>, <code class="literal">DROP IDENTITY</code>, <code class="literal">RESTART</code>,
+ <code class="literal">SET DEFAULT</code>, <code class="literal">SET DATA TYPE</code> (without <code class="literal">USING</code>),
+ <code class="literal">SET GENERATED</code>, and <code class="literal">SET <em class="replaceable"><code>sequence_option</code></em></code>
+ conform with the SQL standard. The other forms are
+ <span class="productname">PostgreSQL</span> extensions of the SQL standard.
+ Also, the ability to specify more than one manipulation in a single
+ <code class="command">ALTER TABLE</code> command is an extension.
+ </p><p>
+ <code class="command">ALTER TABLE DROP COLUMN</code> 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.
+ </p></div><div class="refsect1" id="id-1.9.3.35.10"><h2>See Also</h2><span class="simplelist"><a class="xref" href="sql-createtable.html" title="CREATE TABLE"><span class="refentrytitle">CREATE TABLE</span></a></span></div></div><div xmlns="http://www.w3.org/TR/xhtml1/transitional" class="navfooter"><hr></hr><table width="100%" summary="Navigation footer"><tr><td width="40%" align="left"><a accesskey="p" href="sql-altersystem.html" title="ALTER SYSTEM">Prev</a> </td><td width="20%" align="center"><a accesskey="u" href="sql-commands.html" title="SQL Commands">Up</a></td><td width="40%" align="right"> <a accesskey="n" href="sql-altertablespace.html" title="ALTER TABLESPACE">Next</a></td></tr><tr><td width="40%" align="left" valign="top">ALTER SYSTEM </td><td width="20%" align="center"><a accesskey="h" href="index.html" title="PostgreSQL 13.4 Documentation">Home</a></td><td width="40%" align="right" valign="top"> ALTER TABLESPACE</td></tr></table></div></body></html> \ No newline at end of file