In today’s continuation of the PG18 Hacktober series, we’re diving deeper into PostgreSQL 18’s enhanced LIKE clause for creating foreign tables using the CREATE FOREIGN TABLE command.
Introduction
In Part-1, we explored how the LIKE option helps replicate table structures with control over properties like DEFAULTS and CONSTRAINTS.
Now, in Part-2, we’ll uncover additional LIKE options, such as a COMMENTS, COMPRESSION, STORAGE, and more, each designed to provide finer control over schema replication and metadata consistency across databases.
Real-World Use Case: Production to Reporting Sync.
A DBA managing both a production and a reporting database table often needs to mirror production tables into the reporting system for analytics, but doesn’t want to:
- Manually recreate schemas every time there’s a change
- Risk of missing defaults, data types, or comments
Solution:
Use CREATE FOREIGN TABLE ... LIKE to:
Quickly replicate the structure of the production table
Ensure column names, constraints, defaults, and comments are preserved
Avoid manual DDL and human errors.
Example 1:Copying Defaults but Excluding Constraints
samdb=# CREATE FOREIGN TABLE ft_customer (
LIKE customer INCLUDING DEFAULTS EXCLUDING CONSTRAINTS)
SERVER foreign_server
OPTIONS (schema_name 'public', table_name 'customer');
CREATE FOREIGN TABLEExplanation:
- Creates a foreign table called
ft_customerin your local DB. - Copies the structure of the local customer table since we’re using the
LIKEclause. INCLUDING DEFAULTS– copies default values.EXCLUDING CONSTRAINTS– skips primary keys, unique keys, and check constraints.- table_name ‘customer’ – refers to a remote table named customer.
2. INCLUDING COMMENTS
The INCLUDING COMMENTS option ensures that the column and table comments from the source table are copied to the new foreign table.
This helps maintain metadata documentation, which is especially useful in environments where table comments describe column usage or business rules.
Example:
samdb=# CREATE FOREIGN TABLE ft_customer_comment (
LIKE customer INCLUDING COMMENTS)
SERVER foreign_server
OPTIONS (schema_name 'public', table_name 'customer');
CREATE FOREIGN TABLEExplanation:
This command copies all comments from the local customer table to the new ft_customer_comment foreign table.
It helps keep documentation consistent across local and remote databases critical for analysts, developers, and DBAs maintaining complex schemas.
3. INCLUDING COMPRESSION
The INCLUDING COMPRESSION clause copies compression settings for columns from the source table to the new foreign table.
PostgreSQL 14+ introduced per-column compression, and in PostgreSQL 18, this property can now be retained when creating foreign tables.
Example:
samdb=# CREATE FOREIGN TABLE ft_customer_compression (
LIKE customer INCLUDING COMPRESSION)
SERVER foreign_server
OPTIONS (schema_name 'public', table_name 'customer');
CREATE FOREIGN TABLEExplanation:
This ensures that any compression attributes (such as COMPRESSION pglz or COMPRESSION lz4) used in the original table are also reflected in the foreign table definition.
4. INCLUDING STORAGE
INCLUDING STORAGE copies the storage parameters for each column from the source table to the new foreign table.
Storage parameters define how PostgreSQL stores large or variable-length columns such as text, bytea, or jsonb.
Example:
samdb=# CREATE FOREIGN TABLE ft_customer_storage (
LIKE customer INCLUDING STORAGE)
SERVER foreign_server
OPTIONS (schema_name 'public', table_name 'customer');
CREATE FOREIGN TABLEExplanation:
This ensures that the same storage settings (e.g., MAIN, EXTERNAL, EXTENDED, or PLAIN) are applied in the foreign table to preserve performance characteristics and disk usage efficiency.
5. INCLUDING ALL
If you want to copy every possible attribute from the source table, use the INCLUDING ALL option.
This is a shorthand for copying defaults, constraints, comments, compression, and storage settings together.
Example:
samdb=# CREATE FOREIGN TABLE ft_customer_all (
LIKE customer INCLUDING ALL)
SERVER foreign_server
OPTIONS (schema_name 'public', table_name 'customer');
CREATE FOREIGN TABLEExplanation:
This single command replicates everything: the structure, defaults, constraints, comments, compression, and storage, giving you an exact schema copy for a foreign table.
6. Combining INCLUDING and EXCLUDING
You can combine both the INCLUDING and EXCLUDING options to fine-tune what gets copied.
Example:
samdb=#CREATE FOREIGN TABLE ft_customer_mix (
LIKE customer INCLUDING DEFAULTS EXCLUDING CONSTRAINTS INCLUDING COMMENTS)
SERVER foreign_server OPTIONS (schema_name 'public', table_name 'customer');
CREATE FOREIGN TABLEExplanation:
INCLUDING DEFAULTS– copies column default valuesEXCLUDING CONSTRAINTS– skips primary/unique keys and check constraintsINCLUDING COMMENTS– adds column/table comments- This combination allows you to customize your schema replication precisely as needed.
7. Verifying the Foreign Table
After creation, verify details with \det+:
samdb=# \det+ ft_customer_all
List of foreign tables
Schema | Table | Server | FDW options | Description
--------+-----------------+----------------+-----------------------------------------------+-----------------------------------------------------
public | ft_customer_all | foreign_server | (schema_name 'public', table_name 'customer') | Foreign table mirroring customer from remote server
(1 row)This will display:
- The schema where the foreign table exists
- The server it connects to
- The FDW options used (schema name, table name, etc.)
Conclusion :
The extended LIKE options in PostgreSQL 18, make schema replication for foreign tables far more powerful and precise. Developers can now maintain structural, metadata, and storage consistency between local and remote databases with minimal manual intervention.
Together, Part-1 and Part-2 provide a complete understanding of how PostgreSQL 18 simplifies foreign table creation and management, ensuring consistency, scalability, and efficiency across your data ecosystem.