Database Reference
In-Depth Information
Creating a foreign table
After creating the server and user mapping, the next step is to create a foreign table.
The syntax for creating a foreign table is as follows:
CREATE FOREIGN TABLE [ IF NOT EXISTS ] table_name
( [column_namedata_type
[ OPTIONS ( option 'value' [, ... ] ) ]
[ COLLATE collation ]
[ column_constraint [ ... ] ] [, ... ] ] )
SERVER server_name [ OPTIONS ( option 'value' [, ... ] ) ]
WHERE column_constraint is:
[ CONSTRAINT constraint_name ]
{ NOT NULL | NULL | DEFAULT default_expr }
Let's consider the various parameters mentioned in the preceding syntax:
•
table_name
: This is the name of the table.
•
column_name
: This is the name of the column.
•
data_type
: This gives the data type.
•
DEFAULT default_expr
: This is the the
DEFAULT
clause.
•
server_name
: This is the name of the foreign server.
•
OPTIONS
: This is the foreign data wrapper speciic table options. It normally
contains the remote table name.
Here is a simple example to create a user mapping for the
postgres
user:
CREATE FOREIGN TABLE dummy_table
(
id INTEGER,
name TEXT
)
SERVER dummy_server OPTIONS(table_name 'remote_dummy_table');
Creating a foreign table is remote mapping a table and does not
mean creating a table on a remote server. It is a mapped table on
a remote server.
Using foreign data wrappers
After creating the foreign table, we can perform DML on the table just like a normal
table like the following statement:
SELECT * FROM dummy_table;
