Create a foreign table with only specific columns

April 1, 2023

I would like to know if there is a way to create a foreign table only with specific columns and conditions in PostgreSQL. For example i saw someone suggest this command but it doesn’t really work because the OPTIONS (table '') at the bottom doesn’t actually exist.

CREATE FOREIGN TABLE app_users (
id INT NOT NULL,
login VARCHAR(190),
email VARCHAR(190),
"name" VARCHAR(255)
) SERVER fdw_app
OPTIONS (
table '(SELECT id, login, email, "name" FROM public."user" WHERE login like ''app-%'')');

Sql returns that the available options are only schema_name, table_name, use_remote_estimate, updatable, fetch_size

Is there an actual way to do this or is it impossible?

>Solution :

You’re correct that the syntax you provided is not valid in PostgreSQL, as it doesn’t support specifying a query for a foreign table directly in the CREATE FOREIGN TABLE statement.

However, you can achieve a similar result by creating a view on the foreign server side that filters the data as desired, and then create a foreign table in your local PostgreSQL server to access that view.

Connect to the remote server and create a view:

CREATE VIEW public.filtered_users AS
SELECT id, login, email, "name"
FROM public."user"
WHERE login LIKE 'app-%';

In your local PostgreSQL server, create a foreign table to access the remote view:

CREATE FOREIGN TABLE app_users (
  id INT NOT NULL,
  login VARCHAR(190),
  email VARCHAR(190),
  "name" VARCHAR(255)
) SERVER fdw_app
OPTIONS (
  schema_name 'public',
  table_name 'filtered_users'
);

With these steps, you’ll have a foreign table named app_users in your local PostgreSQL server, which corresponds to the filtered view in the remote server. You can then query app_users as if it were a local table. Note that this approach requires you to have access to the remote server to create the view.