Help Center/ GaussDB(DWS)/ SQL Syntax Reference (8.1.3.x)/ DDL Syntax/ ALTER FOREIGN TABLE (SQL on other GaussDB(DWS))
Updated on 2024-12-19 GMT+08:00

ALTER FOREIGN TABLE (SQL on other GaussDB(DWS))

Function

ALTER FOREIGN TABLE modifies a foreign table in associated analysis.

Precautions

None

Syntax

  • Set a foreign table's attributes:
    1
    2
    ALTER FOREIGN TABLE [ IF EXISTS ] tablename
        OPTIONS ( {[ SET ] option ['value']} [, ... ]);
    
  • Set the owner of the foreign table:
    1
    2
    ALTER FOREIGN TABLE [ IF EXISTS ] tablename 
        OWNER TO new_owner;
    
  • Update the type of a foreign table column:
    1
    2
    ALTER FOREIGN TABLE [ IF EXISTS ] table_name
        MODIFY ( { column_name data_type [, ...] } );
    
  • Modify the column of the foreign table:
    1
    2
    ALTER FOREIGN TABLE [ IF EXISTS ] tablename 
        action [, ... ];
    
    The action syntax is as follows:
    1
    2
    ALTER [ COLUMN ] column_name [ SET DATA ] TYPE data_type
        | MODIFY column_name data_type
    

    For details, see ALTER TABLE.

Parameter Description

  • IF EXISTS

    Sends a notification instead of an error if no tables have identical names. The notification prompts that the table you are querying does not exist.

  • tablename

    Specifies the name of an existing foreign table to be modified.

    Value range: an existing foreign table name

  • new_owner

    Specifies the new owner of the foreign table.

    Value range: a string indicating a valid user name

  • data_type

    Specifies the new type for an existing column.

    Value range: a string compliant with the identifier naming rules

  • column_name

    Specifies the name of an existing column.

    Value range: a string. It must comply with the naming convention.

For details on how to modify other parameters in the foreign table, see Parameter Description in ALTER TABLE.

Example:

  1. Create a foreign server named server_remote. The corresponding foreign data wrapper is GC_FDW.
    1
    CREATE SERVER server_remote FOREIGN DATA WRAPPER GC_FDW OPTIONS (address '10.10.0.100:25000,10.10.0.101:25000',dbname 'test', username 'test', password '{Password}');
    
  2. Create a foreign table named region.
     1
     2
     3
     4
     5
     6
     7
     8
     9
    10
    11
    12
    13
    14
    15
    DROP FOREIGN TABLE IF EXISTS region;
    CREATE FOREIGN TABLE region
    (
        R_REGIONKEY INT4,
        R_NAME TEXT,
        R_COMMENT TEXT
    )
    SERVER
        server_remote
    OPTIONS
    (
        schema_name 'test',
        table_name 'region',
        encoding 'gbk'
    );
    
  3. Modify the region option of the foreign table.
    1
    ALTER FOREIGN TABLE region OPTIONS (SET schema_name 'test');
    
  4. Change the type of the r_name column to text in the region foreign table.
    1
    ALTER FOREIGN TABLE region ALTER r_name TYPE TEXT;