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

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

Function

In the current database, CREATE FOREIGN TABLE creates a foreign table for collaborative analysis. The foreign table is used to access tables stored in other databases for collaborative analysis.

The foreign table is read-only. It can only be queried using SELECT.

Syntax

1
2
3
4
5
6
7
CREATE FOREIGN TABLE [ IF NOT EXISTS ] table_name
( [ { column_name type_name | LIKE source_table } [, ...] ] )
SERVER server_name
OPTIONS ( { option_name ' value ' } [, ...] )
[ READ ONLY ]
[ DISTRIBUTE BY {ROUNDROBIN} ]
[ TO { GROUP groupname | NODE ( nodename [, ... ] ) } ];

Parameter Description

  • IF NOT EXISTS

    Does not throw an error if a table with the same name exists. A notice is issued in this case.

  • table_name

    Specifies the name of the foreign table to be created.

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

  • column_name

    Specifies the name of a column in the foreign table. Columns are separated by commas (,).

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

    Constraints or indexes cannot be created on columns.

  • type_name

    Specifies the data type of the column.

    Sequence and custom types are not allowed.

  • SERVER server_name

    Specifies the server name, which is user-definable.

    Value range: a string indicating an existing server. It must comply with the naming convention.

  • OPTIONS ( { option_name ' value ' } [, ...] )

    Specifies the following parameters for a foreign table:

    • table_name: table name of the associated cluster. If it is omitted, the foreign table name will be used.
    • schema_name: schema of the associated cluster. If it is omitted, the schema of the foreign table will be used.
    • encoding: encoding set of the associated cluster. If it is omitted, the database encoding set of the associated cluster will be used.
  • READ ONLY

    Indicates that a table is a read-only foreign table.

  • DISTRIBUTE BY ROUNDROBIN

    Specifies ROUNDROBIN as the distribution mode for the foreign table.

  • TO { GROUP groupname | NODE ( nodename [, ... ] ) }

    Currently, TO GROUP cannot be used. TO NODE is used for internal scale-out tools.

Examples

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

    The IP addresses and port numbers of associated CNs are specified in OPTIONS. You are advised to set this parameter to an LVS address or multiple CN addresses.

  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. View the created region foreign table.
    1
    \d+ region