PG18 Hacktober : 31 Days of New Features : FDW Enhancements: CREATE FOREIGN TABLE .. LIKE: Part-1

In today’s post of the PG18 Hacktober segment!, we’re going to discuss the LIKE operator that was introduced to simplify Foreign Tables management in Postgres 18. This post would be Part-1 of the 2-part series on this topic.

Introduction

PostgreSQL 18 introduces an enhanced LIKE clause for CREATE FOREIGN TABLE, allowing developers to quickly replicate the structure of an existing table. This feature copies all column names, data types, constraints, and even comments, making it easier to create tables with identical structures.

Unlike the INHERITS clause, tables created with LIKE are completely independent after creation; changes to the source table do not affect the new one. The like option clauses provide flexibility, enabling users to include or exclude specific properties such as defaults, constraints, or storage parameters as needed.

What does the documentation say?

Under Foreign Data Wrappers feature enhancement:

CREATE FOREIGN TABLE … LIKE

Specify a source table from which the new table automatically copies all column names, their data types, and their not-null constraints.

What are Foreign Data Wrappers?

Foreign Data Wrappers (FDWs) are a PostgreSQL feature that allows you to query external data sources as if they were native Postgres tables. Using the SQL/MED standard, FDWs make it possible to access remote PostgreSQL databases, flat files, APIs, and even other database systems (like MySQL or Oracle) all through standard SQL.

The postgres_fdw is the built-in FDW that lets PostgreSQL instances communicate with each other. With FDWs, developers can build federated databases, consolidate reporting, and simplify cross-environment integration, all while maintaining the feel of a single unified schema.

How to use the LIKE option?

The LIKE option can include or exclude specific table properties when creating a new table.

You can use:

INCLUDING: To copy a particular property. (e.g., defaults, constraints, storage settings)

EXCLUDING: To skip copying that property. ( eg., default behavior)

This allows developers to control what gets copied from the source table precisely.

Note: Make sure you installed the “postgres_fdw” extension before you go ahead and execute the following example.

Let make sure:

samdb=# CREATE EXTENSION IF NOT EXISTS postgres_fdw;
CREATE EXTENSION

List of the Extensions

samdb=# \dx
                               List of installed extensions
     Name     | Version |   Schema   |                    Description                     
--------------+---------+------------+----------------------------------------------------
 plpgsql      | 1.0     | pg_catalog | PL/pgSQL procedural language
 postgres_fdw | 1.2     | public     | foreign-data wrapper for remote PostgreSQL servers
(2 rows)

Example: Using LIKE in CREATE FOREIGN TABLE

Step 1: Create the Foreign Server

samdb=# CREATE SERVER foreign_server
FOREIGN DATA WRAPPER postgres_fdw
OPTIONS (host '192.168.232.146', port '5432', dbname 'sam');
CREATE SERVER

Step 2: Create User Mapping

samdb=# CREATE USER MAPPING FOR postgres
SERVER foreign_server
OPTIONS (user 'postgres', password 'postgres');
CREATE USER MAPPING

Example 1: Copying All Defaults and Constraints

samdb=# CREATE FOREIGN TABLE remote_customer
(                    
  LIKE customer INCLUDING DEFAULTS INCLUDING CONSTRAINTS
)
SERVER foreign_server
OPTIONS (schema_name 'public',table_name 'sam');
CREATE FOREIGN TABLE

Explanation:

Creates a foreign table named remote_customer in your local database (samdb).
The structure of remote_customer is copied from the local table customer (LIKE customer).

INCLUDING DEFAULTS and INCLUDING CONSTRAINTS means:

Copy default values and column constraints.
The SERVER foreign_server clause tells PostgreSQL that this table points to a remote database connection (foreign_server).

In OPTIONS, you specify:

schema_name ‘public’– schema in the remote DB.
table_name ‘sam’ – remote table name.

Checking Foreign Table Details

After creating the foreign table, you can verify it in psql using the \det+ command:

samdb=# \det+ remote_customer
                                       List of foreign tables
 Schema |      Table      |     Server     |               FDW options                | Description 
--------+-----------------+----------------+------------------------------------------+-------------
 public | remote_customer | foreign_server | (schema_name 'public', table_name 'sam') | 
(1 row)

This command lists:

The schema where the foreign table is located
The foreign server it’s mapped to
The FDW options, such as the remote schema and table name

Practical Use Cases

Schema cloning across databases: Easily replicate a table structure between databases connected via FDWs — useful for migrations or federated architectures.
Cross-database reporting: Ensure structural consistency across reporting tables in multiple environments (prod, staging, test).
Dev/Test automation: Spin up lightweight schema-only test tables based on production definitions without copying any data.
Multi-Tenant or Multi-Region setups: Maintain consistent schemas across remote regions or isolated tenant databases — all defined from a central schema.

Summary

The new LIKE support in CREATE FOREIGN TABLE is a small but hugely impactful addition in PostgreSQL 18. It gives developers and DBAs the ability to:

Maintain consistent schemas across systems
Reduce boilerplate when working with foreign tables
Avoid human error in FDW setups
Automate schema provisioning more efficiently

Next Up: Part 2

In Part 2, we’ll explore each like option in more detail:

INCLUDING COMMENTS, COMPRESSION, DEFAULTS, STORAGE, and more;

as well as their implications on foreign tables, best practices, and limitations.