PostgreSQL Chinese Document

ALTER TABLE

Name

Alter table -- modify the definition of a table

Synopsis

ALTER TABLE [ ONLY ] name [ * ]    action [, ... ]ALTER TABLE [ ONLY ] name [ * ]    RENAME [ COLUMN ] column TO new_columnALTER TABLE name    RENAME TO new_nameALTER TABLE name    SET SCHEMA new_schemawhere action is one of:    ADD [ COLUMN ] column type [ column_constraint [ ... ] ]    DROP [ COLUMN ] [ IF EXISTS ] column [ RESTRICT | CASCADE ]    ALTER [ COLUMN ] column [ SET DATA ] TYPE type [ USING expression ]    ALTER [ COLUMN ] column SET DEFAULT expression    ALTER [ COLUMN ] column DROP DEFAULT    ALTER [ COLUMN ] column { SET | DROP } NOT NULL    ALTER [ COLUMN ] column SET STATISTICS integer    ALTER [ COLUMN ] column SET ( attribute_option = value [, ... ] )    ALTER [ COLUMN ] column RESET ( attribute_option [, ... ] )    ALTER [ COLUMN ] column SET STORAGE { PLAIN | EXTERNAL | EXTENDED | MAIN }    ADD table_constraint    DROP CONSTRAINT [ IF EXISTS ]  constraint_name [ RESTRICT | CASCADE ]    DISABLE TRIGGER [ trigger_name | ALL | USER ]    ENABLE TRIGGER [ trigger_name | ALL | USER ]    ENABLE REPLICA TRIGGER trigger_name    ENABLE ALWAYS TRIGGER trigger_name    DISABLE RULE rewrite_rule_name    ENABLE RULE rewrite_rule_name    ENABLE REPLICA RULE rewrite_rule_name    ENABLE ALWAYS RULE rewrite_rule_name    CLUSTER ON index_name    SET WITHOUT CLUSTER    SET WITH OIDS    SET WITHOUT OIDS    SET ( storage_parameter = value [, ... ] )    RESET ( storage_parameter [, ... ] )    INHERIT parent_table    NO INHERIT parent_table    OWNER TO new_owner    SET TABLESPACE new_tablespace

Description

ALTER TABLEChange the definition of an existing table. It has several seed forms:

Add Column

It uses the same syntax as create table to add a new field to the table.

Drop column [if exists]

It deletes a field from the table. Indexes and table constraints related to this field are also automatically deleted. If any object outside the table depends on this field, it must be saidCascadeFor example, foreign key reference and view.

Set Data Type

It changes the type of a field in the table. The indexes involved in this field and simple table constraints will be automatically converted to use the new field type by re-analyzing the original expression. OptionalUsingClause declares how to calculate the new field value from the old field value. If it is omitted, the default conversion is to assign a value from the old type to the new type. If there is no implicit or assignment conversion from the old data type to the new type, you must provideUsing

Set/ Drop default

Set or delete the default value for a field in this form. Note that the default value is only applicable to subsequentInsertCommand; they do not modify the existing rows in the table. You can also create a default for the view.On insertInsert rules before applicationInsertStatement.

Set/ Drop not null

It modifies whether a field allows null or rejects null values. If the table contains a non-null field, you can onlySet not null

Set statistics

Set ( Attribute_option= Value[,...])
Reset ( Attribute_option[,...])

It sets or resets each attribute option. Currently, each attribute option that is uniquely defined isN_distinctAndN_distinct_inherited, These overwrites are the estimation of the number of clear values made by subsequent analyze operations.N_distinctAffects the statistical value of the table, whileN_distinct_inheritedStatistics on affected tables and their inherited sub-tables.
When set to a positive value,AnalyzeIt is assumed that the column accurately contains the specified number of non-null values. When it is set to a negative value greater than or equal to-1,AnalyzeIt is assumed that the number of different non-null values in the column is linear in the table size. The exact statistics are calculated by multiplying the table size estimated by the absolute value of the given number. For example, value-1 means that all values in this column are different. Value-0.5 means that each value appears twice on average. This is very effective when the table size changes over time, although the multiplication operation of the number of rows in the table is not executed before the query planning time,
Declare a zero value to restore the normal to estimate the number of different values. For more information about using the PostgreSQL query optimizer for Statistics, see section 14.2.

Set Storage

It sets the storage mode for a field. This setting controls whether the field is saved inline or saved in an affiliated table, and whether the data is compressed.PlainMust be used for a fixed length value (for exampleIntegerAnd is inline and not compressed.MainUsed for inline and compressed data.ExternalUsed for external storage and non-compression of data,ExtendedUsed to compress external data.Extended
Is most of the supported non-PlainDefault Value of the stored data. UseExternalTextAndByteaThe substring operation on the field is faster, but the cost is to increase the storage space. Note:Set StorageIt does not change anything on the table itself, but is a recommended policy for future table operations. See section
54.2 get more information.

Add Table_constraint

It adds a new constraint to the table and uses the same syntax as create table.

Drop constraint [if exists]

It deletes the constraints on a table. IfIf existsIf it has been declared and has no constraints, no error will be thrown. In this case, an announcement is published.

Disable/ Enable [replica | always] trigger

It closes or opens the trigger that belongs to the table. A closed trigger is still known by the system, but will not be executed when a trigger event occurs. For a delayed trigger, the open status is checked when the event occurs, rather than when the function is actually executed. You can open or close any trigger by specifying the name, or all the triggers on the table, or only user triggers (this option removes the triggers used to implement foreign key constraints ). To enable or disable the constraint trigger, the super user permission is required. Be careful when doing so, because if the trigger is not executed, the data integrity guaranteed by the constraint cannot be ensured. The trigger startup principle is also affected by the configuration variable session_replication_role.
A simple trigger is started when the copy task is "initial" (default) or "local. ConfiguredEnable replicaThe trigger will be started only when the session is in "Replica" mode and configuredEnable alwaysThe trigger will be started, whether or not it is in the current replication mode.

Disable/ Enable [replica | always] Rule

It is configured as a table rewrite rule. A sound rule is still known to the system, but it is not applied during query rewriting. The syntax is to close/start a trigger. This configuration pairOn selectRules are negligible and often used to maintain view work, even if the current session is in a non-default copy role.

Cluster

It selects the default index for future cluster operations. The table is not actually clustered again.

Set without Cluster

It deletes the most common Cluster Index specifications from the table. This affects the clustering operations that do not declare indexes in the future.

Set with oids

It addsOidSystem Column (see
Section 5.4 ). If the table already has oids, nothing is done.

Note that this is not equivalentAdd column OID; Add a name that happens to beOidInstead of system columns.

Set without oids

It deletes the oId system field from the table. It is exactly the same as drop column OID restrict, except that no error is reported if there is no OID field in the table.

Set ( Storage_parameter= Value[,...])

It changes one or more storage parameters for the table. See
Storage ParametersObtain detailed information about available parameters. Note that the table content will not be quickly adjusted using this command; depending on this parameter, you may need to override this table to achieve the desired effect. This can be rewritten through the cluster or the love value table.ALTER TABLEForm.

Note:AlthoughCreate TableAllowOidsInWith (Storage_parameter)Declare in semantics,ALTER TABLEBut notOidsA storage parameter. Conversely,
To useSet with oidsAndSet without oidsTo change the oId status.

Reset ( Storage_parameter[,...])

It resets one or more storage parameters of a table. AndSetSimilarly, if the parameters are different, you may need to rewrite the table to obtain the expected results.

Inherit Parent_table

It adds the target table as a new sub-table of the specified parent table. Then, the query on the parent table will contain records in the target table. To be added as a sub-table, the target table must already contain all the same fields as the parent table (other fields can also be included). The data types of these fields must match, and if the fields of the parent table haveNot nullThe corresponding fields of the sub-table must also haveNot nullConstraints.

For all parent tablesCheckThe constraint must match the constraint of the sub-table at the same time. CurrentUnique,Primary Key,Foreign keyConstraints are not taken into account, but may change in the future.

No inherit Parent_table

In this way, the target table is deleted from the child table list of the specified parent table. In this way, the query on the parent table will no longer contain records in the target table.

Owner

In this form, the owner of tables, sequences, and views is changed to the specified user.

Set tablespace

. It changes the tablespace of the table to declare the tablespace, and deletes the index of the table-related data file table for the new tablespace. If yes, it does not need to be deleted.Set tablespaceCommand to delete them. See create tablespace again.

Rename

RenameForm to change the name of a table (or index, sequence, view), or the name of an independent field in the table. They have no impact on the stored data.

Set Schema

In this form, the table is moved to another mode. Related indexes, constraints, and sequences are all moved.

BesidesRenameAndSet SchemaAll other actions can be bound to a multiple modification list for parallel use. For example, you can add several fields and/or modify the types of several fields in a command. This is especially useful for large tables, because you only need to process the table once.

To useALTER TABLEYou must own the table. To modify the schema of a table, you must also haveCreatePermission. To add the table as a new sub-table of the parent table, you must have the parent table at the same time. To modify the owner, you must also be a direct or indirect member of all new roles, and the member must haveCreatePermission. These restrictions force the modification of the owner not to do anything that cannot be done by deleting or recreating the table. However, a Super User can modify the ownership of any table in any way.

Parameters

Name

Name of the existing table to be modified (you can modify it in the Schema mode ). If you declareOnlyOnly the table is changed. If not statedOnly, The table and all its sub-tables will be changed.

Column

Existing or new Field Names

New_column

New name of an existing field

New_name

New table name

Type

The type of the new field or the new type of the existing field.

Table_constraint

New Table constraint Definition

Constraint_name

Name of the existing constraint to be deleted

Cascade

Cascading deletion relies on objects dependent on fields or constraints (for example, views that reference this field)

Restrict

If a field or constraint has any dependent object, the field is not deleted. This is the default action.

Trigger_name

Name of a single trigger to be opened or closed

All

Enable or disable all triggers that belong to the table. (If any trigger generates a constraint trigger internally, this requires superuser permissions, such as the uniqueness and exclusion constraints used to execute foreign key constraints or to postpone .)

User

Close or start all triggers that belong to the table. (If any trigger generates a constraint trigger internally, this requires superuser permissions, such as the uniqueness and exclusion constraints used to execute foreign key constraints or to postpone .)

Index_name

Name of the index on the table to be marked as a cluster

Storage_parameter

Table Storage parameter name

Value

The new value of the storage parameter of the table, which may be a number or word based on different parameters.

Parent_table

Parent table to be created/unassociated with the table

New_owner

Username of the new owner of the table

New_tablespace

The name of the tablespace to be moved.

New_schema

Name of the New Mode to which the table will go

Note:

ColumnKeywords are redundant and can be omitted.

If you useAdd ColumnIf a field is added, all existing rows in the table are initialized to the default value of this field (if no field is declaredDefaultClause, which is null ).

Add a column by default without null or change the type of an original column. The entire table and index to be overwritten are required. This may take a lot of time for large tables; this will temporarily require twice the disk space. Add or delete a systemOidYou also need to override the entire table.

Add oneCheckOrNot nullThe constraints scan the table to ensure that the existing rows meet the constraints.

Provided inALTER TABLEThe main reason for declaring multiple modifications in the table is that multiple scans and rewriting of the table can be combined into one step.

Drop ColumnThe command does not physically delete a field, but simply marks it as invisible to SQL operations. Insert and update the table to store a null value in this field. Therefore, deleting a field is fast, but it does not immediately release the table space on the disk, because the space occupied by the Deleted field has not been recycled. These spaces will be reclaimed as the existing rows are updated. (Deleting a systemOidThese statements are not applied when columns are created; this is a direct rewrite .)

Set Data TypeThe feature that requires rewriting of the entire table is sometimes an advantage, because the rewriting process eliminates useless space in the table. For example, to immediately recycle the space occupied by a Deleted field, the fastest way is:

ALTER TABLE table ALTER COLUMN anycol TYPE anytype;

HereAnycolIs any field that exists in the table, andAnytypeIs the same type as the original type of the field. The result is that there is no visible semantic change in the table, but this order is forced to overwrite, so that the data that is no longer used is deleted.

Set Data TypeOfUsingThe option can actually declare any expression that involves the old value of the row; that is, it can reference fields other than the fields being converted. In this way, you can useSet Data TypeThe syntax performs a very universal conversion. Because of this flexibility,UsingThe expression does not act on the default value of the field (if any); the result may not be a constant expression required by the default expression. This means that if there is no implicit or assignment from the old type to the new type
If the conversion existsUsingClause,Set Data TypeYou may not be able to convert the default value to a new type. In this case, you should useDrop defaultDelete the default value first. RunSet Data TypeAnd then useSet DefaultAdd a new default value. Similar considerations apply to indexes and constraints related to this field.

If the table has any post-representation, adding, renaming, and modifying the type of a field in the parent table will not be allowed if the child table does not make the same modification. That is,Alter table onlyWill be rejected. This ensures that the fields always match with the parent table.

RecursionDrop ColumnThe Operation will delete a subsequent field only when it means it does not relay this field from any other parent table and has never defined this field independently. A non-recursiveDrop Column(That isAlter table only... drop Column) It never deletes any descendant fields, but marks them as defined independently (rather than inherited ).

Trigger,Cluster,Owner,TablespaceBehavior will never be represented after recursion; that is to say, their behavior is like always declaredOnlySame. Add a constraintCheckConstraint recursion.

You cannot change any part of the system table structure.

Refer to the create table section for more valid parameter descriptions. Chapter 5 provides more information about inheritance.

Examples

AddVarcharColumn:

ALTER TABLE distributors ADD COLUMN address varchar(30);

Delete a field from the table:

ALTER TABLE distributors DROP COLUMN address RESTRICT;

Modify the types of two existing fields in one operation:

ALTER TABLE distributors    ALTER COLUMN address TYPE varchar(80),    ALTER COLUMN name TYPE varchar(100);

Use oneUsingTo convert an integer field containing a Unix timestampTimestamp with Time ZoneField:

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';

Similarly, when a field does not automatically convert to a new default expression:

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();

Rename existing fields:

ALTER TABLE distributors RENAME COLUMN address TO city;

Change the name of an existing table:

ALTER TABLE distributors RENAME TO suppliers;

Add a non-empty constraint to a field:

ALTER TABLE distributors ALTER COLUMN street SET NOT NULL;

Delete A non-empty constraint from a field:

ALTER TABLE distributors ALTER COLUMN street DROP NOT NULL;

Add a check constraint to a table:

ALTER TABLE distributors ADD CONSTRAINT zipchk CHECK (char_length(zipcode) = 5);

Delete the invigilation constraints of a table and all its sub-tables

ALTER TABLE distributors DROP CONSTRAINT zipchk;

To delete a check constraint from a table, you only need:

ALTER TABLE ONLY distributors DROP CONSTRAINT zipchk;

(This check constraint only exists in the subtable .)

Add a foreign key constraint to the table:

ALTER TABLE distributors ADD CONSTRAINT distfk FOREIGN KEY (address) REFERENCES addresses (address) MATCH FULL;

Add a unique (Multi-field) constraint to the table:

ALTER TABLE distributors ADD CONSTRAINT dist_id_zipcode_key UNIQUE (dist_id, zipcode);

Add an automatically named primary key constraint to a table. Note that a table can only have one primary key:

ALTER TABLE distributors ADD PRIMARY KEY (dist_id);

Move the table to another tablespace:

ALTER TABLE distributors SET TABLESPACE fasttablespace;

Move the table to another mode:

ALTER TABLE myschema.distributors SET SCHEMA yourschema;

Compatibility

Add,Drop,
Set DefaultThe format is compatible with the SQL standard. Other forms are PostgreSQL's SQL standard extensions. Also, inALTER TABLEMultiple Operations declared in the command are also extensions.

Alter table drop ColumnIt can be used to delete a unique field in a table and leave a table with zero fields. This is an extension of SQL. It does not allow zero-field tables.