ALTER FOREIGN TABLE

Modify the definition of appearance.

Summary

ALTER FOREIGN TABLE [ IF EXISTS ] name
    action [, ... ]
ALTER FOREIGN TABLE [ IF EXISTS ] name
    RENAME [ COLUMN ] column_name TO new_column_name
ALTER FOREIGN TABLE [ IF EXISTS ] name
    RENAME TO new_name
ALTER FOREIGN TABLE [ IF EXISTS ] name
    SET SCHEMA new_schema

The action is as follows:

    ADD [ COLUMN ] column_name column_type [ COLLATE collation ] [ column_constraint [ ... ] ]
    DROP [ COLUMN ] [ IF EXISTS ] column_name [ RESTRICT | CASCADE ]
    ALTER [ COLUMN ] column_name [ SET DATA ] TYPE data_type
    ALTER [ COLUMN ] column_name SET DEFAULT expression
    ALTER [ COLUMN ] column_name DROP DEFAULT
    ALTER [ COLUMN ] column_name { SET | DROP } NOT NULL
    ALTER [ COLUMN ] column_name SET STATISTICS integer
    ALTER [ COLUMN ] column_name SET ( attribute_option = value [, ... ] )
    ALTER [ COLUMN ] column_name RESET ( attribute_option [, ... ] )
    ALTER [ COLUMN ] column_name OPTIONS ( [ ADD | SET | DROP ] option ['value'] [, ... ])
    DISABLE TRIGGER [ trigger_name | ALL | USER ]
    ENABLE TRIGGER [ trigger_name | ALL | USER ]
    ENABLE REPLICA TRIGGER trigger_name
    ENABLE ALWAYS TRIGGER trigger_name
    OWNER TO new_owner
    OPTIONS ( [ ADD | SET | DROP ] option ['value'] [, ... ] )

describe

ALTER FOREIGN TABLE Modifies the definition of an existing appearance. The command takes the following forms:

ADD COLUMN

This method adds a new column to the surface, using the same syntax as CREATE FOREIGN TABLE. This is not like adding a new column to the usual table. It actually does not do anything at the storage level, but just defines some new columns to the current accessed exterior.

DROP COLUMN [ IF EXISTS ]

This form removes a column from the table. If there are objects other than the table, such as the view depends on this column, the CASCADE keyword must be specified. If the IF EXISTS keyword is specified and this column does not exist, the YMatrix database will only generate a reminder instead of throwing an error.

IF EXISTS

If the IF EXISTS keyword is specified and the appearance does not exist, the YMatrix database will only generate a reminder instead of throwing an error.

SET DATA TYPE

This form modifies the type of a column in the exterior.

SET/DROP DEFAULT

This form sets or deletes the default value of the column. The default value will only be applied to the subsequent INSERT or UPDATE commands; it will not trigger modifications to those rows that already exist in the table.

SET/DROP NOT NULL

Flags a column allows or does not allow null values.

SET STATISTICS

This form sets the statistical information collection target for each column for the subsequent ANALYZE operation.

SET ( attribute_option = value [, ...] ] )

RESET ( attribute_option [, ... ] )

This form sets or resets the options for each property.

DISABLE/ENABLE [ REPLICA | ALWAYS ] TRIGGER

These forms are configured to trigger those that belong to the appearance.

OWNER

This form modifies the owner of the appearance to the specified user.

RENAME

RENAME Modify the name of an appearance or the name of an individual column in the appearance.

SET SCHEMA

This form moves the appearance to other modes.

OPTIONS ( [ ADD | SET | DROP ] option ['value'] [, ... ] )

Modify options for appearance. ADD, SET, and DROP specify the action to perform. If the operation is not specified, the default operation is ADD. The option name must be unique. YMatrix databases use external data wrappers to verify names and values.

You can write all operations except RENAME and SET SCHEMA in a list to allow the YMatrix database to apply these modifications in parallel. For example, you can add multiple columns or modify the attributes of multiple columns in one command.

You must have this table to use the ALTER FOREIGN TABLE operation. In order to modify the appearance pattern, you must also have CREATE permissions in the new mode. To modify the owner, you must be a direct or indirect member of the new role, and that role must have CREATE permissions in the table mode. These restrictions mandate that changing the owner by deleting and recreating the table will not do anything. However, superusers can change ownership of any table anyway. In order to add or modify a column type, you must have USAGE permissions on the data type.

Parameters

name

The name of the existing external table to be changed (probably schema-qualified).

column_name

Name of the new or existing column.

new_column_name

New name of the existing column

new_name

New name for appearance.

data_type

The data type of the new column, or the new data type of the existing column.

CASCADE

Automatically delete objects that depend on the column to be deleted (for example, views associated to the column).

RESTRICT

Reject to delete columns with any object dependencies. This is the default performance.

trigger_name

The single trigger name to enable or disable.

ALL

Disable or enable all triggers belonging to the appearance. (Superuser permission is required if any trigger is an internally generated trigger. The core system will not add such triggers to the exterior, but additional code can do so.)

USER

Disable or enable all triggers belonging to the exterior except for triggers generated internally.

new_owner

Username of the new owner of the appearance.

new_schema

The name of the pattern to which the appearance will be moved

Example

Flag a column as NOT NULL:

ALTER FOREIGN TABLE distributors ALTER COLUMN street SET NOT NULL;

Change an exterior option:

ALTER FOREIGN TABLE myschema.distributors 
    OPTIONS (ADD opt1 'value', SET opt2 'value2', DROP opt3 'value3');

compatibility

These ADD, DROP and SET DATA TYPE are in the form of the SQL standard. Other forms are extensions of YMatrix databases in the SQL standard. The ability to specify multiple operations in a single ALTER FOREIGN TABLE command is also an extension of the YMatrix database.