If IF EXISTS is specified and the column does not exist, no error is thrown. of multiple alterations to apply in parallel. This form moves the table into another schema. If ONLY is (These restrictions enforce that altering the Chapter 5 has further information on (This is the default for system tables.) the same way as if the index had been built by a regular Renaming a Table. the column's default value (NULL if no DEFAULT clause is specified). the forms of ALTER TABLE that
This automatically cast to the new data type: To add a not-null constraint to a column: To remove a not-null constraint from a column: To add a check constraint to a table and all its children: To remove a check constraint from a table and all its In all other cases, this is a fast operation. Because of this flexibility, the USING 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.
(These restrictions enforce that altering the owner doesn't do anything you couldn't do by dropping and recreating the table. Currently, the only defined per-attribute options are
Note that this is not equivalent to ADD COLUMN oid oid; that would add a normal column that happened to be named oid, not a system column. The new value for a table storage parameter. This form adds a new constraint to a table using the ADD PRIMARY KEY or ADD UNIQUE command. altered. MAIN is for inline, compressible data. The name (optionally schema-qualified) of an existing table to alter. The name (optionally schema-qualified) of an existing The rewriting forms of ALTER TABLE are not MVCC-safe. Adding or removing a system oid You must own the table to use ALTER TABLE.
Disable or enable all triggers belonging to the table. enabling internally generated constraint triggers requires Any constraints associated with the domain are moved into the new schema as well.
value of -0.5 implies that each value appears twice on the
from initial creation is that you can defer validation to
Subsequent insert and update operations in the table will store a null value for the column. Adding or removing a system oid column also requires rewriting the entire table. compute the new column value from the old; if omitted, the Instead use the SET WITH OIDS and SET WITHOUT OIDS forms to change OID status.
This results in reconstructing each row with the dropped column replaced by a null value. Currently only foreign key constraints may be altered. If single pass over the table. This form removes the most recently used CLUSTER index specification from the
See also CREATE TABLESPACE. This is the default behavior. To This form changes the owner of the domain to the specified user. If ONLY is not specified, Similar To force immediate reclamation of space occupied by a dropped column, you can execute one of the forms of ALTER TABLE that performs a rewrite of the whole table. renamed to match the constraint name. single trigger specified by name, or all triggers on the 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.
cases, this is a fast operation. below. validated by using the VALIDATE A parent table to associate or de-associate with this table.
ENABLE REPLICA will only fire if Simply enabled triggers will fire when the replication role rows already in the table to change.
The main purpose of the NOT VALID constraint option is to reduce the impact of adding a constraint on concurrent updates. There is no effect on the stored data. CREATE privilege on the new schema. large tables, since only one pass over the table need be
Defaults can also be This form changes the type of a column of a table. (If the constraint is a foreign key then a ROW SHARE lock is also required on the table referenced by the constraint.) OF would permit an equivalent table definition. and EXTENDED is for external, This form adds a new constraint to a domain using the same syntax as CREATE DOMAIN. immediately by this command; depending on the parameter you Also, it must be a b-tree index with default The name of the tablespace to which the table will be
Copyright © 1996-2020 The PostgreSQL Global Development Group. This is exactly equivalent to DROP COLUMN oid RESTRICT, except that it will not complain if there is already no oid column. view to the specified user. 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). to verify the column(s) contain no nulls.
Note that system catalogs are not moved by this command, use ALTER DATABASE or explicit ALTER TABLE invocations instead if desired. extension. It does nothing clustering. is the default for the new column type. refer to Section 14.2. The optional Thus, dropping a table will store a null value for the column. SET NOT NULL when the column See Notes below for more information about using the NOT VALID option. table columns are moved as well. Triggers configured as ENABLE REPLICA will only fire if the session is in "replica" mode, and triggers configured as ENABLE ALWAYS will fire regardless of the current replication mode. by multiplying the estimated table size by the absolute This form selects the default index for future CLUSTER operations. It does not This configuration is ignored for ON SELECT rules, which are always applied in order to keep views working even if the current session is in a non-default replication role.
Name of an existing constraint to drop or rename. This form validates a constraint previously added as NOT VALID, that is, it verifies that all values in table columns of the domain type satisfy the specified constraint. column is quick but it will not immediately reduce the on-disk These forms change whether a column is marked to allow null values or to reject null values. Section 14.2. The RENAME forms change the
optional COLLATE clause specifies
recurse only for CHECK constraints, and This form drops the specified constraint on a table. There must also be matching child-table constraints for Refuse to drop the constraint if there are any dependent objects. All the columns of to drop the only column of a table, leaving a zero-column table. In this case a notice is issued instead. A notice is issued in this case. SELECT rules, which are always applied in order to to be rewritten. by scanning the table to ensure there are no unmatched This form resets one or more storage parameters to their defaults. correct pre-existing errors while preventing new A USING clause must be provided if there is no implicit or assignment cast from old to new type. Disable or enable all triggers belonging to the table The user name of the new owner of the domain. Currently, the only defined per-attribute options are n_distinct and n_distinct_inherited, which override the number-of-distinct-values estimates made by subsequent ANALYZE operations. average. Adding a column with a non-null default or changing the type
For example, it is possible to add several columns and/or alter the type of several columns in a single command. specified and the column does not exist, no error is Automatically drop objects that depend on the dropped If PRIMARY KEY is specified, and the index's columns are not already marked NOT NULL, then this command will attempt to do ALTER COLUMN SET NOT NULL against each such column. occurs. This form changes one or more storage parameters for the constraint will be named the same as the index. See the example below. constraint using this syntax. The rewriting forms of ALTER TABLE