diff options
Diffstat (limited to 'doc/src/sgml/html/sql-altertable.html')
| -rw-r--r-- | doc/src/sgml/html/sql-altertable.html | 1094 |
1 files changed, 1094 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..9c23a3c --- /dev/null +++ b/doc/src/sgml/html/sql-altertable.html @@ -0,0 +1,1094 @@ +<?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 Vsnapshot" /><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 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 15.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 /></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> [ CONCURRENTLY | FINALIZE ] + +<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 } + ALTER [ COLUMN ] <em class="replaceable"><code>column_name</code></em> SET COMPRESSION <em class="replaceable"><code>compression_method</code></em> + 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 ACCESS METHOD <em class="replaceable"><code>new_access_method</code></em> + 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_ROLE | 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 [ NULLS [ NOT ] DISTINCT ] <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 [ NULLS [ NOT ] DISTINCT ] ( <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 } ] + +<span class="phrase"><em class="replaceable"><code>referential_action</code></em> in a <code class="literal">FOREIGN KEY</code>/<code class="literal">REFERENCES</code> constraint is:</span> + +{ NO ACTION | RESTRICT | CASCADE | SET NULL [ ( <em class="replaceable"><code>column_name</code></em> [, ... ] ) ] | SET DEFAULT [ ( <em class="replaceable"><code>column_name</code></em> [, ... ] ) ] } +</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="link" href="sql-createtable.html" title="CREATE TABLE"><code class="command">CREATE TABLE</code></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><p> + When this form is used, the column's statistics are removed, + so running <a class="link" href="sql-analyze.html" title="ANALYZE"><code class="command">ANALYZE</code></a> + on the table afterwards is recommended. + </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="link" href="sql-createtable.html" title="CREATE TABLE"><code class="command">CREATE TABLE</code></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="link" href="sql-altersequence.html" title="ALTER SEQUENCE"><code class="command">ALTER SEQUENCE</code></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="link" href="sql-analyze.html" title="ANALYZE"><code class="command">ANALYZE</code></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="link" href="sql-analyze.html" title="ANALYZE"><code class="command">ANALYZE</code></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="73.2. TOAST">Section 73.2</a> for more information. + </p></dd><dt><span class="term"> + <code class="literal">SET COMPRESSION <em class="replaceable"><code>compression_method</code></em></code> + </span></dt><dd><p> + This form sets the compression method for a column, determining how + values inserted in future will be compressed (if the storage mode + permits compression at all). + This does not cause the table to be rewritten, so existing data may still + be compressed with other compression methods. If the table is restored + with <span class="application">pg_restore</span>, then all values are rewritten + with the configured compression method. + However, when data is inserted from another relation (for example, + by <code class="command">INSERT ... SELECT</code>), values from the source table are + not necessarily detoasted, so any previously compressed data may retain + its existing compression method, rather than being recompressed with the + compression method of the target column. + The supported compression + methods are <code class="literal">pglz</code> and <code class="literal">lz4</code>. + (<code class="literal">lz4</code> is available only if <code class="option">--with-lz4</code> + was used when building <span class="productname">PostgreSQL</span>.) In + addition, <em class="replaceable"><code>compression_method</code></em> + can be <code class="literal">default</code>, which selects the default behavior of + consulting the <a class="xref" href="runtime-config-client.html#GUC-DEFAULT-TOAST-COMPRESSION">default_toast_compression</a> setting + at the time of data insertion to determine the method to use. + </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="link" href="sql-createtable.html" title="CREATE TABLE"><code class="command">CREATE TABLE</code></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="link" href="sql-createtable.html" title="CREATE TABLE"><code class="command">CREATE TABLE</code></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; so 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> + When this command is applied to a partitioned table, the states of + corresponding clone triggers in the partitions are updated too, + unless <code class="literal">ONLY</code> is specified. + </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="link" href="sql-createpolicy.html" title="CREATE POLICY"><code class="command">CREATE POLICY</code></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="link" href="sql-createpolicy.html" title="CREATE POLICY"><code class="command">CREATE POLICY</code></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="link" href="sql-cluster.html" title="CLUSTER"><code class="command">CLUSTER</code></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="link" href="sql-cluster.html" title="CLUSTER"><code class="command">CLUSTER</code></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 ACCESS METHOD</code></span></dt><dd><p> + This form changes the access method of the table by rewriting it. See + <a class="xref" href="tableam.html" title="Chapter 63. Table Access Method Interface Definition">Chapter 63</a> for more information. + </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="link" href="sql-createtablespace.html" title="CREATE TABLESPACE"><code class="command">CREATE TABLESPACE</code></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><p> + This also changes the persistence of any sequences linked to the table + (for identity or serial columns). However, it is also possible to + change the persistence of such sequences separately. + </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="link" href="sql-createtable.html" title="CREATE TABLE"><code class="command">CREATE TABLE</code></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"><code class="command">VACUUM + FULL</code></a>, <a class="link" href="sql-cluster.html" title="CLUSTER"><code class="command">CLUSTER</code></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-ALTERTABLE-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. + In most cases, the old value of each column is only logged if it differs + from the new value; however, if the old value is stored externally, it is + always logged regardless of whether it changed. + This option has no effect except when logical replication is in use. + </p><div class="variablelist"><dl class="variablelist"><dt><span class="term"><code class="literal">DEFAULT</code></span></dt><dd><p> + Records the old values of the columns of the primary key, if any. + This is the default for non-system tables. + </p></dd><dt><span class="term"><code class="literal">USING INDEX <em class="replaceable"><code>index_name</code></em></code></span></dt><dd><p> + Records the old values of the columns covered by the named index, + that must be unique, not partial, not deferrable, and include only + columns marked <code class="literal">NOT NULL</code>. If this index is + dropped, the behavior is the same as <code class="literal">NOTHING</code>. + </p></dd><dt><span class="term"><code class="literal">FULL</code></span></dt><dd><p> + Records the old values of all columns in the row. + </p></dd><dt><span class="term"><code class="literal">NOTHING</code></span></dt><dd><p> + Records no information about the old row. This is the default for + system tables. + </p></dd></dl></div></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="link" href="sql-createtable.html" title="CREATE TABLE"><code class="command">CREATE TABLE</code></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 a + <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 id="SQL-ALTERTABLE-DETACH-PARTITION"><span class="term"><code class="literal">DETACH PARTITION <em class="replaceable"><code>partition_name</code></em> [ CONCURRENTLY | FINALIZE ]</code></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><p> + If <code class="literal">CONCURRENTLY</code> is specified, it runs using a reduced + lock level to avoid blocking other sessions that might be accessing the + partitioned table. In this mode, two transactions are used internally. + During the first transaction, a <code class="literal">SHARE UPDATE EXCLUSIVE</code> + lock is taken on both parent table and partition, and the partition is + marked as undergoing detach; at that point, the transaction is committed + and all other transactions using the partitioned table are waited for. + Once all those transactions have completed, the second transaction + acquires <code class="literal">SHARE UPDATE EXCLUSIVE</code> on the partitioned + table and <code class="literal">ACCESS EXCLUSIVE</code> on the partition, + and the detach process completes. A <code class="literal">CHECK</code> constraint + that duplicates the partition constraint is added to the partition. + <code class="literal">CONCURRENTLY</code> cannot be run in a transaction block and + is not allowed if the partitioned table contains a default partition. + </p><p> + If <code class="literal">FINALIZE</code> is specified, a previous + <code class="literal">DETACH CONCURRENTLY</code> invocation that was canceled or + interrupted is completed. + At most one partition in a partitioned table can be pending detach at + a time. + </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_access_method</code></em></span></dt><dd><p> + The name of the access method to which the table will be converted. + </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. However, indexes must always be rebuilt unless the system can + verify that the new index would be logically equivalent to the existing + one. For example, if the collation for a column has been changed, an index + rebuild is always required because the new sort order might be different. + However, in the absence of a collation change, a column can be changed + from <code class="type">text</code> to <code class="type">varchar</code> (or vice versa) without + rebuilding the indexes because these data types sort identically. + 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.6. Caveats">Section 13.6</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">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. + Actions affecting trigger states recurse to partitions of partitioned + tables (unless <code class="literal">ONLY</code> is specified), but never to + traditional-inheritance descendants. + 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 class="navfooter"><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 15.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 |