Help Center/ GaussDB(DWS)/ SQL Syntax Reference/ SQL Syntax Reference (9.1.0.x)/ DDL Syntax/ ALTER FOREIGN TABLE (for HDFS or OBS)
Updated on 2025-07-22 GMT+08:00
View PDF
Share

ALTER FOREIGN TABLE (for HDFS or OBS)

Function

Modifies an HDFS or OBS foreign table.

Precautions

None

Syntax

  • Set the attributes of a foreign table. 
    1
2
    		 
    ALTER FOREIGN TABLE [ IF EXISTS ]  table_name
    OPTIONS ( {[ ADD | SET | DROP ] option ['value']} [, ... ]);
  • Set the owner of the foreign table. 
    1
2
    		 
    ALTER FOREIGN TABLE [ IF EXISTS ] tablename 
    OWNER TO new_owner;
  • Update a foreign table column. 
    1
2
    		 
    ALTER FOREIGN TABLE [ IF EXISTS ] table_name
    MODIFY ( { column_name data_type | column_name [ CONSTRAINT constraint_name ] NOT NULL [ ENABLE ] | column_name [ CONSTRAINT constraint_name ] NULL } [, ...] );
  • Modify the columns of a foreign table. 
    1
2
    		 
    ALTER FOREIGN TABLE [ IF EXISTS ] tablename 
    action [, ... ];
    The action syntax is as follows: 
    1
2
3
4
5
6
7
    		 
    ALTER [ COLUMN ] column_name [ SET DATA ] TYPE data_type
   | ALTER [ COLUMN ] column_name { SET | DROP } NOT NULL
   | ALTER [ COLUMN ] column_name SET STATISTICS [PERCENT] integer
   | ALTER [ COLUMN ] column_name OPTIONS ( {[ ADD | SET | DROP ] option ['value'] } [, ... ])
   | MODIFY column_name data_type
   | MODIFY column_name [ CONSTRAINT constraint_name ] NOT NULL [ ENABLE ]
   | MODIFY column_name [ CONSTRAINT constraint_name ] NULL

    For details, see ALTER TABLE.

  • Add an informational constraint for a foreign table. 
    1
2
3
4
    		 
    ALTER FOREIGN TABLE [ IF EXISTS ] tablename
    ADD [ CONSTRAINT constraint_name ]
    { PRIMARY KEY | UNIQUE } ( column_name )
    [ NOT ENFORCED [ ENABLE QUERY OPTIMIZATION | DISABLE QUERY OPTIMIZATION ] | ENFORCED ];

    For details about the parameters for adding an informational constraint, see Parameter Description.

  • Remove a foreign table informational constraint. 
    1
2
    		 
    ALTER FOREIGN TABLE [ IF EXISTS ] tablename 
    DROP CONSTRAINT constraint_name ;
  • Set the row-level access control switch for foreign tables (supported by cluster versions 9.1.0 and later). 
    ALTER FOREIGN TABLE [ IF EXISTS ] tablename
        ENABLE | DISABLE | FORCE | NO FORCE ROW LEVEL SECURITY

Parameter Description

Table 1 ALTER FOREIGN TABLE (For HDFS or OBS) parameters

Parameter

Description

Value Range

IF EXISTS

Sends a message instead of an error if the specified foreign table does not exist.

-

tablename

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

Name of an existing foreign table.

new_owner

Specifies the new owner of the foreign table.

A string that indicates a valid username.

data_type

Specifies the new data type of an existing column.

A string.

constraint_name

Specifies the name of a constraint to be added or deleted.

A string.

column_name

Specifies an existing column name.

A string.

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

Examples

  1. Create HDFS_Server and set the corresponding foreign data wrapper to HDFS_FDW or DFS_FDW. 
    1
    		 
    CREATE SERVER hdfs_server FOREIGN DATA WRAPPER HDFS_FDW OPTIONS (address '10.10.0.100:25000,10.10.0.101:25000',hdfscfgpath '/opt/hadoop_client/HDFS/hadoop/etc/hadoop',type'HDFS');
  2. Create an HDFS foreign table that does not contain partition columns. 
     1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
    		 
    DROP FOREIGN TABLE IF EXISTS ft_region;
CREATE FOREIGN TABLE ft_region
(
    R_REGIONKEY INT4,
    R_NAME TEXT,
    R_COMMENT TEXT
)
SERVER
    hdfs_server
OPTIONS
(
    FORMAT 'orc',
    encoding 'utf8',
    FOLDERNAME '/user/hive/warehouse/gauss.db/region_orc11_64stripe/'
)
DISTRIBUTE BY 
     roundrobin;
  3. Change the type of the r_name column in the foreign table ft_region to text. 
    1
    		 
    ALTER FOREIGN TABLE ft_region ALTER r_name TYPE TEXT;
  4. Mark the r_name column of the foreign table ft_region as not null. 
    1
    		 
    ALTER FOREIGN TABLE ft_region ALTER r_name SET NOT NULL;

Helpful Links

CREATE FOREIGN TABLE (SQL on OBS or Hadoop) and DROP FOREIGN TABLE

Parent Topic: DDL Syntax