Proposal to CREATE FOREIGN TABLE LIKE
Started by Zhang Mingli12 months ago26 messages
Hi, all
I wanted to bring up an idea that could really help out.
Our DBA team uses foreign tables for ETL processes in Greenplum and Cloudberry,
and we often need to create foreign tables that match the column definitions of local tables.
When dealing with wide tables and lots of those foreign tables, it can get pretty tedious and mistakes happen easily.
We end up having to troubleshoot errors when querying, which is a hassle.
Sure, we could use pg_dump to get the table DDL and modify the name, but that just adds more busywork.
CREATE FOREIGN TABLE LIKE command could save a lot of time and reduce errors in the long run.
It would work similarly to CREATE TABLE LIKE, copying the column definitions and constraints from the source table.
And since Postgres doesn’t enforce constraints on foreign tables, it’s up to the user to make sure the constraints match the actual data.
https://www.postgresql.org/docs/current/sql-createforeigntable.html
This means that enabling CREATE FOREIGN TABLE LIKE shouldn’t introduce more issues with constraints
I haven’t rush with the codes yet, but it seems like it could be straightforward to implement by tweaking the existing limitations:
```
static void
transformTableLikeClause(CreateStmtContext *cxt, TableLikeClause *table_like_clause)
{
...
if (cxt->isforeign)
ereport(ERROR,
(errcode(ERRCODE_FEATURE_NOT_SUPPORTED),
errmsg("LIKE is not supported for creating foreign tables")));
}
```
with some test cases and Documents changes.
Zhang Mingli
www.hashdata.xyz
Import Notes
Reply to msg id not found: 8eac1bb4-bfbf-4b32-b439-adcbdcd9ed98@SparkReference msg id not found: 8eac1bb4-bfbf-4b32-b439-adcbdcd9ed98@Spark
Re: Proposal to CREATE FOREIGN TABLE LIKE
On 2025-Feb-01, Zhang Mingli wrote:
Our DBA team uses foreign tables for ETL processes in Greenplum and Cloudberry,
and we often need to create foreign tables that match the column definitions of local tables.When dealing with wide tables and lots of those foreign tables, it can get pretty tedious and mistakes happen easily.
Sure. Did you consider IMPORT FOREIGN SCHEMA?
--
Álvaro Herrera 48°01'N 7°57'E — https://www.EnterpriseDB.com/
[…] indem ich in meinem Leben oft an euch gedacht, euch glücklich zu machen. Seyd es!
A menudo he pensado en vosotros, en haceros felices. ¡Sedlo, pues!
Heiligenstädter Testament, L. v. Beethoven, 1802
https://de.wikisource.org/wiki/Heiligenstädter_Testament
Re: Proposal to CREATE FOREIGN TABLE LIKE
Zhang Mingli
www.hashdata.xyz
On Feb 1, 2025 at 20:20 +0800, Álvaro Herrera <alvherre@alvh.no-ip.org>, wrote:
Sure. Did you consider IMPORT FOREIGN SCHEMA?
Hi, Álvaro
Thank you very much for your suggestion.
I've looked into it, and it certainly can be beneficial, especially for postgres_fdw.
However, I believe that not all FDWs support the concept of a schema or can be used with the IMPORT FOREIGN SCHEMA command, is it?
For example, we use kafka_fdw to produce and consume data from a Kafka server.
In our scenario, we sometimes need to write records from a local table into Kafka. Here’s a brief outline of our process:
1. We already have a wide table, local_wide_table in Postgres.
2. We need to create a foreign table, foreign_table, with the same definition as local_wide_table.
3. Insert records into foreign_table by selecting from local_wide_table with the some quals.
In step 2, we currently have to manually create the foreign table using CREATE FOREIGN TABLE and copy the column definitions one by one.
Re: Proposal to CREATE FOREIGN TABLE LIKE
On 2025-Feb-01, Zhang Mingli wrote:
For example, we use kafka_fdw to produce and consume data from a Kafka
server. In our scenario, we sometimes need to write records from a
local table into Kafka. Here’s a brief outline of our process:1. We already have a wide table, local_wide_table in Postgres.
2. We need to create a foreign table, foreign_table, with the same
definition as local_wide_table.
3. Insert records into foreign_table by selecting
from local_wide_table with the some quals.In step 2, we currently have to manually create the foreign table
using CREATE FOREIGN TABLE and copy the column definitions one by one.
Eh yeah, I guess for this use case it makes sense to allow a LIKE clause
on CREATE FOREIGN TABLE. Were you going to submit a patch?
--
Álvaro Herrera Breisgau, Deutschland — https://www.EnterpriseDB.com/
"Having your biases confirmed independently is how scientific progress is
made, and hence made our great society what it is today" (Mary Gardiner)
Re: Proposal to CREATE FOREIGN TABLE LIKE
Zhang Mingli
www.hashdata.xyz
On Feb 2, 2025 at 21:24 +0800, Álvaro Herrera <alvherre@alvh.no-ip.org>,
wrote:
Eh yeah, I guess for this use case it makes sense to allow a LIKE clause
on CREATE FOREIGN TABLE. Were you going to submit a patch?
Hi,
Yes, I would like to provide a patch.
Glad to see we have come to an agreement on this.
Re: Proposal to CREATE FOREIGN TABLE LIKE
On Mon, Feb 03, 2025 at 06:22:13AM +0800, Mingli Zhang wrote:
Yes, I would like to provide a patch.
Glad to see we have come to an agreement on this.
Just adding my +1 here. FWIW.
--
Michael
Re: Proposal to CREATE FOREIGN TABLE LIKE
On Feb 3, 2025 at 08:29 +0800, Michael Paquier <michael@paquier.xyz>, wrote:
On Mon, Feb 03, 2025 at 06:22:13AM +0800, Mingli Zhang wrote:
Yes, I would like to provide a patch.
Glad to see we have come to an agreement on this.
Just adding my +1 here. FWIW.
Hi,
Patch added.
Added support for CREATE FOREIGN TABLE LIKE to enable the creation of foreign tables based on the column definitions, constraints of existing source tables.
This feature mirrors the behavior of CREATE TABLE LIKE, but ignores inapplicable options such as INCLUDING INDEXES and INCLUDING COMPRESSION for foreign tables.
I have disallowed the COMPRESSION option due to existing inconsistencies between STORAGE and COMPRESSION.
I’ve posted a summary of these issues at Inconsistency between Compression and Storage for Foreign Tables[0] /messages/by-id/6cecef0e-ee14-473c-bb0a-6aa61f539a66@Spark.
Once we align the behavior of STORAGE and COMPRESSION, I believe that COMPRESSION should also be copied for foreign tables, similar to STORAGE.
This might be beneficial for foreign data wrappers (FDWs) that take it into account.
[0]: /messages/by-id/6cecef0e-ee14-473c-bb0a-6aa61f539a66@Spark
--
Zhang Mingli
HashData
Attachments:
v0-0001-CREATE-FOREIGN-TABLE-LIKE.patchapplication/octet-streamDownload
From c9f1dd7583ebe537e9e0397682b20d66fb3d6730 Mon Sep 17 00:00:00 2001
From: Zhang Mingli <avamingli@gmail.com>
Date: Thu, 6 Feb 2025 17:51:24 +0800
Subject: [PATCH] CREATE FOREIGN TABLE LIKE
Added support for CREATE FOREIGN TABLE LIKE to enable the creation of
foreign tables based on the column definitions, constraints of existing
source tables. This feature mirrors the behavior of CREATE TABLE LIKE,
but ignores inapplicable options such as INCLUDING INDEXES
and INCLUDING COMPRESSION for foreign tables.
Authored-by: Zhang Mingli avamingli@gmail.com
---
doc/src/sgml/ref/create_foreign_table.sgml | 143 +++++++++++++++++-
src/backend/parser/parse_utilcmd.c | 20 +--
.../regress/expected/create_table_like.out | 119 +++++++++++++++
src/test/regress/sql/create_table_like.sql | 46 ++++++
4 files changed, 318 insertions(+), 10 deletions(-)
diff --git a/doc/src/sgml/ref/create_foreign_table.sgml b/doc/src/sgml/ref/create_foreign_table.sgml
index 0dcd9ca6f8..b8bfcf7013 100644
--- a/doc/src/sgml/ref/create_foreign_table.sgml
+++ b/doc/src/sgml/ref/create_foreign_table.sgml
@@ -23,7 +23,8 @@ PostgreSQL documentation
<synopsis>
CREATE FOREIGN TABLE [ IF NOT EXISTS ] <replaceable class="parameter">table_name</replaceable> ( [
{ <replaceable class="parameter">column_name</replaceable> <replaceable class="parameter">data_type</replaceable> [ OPTIONS ( <replaceable class="parameter">option</replaceable> '<replaceable class="parameter">value</replaceable>' [, ... ] ) ] [ COLLATE <replaceable>collation</replaceable> ] [ <replaceable class="parameter">column_constraint</replaceable> [ ... ] ]
- | <replaceable>table_constraint</replaceable> }
+ | <replaceable>table_constraint</replaceable>
+ | LIKE <replaceable>source_table</replaceable> [ <replaceable>like_option</replaceable> ... ] }
[, ... ]
] )
[ INHERITS ( <replaceable>parent_table</replaceable> [, ... ] ) ]
@@ -57,6 +58,10 @@ CREATE FOREIGN TABLE [ IF NOT EXISTS ] <replaceable class="parameter">table_name
CHECK ( <replaceable class="parameter">expression</replaceable> ) [ NO INHERIT ] }
[ ENFORCED | NOT ENFORCED ]
+<phrase>and <replaceable class="parameter">like_option</replaceable> is:</phrase>
+
+{ INCLUDING | EXCLUDING } { COMMENTS | CONSTRAINTS | DEFAULTS | GENERATED | IDENTITY | STATISTICS | ALL }
+
<phrase>and <replaceable class="parameter">partition_bound_spec</replaceable> is:</phrase>
IN ( <replaceable class="parameter">partition_bound_expr</replaceable> [, ...] ) |
@@ -191,6 +196,142 @@ WITH ( MODULUS <replaceable class="parameter">numeric_literal</replaceable>, REM
</listitem>
</varlistentry>
+ <varlistentry id="sql-createtable-parms-like">
+ <term><literal>LIKE <replaceable>source_table</replaceable> [ <replaceable>like_option</replaceable> ... ]</literal></term>
+ <listitem>
+ <para>
+ The <literal>LIKE</literal> clause specifies a table from which
+ the new table automatically copies all column names, their data types,
+ and their not-null constraints.
+ </para>
+ <para>
+ Unlike <literal>INHERITS</literal>, the new table and original table
+ are completely decoupled after creation is complete. Changes to the
+ original table will not be applied to the new table, and it is not
+ possible to include data of the new table in scans of the original
+ table.
+ </para>
+ <para>
+ Also unlike <literal>INHERITS</literal>, columns and
+ constraints copied by <literal>LIKE</literal> are not merged with similarly
+ named columns and constraints.
+ If the same name is specified explicitly or in another
+ <literal>LIKE</literal> clause, an error is signaled.
+ </para>
+ <para>
+ The optional <replaceable>like_option</replaceable> clauses specify
+ which additional properties of the original table to copy. Specifying
+ <literal>INCLUDING</literal> copies the property, specifying
+ <literal>EXCLUDING</literal> omits the property.
+ <literal>EXCLUDING</literal> is the default. If multiple specifications
+ are made for the same kind of object, the last one is used. The
+ available options are:
+
+ <variablelist>
+ <varlistentry id="sql-createtable-parms-like-opt-comments">
+ <term><literal>INCLUDING COMMENTS</literal></term>
+ <listitem>
+ <para>
+ Comments for the copied columns, constraints, and indexes will be
+ copied. The default behavior is to exclude comments, resulting in
+ the copied columns and constraints in the new table having no
+ comments.
+ </para>
+ </listitem>
+ </varlistentry>
+
+ <varlistentry id="sql-createtable-parms-like-opt-constraints">
+ <term><literal>INCLUDING CONSTRAINTS</literal></term>
+ <listitem>
+ <para>
+ <literal>CHECK</literal> constraints will be copied. No distinction
+ is made between column constraints and table constraints. Not-null
+ constraints are always copied to the new table.
+ </para>
+ </listitem>
+ </varlistentry>
+
+ <varlistentry id="sql-createtable-parms-like-opt-defaults">
+ <term><literal>INCLUDING DEFAULTS</literal></term>
+ <listitem>
+ <para>
+ Default expressions for the copied column definitions will be
+ copied. Otherwise, default expressions are not copied, resulting in
+ the copied columns in the new table having null defaults. Note that
+ copying defaults that call database-modification functions, such as
+ <function>nextval</function>, may create a functional linkage
+ between the original and new tables.
+ </para>
+ </listitem>
+ </varlistentry>
+
+ <varlistentry id="sql-createtable-parms-like-opt-generated">
+ <term><literal>INCLUDING GENERATED</literal></term>
+ <listitem>
+ <para>
+ Any generation expressions of copied column definitions will be
+ copied. By default, new columns will be regular base columns.
+ </para>
+ </listitem>
+ </varlistentry>
+
+ <varlistentry id="sql-createtable-parms-like-opt-identity">
+ <term><literal>INCLUDING IDENTITY</literal></term>
+ <listitem>
+ <para>
+ Any identity specifications of copied column definitions will be
+ copied. A new sequence is created for each identity column of the
+ new table, separate from the sequences associated with the old
+ table.
+ </para>
+ </listitem>
+ </varlistentry>
+
+ <varlistentry id="sql-createtable-parms-like-opt-statistics">
+ <term><literal>INCLUDING STATISTICS</literal></term>
+ <listitem>
+ <para>
+ Extended statistics are copied to the new table.
+ </para>
+ </listitem>
+ </varlistentry>
+
+ <varlistentry id="sql-createtable-parms-like-opt-storage">
+ <term><literal>INCLUDING STORAGE</literal></term>
+ <listitem>
+ <para>
+ <literal>STORAGE</literal> settings for the copied column
+ definitions will be copied. The default behavior is to exclude
+ <literal>STORAGE</literal> settings, resulting in the copied columns
+ in the new table having type-specific default settings. For more on
+ <literal>STORAGE</literal> settings, see <xref
+ linkend="storage-toast"/>.
+ </para>
+ </listitem>
+ </varlistentry>
+
+ <varlistentry id="sql-createtable-parms-like-opt-all">
+ <term><literal>INCLUDING ALL</literal></term>
+ <listitem>
+ <para>
+ <literal>INCLUDING ALL</literal> is an abbreviated form selecting
+ all the available individual options. (It could be useful to write
+ individual <literal>EXCLUDING</literal> clauses after
+ <literal>INCLUDING ALL</literal> to select all but some specific
+ options.)
+ </para>
+ </listitem>
+ </varlistentry>
+ </variablelist>
+ </para>
+
+ <para>
+ Foreign tables have no real storage in PostgreSQL.
+ Inapplicable options (e.g., <literal>INCLUDING INDEXES</literal>) are ignored.
+ </para>
+ </listitem>
+ </varlistentry>
+
<varlistentry>
<term><literal>CONSTRAINT <replaceable class="parameter">constraint_name</replaceable></literal></term>
<listitem>
diff --git a/src/backend/parser/parse_utilcmd.c b/src/backend/parser/parse_utilcmd.c
index ca028d2a66..ad37bc56b2 100644
--- a/src/backend/parser/parse_utilcmd.c
+++ b/src/backend/parser/parse_utilcmd.c
@@ -1117,6 +1117,12 @@ transformTableConstraint(CreateStmtContext *cxt, Constraint *constraint)
* process at this point, add the TableLikeClause to cxt->likeclauses, which
* will cause utility.c to call expandTableLikeClause() after the new
* table has been created.
+ *
+ * For foreign tables, they have no real storage in Postgres.
+ * Inapplicable options are ignored:
+ * CREATE_TABLE_LIKE_COMPRESSION
+ * CREATE_TABLE_LIKE_INDEXES
+ * while INDEXES is handled in expandTableLikeClause() later.
*/
static void
transformTableLikeClause(CreateStmtContext *cxt, TableLikeClause *table_like_clause)
@@ -1131,12 +1137,6 @@ transformTableLikeClause(CreateStmtContext *cxt, TableLikeClause *table_like_cla
setup_parser_errposition_callback(&pcbstate, cxt->pstate,
table_like_clause->relation->location);
- /* we could support LIKE in many cases, but worry about it another day */
- if (cxt->isforeign)
- ereport(ERROR,
- (errcode(ERRCODE_FEATURE_NOT_SUPPORTED),
- errmsg("LIKE is not supported for creating foreign tables")));
-
/* Open the relation referenced by the LIKE clause */
relation = relation_openrv(table_like_clause->relation, AccessShareLock);
@@ -1242,8 +1242,9 @@ transformTableLikeClause(CreateStmtContext *cxt, TableLikeClause *table_like_cla
def->storage = 0;
/* Likewise, copy compression if requested */
- if ((table_like_clause->options & CREATE_TABLE_LIKE_COMPRESSION) != 0
- && CompressionMethodIsValid(attribute->attcompression))
+ if ((table_like_clause->options & CREATE_TABLE_LIKE_COMPRESSION) != 0 &&
+ CompressionMethodIsValid(attribute->attcompression) &&
+ !cxt->isforeign)
def->compression =
pstrdup(GetCompressionMethodName(attribute->attcompression));
else
@@ -1522,7 +1523,8 @@ expandTableLikeClause(RangeVar *heapRel, TableLikeClause *table_like_clause)
* Process indexes if required.
*/
if ((table_like_clause->options & CREATE_TABLE_LIKE_INDEXES) &&
- relation->rd_rel->relhasindex)
+ relation->rd_rel->relhasindex &&
+ childrel->rd_rel->relkind != RELKIND_FOREIGN_TABLE)
{
List *parent_indexes;
ListCell *l;
diff --git a/src/test/regress/expected/create_table_like.out b/src/test/regress/expected/create_table_like.out
index e061389135..8ddadd57e2 100644
--- a/src/test/regress/expected/create_table_like.out
+++ b/src/test/regress/expected/create_table_like.out
@@ -563,3 +563,122 @@ DROP TYPE ctlty1;
DROP VIEW ctlv1;
DROP TABLE IF EXISTS ctlt4, ctlt10, ctlt11, ctlt11a, ctlt12;
NOTICE: table "ctlt10" does not exist, skipping
+--
+-- CREATE FOREIGN TABLE LIKE
+--
+CREATE FOREIGN DATA WRAPPER ctl_dummy;
+CREATE SERVER ctl_s0 FOREIGN DATA WRAPPER ctl_dummy;
+CREATE TABLE ctl_table(a int DEFAULT 1, b text,
+ c int GENERATED ALWAYS AS (a * 2) STORED,
+ d bigint GENERATED ALWAYS AS IDENTITY);
+CREATE INDEX ctl_table_a_key ON ctl_table(a);
+COMMENT ON COLUMN ctl_table.b IS 'Column b';
+CREATE STATISTICS ctl_table_stat ON a,b FROM ctl_table;
+ALTER TABLE ctl_table add constraint foo CHECK (b = 'text');
+\d+ ctl_table
+ Table "public.ctl_table"
+ Column | Type | Collation | Nullable | Default | Storage | Stats target | Description
+--------+---------+-----------+----------+------------------------------------+----------+--------------+-------------
+ a | integer | | | 1 | plain | |
+ b | text | | | | extended | | Column b
+ c | integer | | | generated always as (a * 2) stored | plain | |
+ d | bigint | | not null | generated always as identity | plain | |
+Indexes:
+ "ctl_table_a_key" btree (a)
+Check constraints:
+ "foo" CHECK (b = 'text'::text)
+Statistics objects:
+ "public.ctl_table_stat" ON a, b FROM ctl_table
+Not-null constraints:
+ "ctl_table_d_not_null" NOT NULL "d"
+
+-- Test EXCLUDING ALL by default
+CREATE FOREIGN TABLE ctl_foreign_table1(LIKE ctl_table) SERVER ctl_s0;
+\d+ ctl_foreign_table1
+ Foreign table "public.ctl_foreign_table1"
+ Column | Type | Collation | Nullable | Default | FDW options | Storage | Stats target | Description
+--------+---------+-----------+----------+---------+-------------+----------+--------------+-------------
+ a | integer | | | | | plain | |
+ b | text | | | | | extended | |
+ c | integer | | | | | plain | |
+ d | bigint | | not null | | | plain | |
+Not-null constraints:
+ "ctl_table_d_not_null" NOT NULL "d"
+Server: ctl_s0
+
+-- Test INCLUDING INDEXES does't take effect
+CREATE FOREIGN TABLE ctl_foreign_table2(LIKE ctl_table INCLUDING INDEXES) SERVER ctl_s0;
+\d+ ctl_foreign_table2
+ Foreign table "public.ctl_foreign_table2"
+ Column | Type | Collation | Nullable | Default | FDW options | Storage | Stats target | Description
+--------+---------+-----------+----------+---------+-------------+----------+--------------+-------------
+ a | integer | | | | | plain | |
+ b | text | | | | | extended | |
+ c | integer | | | | | plain | |
+ d | bigint | | not null | | | plain | |
+Not-null constraints:
+ "ctl_table_d_not_null" NOT NULL "d"
+Server: ctl_s0
+
+-- Test INCLUDING COMPRESSION does't take effect
+\set HIDE_TOAST_COMPRESSION false
+ALTER TABLE ctl_table ALTER COLUMN b TYPE varchar;
+ALTER TABLE ctl_table ALTER COLUMN b SET COMPRESSION pglz;
+\d+ ctl_table
+ Table "public.ctl_table"
+ Column | Type | Collation | Nullable | Default | Storage | Compression | Stats target | Description
+--------+-------------------+-----------+----------+------------------------------------+----------+-------------+--------------+-------------
+ a | integer | | | 1 | plain | | |
+ b | character varying | | | | extended | pglz | | Column b
+ c | integer | | | generated always as (a * 2) stored | plain | | |
+ d | bigint | | not null | generated always as identity | plain | | |
+Indexes:
+ "ctl_table_a_key" btree (a)
+Check constraints:
+ "foo" CHECK (b::text = 'text'::text)
+Statistics objects:
+ "public.ctl_table_stat" ON a, b FROM ctl_table
+Not-null constraints:
+ "ctl_table_d_not_null" NOT NULL "d"
+
+CREATE FOREIGN TABLE ctl_foreign_table3(LIKE ctl_table INCLUDING COMPRESSION) SERVER ctl_s0;
+\d+ ctl_foreign_table3
+ Foreign table "public.ctl_foreign_table3"
+ Column | Type | Collation | Nullable | Default | FDW options | Storage | Stats target | Description
+--------+-------------------+-----------+----------+---------+-------------+----------+--------------+-------------
+ a | integer | | | | | plain | |
+ b | character varying | | | | | extended | |
+ c | integer | | | | | plain | |
+ d | bigint | | not null | | | plain | |
+Not-null constraints:
+ "ctl_table_d_not_null" NOT NULL "d"
+Server: ctl_s0
+
+\set HIDE_TOAST_COMPRESSION true
+-- Test valid like_options take effect:
+-- COMMENTS, STATISTICS, CONSTRAINTS
+-- GENERATED, DEFAULTS, IDENTITY, STORAGE
+CREATE FOREIGN TABLE ctl_foreign_table4(LIKE ctl_table INCLUDING ALL) SERVER ctl_s0;
+\d+ ctl_foreign_table4
+ Foreign table "public.ctl_foreign_table4"
+ Column | Type | Collation | Nullable | Default | FDW options | Storage | Stats target | Description
+--------+-------------------+-----------+----------+------------------------------------+-------------+----------+--------------+-------------
+ a | integer | | | 1 | | plain | |
+ b | character varying | | | | | extended | | Column b
+ c | integer | | | generated always as (a * 2) stored | | plain | |
+ d | bigint | | not null | generated always as identity | | plain | |
+Check constraints:
+ "foo" CHECK (b::text = 'text'::text)
+Statistics objects:
+ "public.ctl_foreign_table4_a_b_stat" ON a, b FROM ctl_foreign_table4
+Not-null constraints:
+ "ctl_table_d_not_null" NOT NULL "d"
+Server: ctl_s0
+
+DROP TABLE ctl_table;
+DROP FOREIGN TABLE ctl_foreign_table1;
+DROP FOREIGN TABLE ctl_foreign_table2;
+DROP FOREIGN TABLE ctl_foreign_table3;
+DROP FOREIGN TABLE ctl_foreign_table4;
+DROP FOREIGN DATA WRAPPER ctl_dummy CASCADE;
+NOTICE: drop cascades to server ctl_s0
diff --git a/src/test/regress/sql/create_table_like.sql b/src/test/regress/sql/create_table_like.sql
index a41f8b83d7..95b2d09d64 100644
--- a/src/test/regress/sql/create_table_like.sql
+++ b/src/test/regress/sql/create_table_like.sql
@@ -225,3 +225,49 @@ DROP SEQUENCE ctlseq1;
DROP TYPE ctlty1;
DROP VIEW ctlv1;
DROP TABLE IF EXISTS ctlt4, ctlt10, ctlt11, ctlt11a, ctlt12;
+
+--
+-- CREATE FOREIGN TABLE LIKE
+--
+CREATE FOREIGN DATA WRAPPER ctl_dummy;
+CREATE SERVER ctl_s0 FOREIGN DATA WRAPPER ctl_dummy;
+
+CREATE TABLE ctl_table(a int DEFAULT 1, b text,
+ c int GENERATED ALWAYS AS (a * 2) STORED,
+ d bigint GENERATED ALWAYS AS IDENTITY);
+
+CREATE INDEX ctl_table_a_key ON ctl_table(a);
+COMMENT ON COLUMN ctl_table.b IS 'Column b';
+CREATE STATISTICS ctl_table_stat ON a,b FROM ctl_table;
+ALTER TABLE ctl_table add constraint foo CHECK (b = 'text');
+\d+ ctl_table
+
+-- Test EXCLUDING ALL by default
+CREATE FOREIGN TABLE ctl_foreign_table1(LIKE ctl_table) SERVER ctl_s0;
+\d+ ctl_foreign_table1
+
+-- Test INCLUDING INDEXES does't take effect
+CREATE FOREIGN TABLE ctl_foreign_table2(LIKE ctl_table INCLUDING INDEXES) SERVER ctl_s0;
+\d+ ctl_foreign_table2
+
+-- Test INCLUDING COMPRESSION does't take effect
+\set HIDE_TOAST_COMPRESSION false
+ALTER TABLE ctl_table ALTER COLUMN b TYPE varchar;
+ALTER TABLE ctl_table ALTER COLUMN b SET COMPRESSION pglz;
+\d+ ctl_table
+CREATE FOREIGN TABLE ctl_foreign_table3(LIKE ctl_table INCLUDING COMPRESSION) SERVER ctl_s0;
+\d+ ctl_foreign_table3
+\set HIDE_TOAST_COMPRESSION true
+
+-- Test valid like_options take effect:
+-- COMMENTS, STATISTICS, CONSTRAINTS
+-- GENERATED, DEFAULTS, IDENTITY, STORAGE
+CREATE FOREIGN TABLE ctl_foreign_table4(LIKE ctl_table INCLUDING ALL) SERVER ctl_s0;
+\d+ ctl_foreign_table4
+
+DROP TABLE ctl_table;
+DROP FOREIGN TABLE ctl_foreign_table1;
+DROP FOREIGN TABLE ctl_foreign_table2;
+DROP FOREIGN TABLE ctl_foreign_table3;
+DROP FOREIGN TABLE ctl_foreign_table4;
+DROP FOREIGN DATA WRAPPER ctl_dummy CASCADE;
--
2.34.1
Re: Proposal to CREATE FOREIGN TABLE LIKE
On 2025-Feb-06, Zhang Mingli wrote:
Added support for CREATE FOREIGN TABLE LIKE to enable the creation of
foreign tables based on the column definitions, constraints of
existing source tables.
This feature mirrors the behavior of CREATE TABLE LIKE, but ignores
inapplicable options such as INCLUDING INDEXES and INCLUDING
COMPRESSION for foreign tables.
I think it'd be better to throw errors if they are given -- but
INCLUDING ALL should be made to work in a different way than today so
that it doesn't raise errors uselessly. Right now it works by setting
all the bits in the value, um.
--
Álvaro Herrera PostgreSQL Developer — https://www.EnterpriseDB.com/
Re: Proposal to CREATE FOREIGN TABLE LIKE
On 2025-Feb-06, Álvaro Herrera wrote:
On 2025-Feb-06, Zhang Mingli wrote:
Added support for CREATE FOREIGN TABLE LIKE to enable the creation of
foreign tables based on the column definitions, constraints of
existing source tables.
This feature mirrors the behavior of CREATE TABLE LIKE, but ignores
inapplicable options such as INCLUDING INDEXES and INCLUDING
COMPRESSION for foreign tables.I think it'd be better to throw errors if they are given -- but
INCLUDING ALL should be made to work in a different way than today so
that it doesn't raise errors uselessly. Right now it works by setting
all the bits in the value, um.
Ah, but our fine manual already says
The LIKE clause can also be used to copy column definitions from views,
foreign tables, or composite types. Inapplicable options (e.g.,
INCLUDING INDEXES from a view) are ignored.
so what you implemented seems to be okay from that POV.
--
Álvaro Herrera Breisgau, Deutschland — https://www.EnterpriseDB.com/
"Investigación es lo que hago cuando no sé lo que estoy haciendo"
(Wernher von Braun)
Re: Proposal to CREATE FOREIGN TABLE LIKE
On Feb 6, 2025 at 18:31 +0800, Álvaro Herrera <alvherre@alvh.no-ip.org>, wrote:
Ah, but our fine manual already says
The LIKE clause can also be used to copy column definitions from views,
foreign tables, or composite types. Inapplicable options (e.g.,
INCLUDING INDEXES from a view) are ignored.so what you implemented seems to be okay from that POV.
Hi, Yeah,
Our current convention is to ignore any inapplicable options without throwing errors.
As you mentioned, we use bits to identify the options, which does add some complexity to the codes if we try to track the origin of the option bits.
--
Zhang Mingli
HashData
Re: Proposal to CREATE FOREIGN TABLE LIKE
On Feb 6, 2025 at 18:09 +0800, Zhang Mingli <zmlpostgres@gmail.com>, wrote:
On Feb 3, 2025 at 08:29 +0800, Michael Paquier <michael@paquier.xyz>, wrote:
On Mon, Feb 03, 2025 at 06:22:13AM +0800, Mingli Zhang wrote:
Yes, I would like to provide a patch.
Glad to see we have come to an agreement on this.
Just adding my +1 here. FWIW.
Hi,
Patch added.
Add it to commitfest: https://commitfest.postgresql.org/52/5557
--
Zhang Mingli
HashData
Re: Proposal to CREATE FOREIGN TABLE LIKE
On Feb 7, 2025 at 22:24 +0800, Zhang Mingli <zmlpostgres@gmail.com>, wrote:
On Feb 6, 2025 at 18:09 +0800, Zhang Mingli <zmlpostgres@gmail.com>, wrote:
On Feb 3, 2025 at 08:29 +0800, Michael Paquier <michael@paquier.xyz>, wrote:
On Mon, Feb 03, 2025 at 06:22:13AM +0800, Mingli Zhang wrote:
Yes, I would like to provide a patch.
Glad to see we have come to an agreement on this.
Just adding my +1 here. FWIW.
Hi,
Patch added.
Add it to commitfest: https://commitfest.postgresql.org/52/5557
Fix CI failure of doc build in v1 patch.
--
Zhang Mingli
HashData
Attachments:
v1-0001-CREATE-FOREIGN-TABLE-LIKE.patchapplication/octet-streamDownload
From 636e90d8ea0c2caa6ca575ee424498c734d3d418 Mon Sep 17 00:00:00 2001
From: Zhang Mingli <avamingli@gmail.com>
Date: Sat, 8 Feb 2025 00:46:17 +0800
Subject: [PATCH] CREATE FOREIGN TABLE LIKE
Added support for CREATE FOREIGN TABLE LIKE to enable the creation of
foreign tables based on the column definitions, constraints of existing
source tables. This feature mirrors the behavior of CREATE TABLE LIKE,
but ignores inapplicable options such as INCLUDING INDEXES
and INCLUDING COMPRESSION for foreign tables.
Authored-by: Zhang Mingli avamingli@gmail.com
---
doc/src/sgml/ref/create_foreign_table.sgml | 143 +++++++++++++++++-
src/backend/parser/parse_utilcmd.c | 20 +--
.../regress/expected/create_table_like.out | 119 +++++++++++++++
src/test/regress/sql/create_table_like.sql | 46 ++++++
4 files changed, 318 insertions(+), 10 deletions(-)
diff --git a/doc/src/sgml/ref/create_foreign_table.sgml b/doc/src/sgml/ref/create_foreign_table.sgml
index 0dcd9ca6f8..c328459ef5 100644
--- a/doc/src/sgml/ref/create_foreign_table.sgml
+++ b/doc/src/sgml/ref/create_foreign_table.sgml
@@ -23,7 +23,8 @@ PostgreSQL documentation
<synopsis>
CREATE FOREIGN TABLE [ IF NOT EXISTS ] <replaceable class="parameter">table_name</replaceable> ( [
{ <replaceable class="parameter">column_name</replaceable> <replaceable class="parameter">data_type</replaceable> [ OPTIONS ( <replaceable class="parameter">option</replaceable> '<replaceable class="parameter">value</replaceable>' [, ... ] ) ] [ COLLATE <replaceable>collation</replaceable> ] [ <replaceable class="parameter">column_constraint</replaceable> [ ... ] ]
- | <replaceable>table_constraint</replaceable> }
+ | <replaceable>table_constraint</replaceable>
+ | LIKE <replaceable>source_table</replaceable> [ <replaceable>like_option</replaceable> ... ] }
[, ... ]
] )
[ INHERITS ( <replaceable>parent_table</replaceable> [, ... ] ) ]
@@ -57,6 +58,10 @@ CREATE FOREIGN TABLE [ IF NOT EXISTS ] <replaceable class="parameter">table_name
CHECK ( <replaceable class="parameter">expression</replaceable> ) [ NO INHERIT ] }
[ ENFORCED | NOT ENFORCED ]
+<phrase>and <replaceable class="parameter">like_option</replaceable> is:</phrase>
+
+{ INCLUDING | EXCLUDING } { COMMENTS | CONSTRAINTS | DEFAULTS | GENERATED | IDENTITY | STATISTICS | ALL }
+
<phrase>and <replaceable class="parameter">partition_bound_spec</replaceable> is:</phrase>
IN ( <replaceable class="parameter">partition_bound_expr</replaceable> [, ...] ) |
@@ -191,6 +196,142 @@ WITH ( MODULUS <replaceable class="parameter">numeric_literal</replaceable>, REM
</listitem>
</varlistentry>
+ <varlistentry>
+ <term><literal>LIKE <replaceable>source_table</replaceable> [ <replaceable>like_option</replaceable> ... ]</literal></term>
+ <listitem>
+ <para>
+ The <literal>LIKE</literal> clause specifies a table from which
+ the new table automatically copies all column names, their data types,
+ and their not-null constraints.
+ </para>
+ <para>
+ Unlike <literal>INHERITS</literal>, the new table and original table
+ are completely decoupled after creation is complete. Changes to the
+ original table will not be applied to the new table, and it is not
+ possible to include data of the new table in scans of the original
+ table.
+ </para>
+ <para>
+ Also unlike <literal>INHERITS</literal>, columns and
+ constraints copied by <literal>LIKE</literal> are not merged with similarly
+ named columns and constraints.
+ If the same name is specified explicitly or in another
+ <literal>LIKE</literal> clause, an error is signaled.
+ </para>
+ <para>
+ The optional <replaceable>like_option</replaceable> clauses specify
+ which additional properties of the original table to copy. Specifying
+ <literal>INCLUDING</literal> copies the property, specifying
+ <literal>EXCLUDING</literal> omits the property.
+ <literal>EXCLUDING</literal> is the default. If multiple specifications
+ are made for the same kind of object, the last one is used. The
+ available options are:
+
+ <variablelist>
+ <varlistentry>
+ <term><literal>INCLUDING COMMENTS</literal></term>
+ <listitem>
+ <para>
+ Comments for the copied columns, constraints, and indexes will be
+ copied. The default behavior is to exclude comments, resulting in
+ the copied columns and constraints in the new table having no
+ comments.
+ </para>
+ </listitem>
+ </varlistentry>
+
+ <varlistentry>
+ <term><literal>INCLUDING CONSTRAINTS</literal></term>
+ <listitem>
+ <para>
+ <literal>CHECK</literal> constraints will be copied. No distinction
+ is made between column constraints and table constraints. Not-null
+ constraints are always copied to the new table.
+ </para>
+ </listitem>
+ </varlistentry>
+
+ <varlistentry>
+ <term><literal>INCLUDING DEFAULTS</literal></term>
+ <listitem>
+ <para>
+ Default expressions for the copied column definitions will be
+ copied. Otherwise, default expressions are not copied, resulting in
+ the copied columns in the new table having null defaults. Note that
+ copying defaults that call database-modification functions, such as
+ <function>nextval</function>, may create a functional linkage
+ between the original and new tables.
+ </para>
+ </listitem>
+ </varlistentry>
+
+ <varlistentry>
+ <term><literal>INCLUDING GENERATED</literal></term>
+ <listitem>
+ <para>
+ Any generation expressions of copied column definitions will be
+ copied. By default, new columns will be regular base columns.
+ </para>
+ </listitem>
+ </varlistentry>
+
+ <varlistentry>
+ <term><literal>INCLUDING IDENTITY</literal></term>
+ <listitem>
+ <para>
+ Any identity specifications of copied column definitions will be
+ copied. A new sequence is created for each identity column of the
+ new table, separate from the sequences associated with the old
+ table.
+ </para>
+ </listitem>
+ </varlistentry>
+
+ <varlistentry>
+ <term><literal>INCLUDING STATISTICS</literal></term>
+ <listitem>
+ <para>
+ Extended statistics are copied to the new table.
+ </para>
+ </listitem>
+ </varlistentry>
+
+ <varlistentry>
+ <term><literal>INCLUDING STORAGE</literal></term>
+ <listitem>
+ <para>
+ <literal>STORAGE</literal> settings for the copied column
+ definitions will be copied. The default behavior is to exclude
+ <literal>STORAGE</literal> settings, resulting in the copied columns
+ in the new table having type-specific default settings. For more on
+ <literal>STORAGE</literal> settings, see <xref
+ linkend="storage-toast"/>.
+ </para>
+ </listitem>
+ </varlistentry>
+
+ <varlistentry>
+ <term><literal>INCLUDING ALL</literal></term>
+ <listitem>
+ <para>
+ <literal>INCLUDING ALL</literal> is an abbreviated form selecting
+ all the available individual options. (It could be useful to write
+ individual <literal>EXCLUDING</literal> clauses after
+ <literal>INCLUDING ALL</literal> to select all but some specific
+ options.)
+ </para>
+ </listitem>
+ </varlistentry>
+ </variablelist>
+ </para>
+
+ <para>
+ Foreign tables have no real storage in PostgreSQL.
+ Inapplicable options (e.g., <literal>INCLUDING INDEXES</literal>) are ignored.
+ </para>
+ </listitem>
+ </varlistentry>
+
<varlistentry>
<term><literal>CONSTRAINT <replaceable class="parameter">constraint_name</replaceable></literal></term>
<listitem>
diff --git a/src/backend/parser/parse_utilcmd.c b/src/backend/parser/parse_utilcmd.c
index ca028d2a66..ad37bc56b2 100644
--- a/src/backend/parser/parse_utilcmd.c
+++ b/src/backend/parser/parse_utilcmd.c
@@ -1117,6 +1117,12 @@ transformTableConstraint(CreateStmtContext *cxt, Constraint *constraint)
* process at this point, add the TableLikeClause to cxt->likeclauses, which
* will cause utility.c to call expandTableLikeClause() after the new
* table has been created.
+ *
+ * For foreign tables, they have no real storage in Postgres.
+ * Inapplicable options are ignored:
+ * CREATE_TABLE_LIKE_COMPRESSION
+ * CREATE_TABLE_LIKE_INDEXES
+ * while INDEXES is handled in expandTableLikeClause() later.
*/
static void
transformTableLikeClause(CreateStmtContext *cxt, TableLikeClause *table_like_clause)
@@ -1131,12 +1137,6 @@ transformTableLikeClause(CreateStmtContext *cxt, TableLikeClause *table_like_cla
setup_parser_errposition_callback(&pcbstate, cxt->pstate,
table_like_clause->relation->location);
- /* we could support LIKE in many cases, but worry about it another day */
- if (cxt->isforeign)
- ereport(ERROR,
- (errcode(ERRCODE_FEATURE_NOT_SUPPORTED),
- errmsg("LIKE is not supported for creating foreign tables")));
-
/* Open the relation referenced by the LIKE clause */
relation = relation_openrv(table_like_clause->relation, AccessShareLock);
@@ -1242,8 +1242,9 @@ transformTableLikeClause(CreateStmtContext *cxt, TableLikeClause *table_like_cla
def->storage = 0;
/* Likewise, copy compression if requested */
- if ((table_like_clause->options & CREATE_TABLE_LIKE_COMPRESSION) != 0
- && CompressionMethodIsValid(attribute->attcompression))
+ if ((table_like_clause->options & CREATE_TABLE_LIKE_COMPRESSION) != 0 &&
+ CompressionMethodIsValid(attribute->attcompression) &&
+ !cxt->isforeign)
def->compression =
pstrdup(GetCompressionMethodName(attribute->attcompression));
else
@@ -1522,7 +1523,8 @@ expandTableLikeClause(RangeVar *heapRel, TableLikeClause *table_like_clause)
* Process indexes if required.
*/
if ((table_like_clause->options & CREATE_TABLE_LIKE_INDEXES) &&
- relation->rd_rel->relhasindex)
+ relation->rd_rel->relhasindex &&
+ childrel->rd_rel->relkind != RELKIND_FOREIGN_TABLE)
{
List *parent_indexes;
ListCell *l;
diff --git a/src/test/regress/expected/create_table_like.out b/src/test/regress/expected/create_table_like.out
index e061389135..8ddadd57e2 100644
--- a/src/test/regress/expected/create_table_like.out
+++ b/src/test/regress/expected/create_table_like.out
@@ -563,3 +563,122 @@ DROP TYPE ctlty1;
DROP VIEW ctlv1;
DROP TABLE IF EXISTS ctlt4, ctlt10, ctlt11, ctlt11a, ctlt12;
NOTICE: table "ctlt10" does not exist, skipping
+--
+-- CREATE FOREIGN TABLE LIKE
+--
+CREATE FOREIGN DATA WRAPPER ctl_dummy;
+CREATE SERVER ctl_s0 FOREIGN DATA WRAPPER ctl_dummy;
+CREATE TABLE ctl_table(a int DEFAULT 1, b text,
+ c int GENERATED ALWAYS AS (a * 2) STORED,
+ d bigint GENERATED ALWAYS AS IDENTITY);
+CREATE INDEX ctl_table_a_key ON ctl_table(a);
+COMMENT ON COLUMN ctl_table.b IS 'Column b';
+CREATE STATISTICS ctl_table_stat ON a,b FROM ctl_table;
+ALTER TABLE ctl_table add constraint foo CHECK (b = 'text');
+\d+ ctl_table
+ Table "public.ctl_table"
+ Column | Type | Collation | Nullable | Default | Storage | Stats target | Description
+--------+---------+-----------+----------+------------------------------------+----------+--------------+-------------
+ a | integer | | | 1 | plain | |
+ b | text | | | | extended | | Column b
+ c | integer | | | generated always as (a * 2) stored | plain | |
+ d | bigint | | not null | generated always as identity | plain | |
+Indexes:
+ "ctl_table_a_key" btree (a)
+Check constraints:
+ "foo" CHECK (b = 'text'::text)
+Statistics objects:
+ "public.ctl_table_stat" ON a, b FROM ctl_table
+Not-null constraints:
+ "ctl_table_d_not_null" NOT NULL "d"
+
+-- Test EXCLUDING ALL by default
+CREATE FOREIGN TABLE ctl_foreign_table1(LIKE ctl_table) SERVER ctl_s0;
+\d+ ctl_foreign_table1
+ Foreign table "public.ctl_foreign_table1"
+ Column | Type | Collation | Nullable | Default | FDW options | Storage | Stats target | Description
+--------+---------+-----------+----------+---------+-------------+----------+--------------+-------------
+ a | integer | | | | | plain | |
+ b | text | | | | | extended | |
+ c | integer | | | | | plain | |
+ d | bigint | | not null | | | plain | |
+Not-null constraints:
+ "ctl_table_d_not_null" NOT NULL "d"
+Server: ctl_s0
+
+-- Test INCLUDING INDEXES does't take effect
+CREATE FOREIGN TABLE ctl_foreign_table2(LIKE ctl_table INCLUDING INDEXES) SERVER ctl_s0;
+\d+ ctl_foreign_table2
+ Foreign table "public.ctl_foreign_table2"
+ Column | Type | Collation | Nullable | Default | FDW options | Storage | Stats target | Description
+--------+---------+-----------+----------+---------+-------------+----------+--------------+-------------
+ a | integer | | | | | plain | |
+ b | text | | | | | extended | |
+ c | integer | | | | | plain | |
+ d | bigint | | not null | | | plain | |
+Not-null constraints:
+ "ctl_table_d_not_null" NOT NULL "d"
+Server: ctl_s0
+
+-- Test INCLUDING COMPRESSION does't take effect
+\set HIDE_TOAST_COMPRESSION false
+ALTER TABLE ctl_table ALTER COLUMN b TYPE varchar;
+ALTER TABLE ctl_table ALTER COLUMN b SET COMPRESSION pglz;
+\d+ ctl_table
+ Table "public.ctl_table"
+ Column | Type | Collation | Nullable | Default | Storage | Compression | Stats target | Description
+--------+-------------------+-----------+----------+------------------------------------+----------+-------------+--------------+-------------
+ a | integer | | | 1 | plain | | |
+ b | character varying | | | | extended | pglz | | Column b
+ c | integer | | | generated always as (a * 2) stored | plain | | |
+ d | bigint | | not null | generated always as identity | plain | | |
+Indexes:
+ "ctl_table_a_key" btree (a)
+Check constraints:
+ "foo" CHECK (b::text = 'text'::text)
+Statistics objects:
+ "public.ctl_table_stat" ON a, b FROM ctl_table
+Not-null constraints:
+ "ctl_table_d_not_null" NOT NULL "d"
+
+CREATE FOREIGN TABLE ctl_foreign_table3(LIKE ctl_table INCLUDING COMPRESSION) SERVER ctl_s0;
+\d+ ctl_foreign_table3
+ Foreign table "public.ctl_foreign_table3"
+ Column | Type | Collation | Nullable | Default | FDW options | Storage | Stats target | Description
+--------+-------------------+-----------+----------+---------+-------------+----------+--------------+-------------
+ a | integer | | | | | plain | |
+ b | character varying | | | | | extended | |
+ c | integer | | | | | plain | |
+ d | bigint | | not null | | | plain | |
+Not-null constraints:
+ "ctl_table_d_not_null" NOT NULL "d"
+Server: ctl_s0
+
+\set HIDE_TOAST_COMPRESSION true
+-- Test valid like_options take effect:
+-- COMMENTS, STATISTICS, CONSTRAINTS
+-- GENERATED, DEFAULTS, IDENTITY, STORAGE
+CREATE FOREIGN TABLE ctl_foreign_table4(LIKE ctl_table INCLUDING ALL) SERVER ctl_s0;
+\d+ ctl_foreign_table4
+ Foreign table "public.ctl_foreign_table4"
+ Column | Type | Collation | Nullable | Default | FDW options | Storage | Stats target | Description
+--------+-------------------+-----------+----------+------------------------------------+-------------+----------+--------------+-------------
+ a | integer | | | 1 | | plain | |
+ b | character varying | | | | | extended | | Column b
+ c | integer | | | generated always as (a * 2) stored | | plain | |
+ d | bigint | | not null | generated always as identity | | plain | |
+Check constraints:
+ "foo" CHECK (b::text = 'text'::text)
+Statistics objects:
+ "public.ctl_foreign_table4_a_b_stat" ON a, b FROM ctl_foreign_table4
+Not-null constraints:
+ "ctl_table_d_not_null" NOT NULL "d"
+Server: ctl_s0
+
+DROP TABLE ctl_table;
+DROP FOREIGN TABLE ctl_foreign_table1;
+DROP FOREIGN TABLE ctl_foreign_table2;
+DROP FOREIGN TABLE ctl_foreign_table3;
+DROP FOREIGN TABLE ctl_foreign_table4;
+DROP FOREIGN DATA WRAPPER ctl_dummy CASCADE;
+NOTICE: drop cascades to server ctl_s0
diff --git a/src/test/regress/sql/create_table_like.sql b/src/test/regress/sql/create_table_like.sql
index a41f8b83d7..95b2d09d64 100644
--- a/src/test/regress/sql/create_table_like.sql
+++ b/src/test/regress/sql/create_table_like.sql
@@ -225,3 +225,49 @@ DROP SEQUENCE ctlseq1;
DROP TYPE ctlty1;
DROP VIEW ctlv1;
DROP TABLE IF EXISTS ctlt4, ctlt10, ctlt11, ctlt11a, ctlt12;
+
+--
+-- CREATE FOREIGN TABLE LIKE
+--
+CREATE FOREIGN DATA WRAPPER ctl_dummy;
+CREATE SERVER ctl_s0 FOREIGN DATA WRAPPER ctl_dummy;
+
+CREATE TABLE ctl_table(a int DEFAULT 1, b text,
+ c int GENERATED ALWAYS AS (a * 2) STORED,
+ d bigint GENERATED ALWAYS AS IDENTITY);
+
+CREATE INDEX ctl_table_a_key ON ctl_table(a);
+COMMENT ON COLUMN ctl_table.b IS 'Column b';
+CREATE STATISTICS ctl_table_stat ON a,b FROM ctl_table;
+ALTER TABLE ctl_table add constraint foo CHECK (b = 'text');
+\d+ ctl_table
+
+-- Test EXCLUDING ALL by default
+CREATE FOREIGN TABLE ctl_foreign_table1(LIKE ctl_table) SERVER ctl_s0;
+\d+ ctl_foreign_table1
+
+-- Test INCLUDING INDEXES does't take effect
+CREATE FOREIGN TABLE ctl_foreign_table2(LIKE ctl_table INCLUDING INDEXES) SERVER ctl_s0;
+\d+ ctl_foreign_table2
+
+-- Test INCLUDING COMPRESSION does't take effect
+\set HIDE_TOAST_COMPRESSION false
+ALTER TABLE ctl_table ALTER COLUMN b TYPE varchar;
+ALTER TABLE ctl_table ALTER COLUMN b SET COMPRESSION pglz;
+\d+ ctl_table
+CREATE FOREIGN TABLE ctl_foreign_table3(LIKE ctl_table INCLUDING COMPRESSION) SERVER ctl_s0;
+\d+ ctl_foreign_table3
+\set HIDE_TOAST_COMPRESSION true
+
+-- Test valid like_options take effect:
+-- COMMENTS, STATISTICS, CONSTRAINTS
+-- GENERATED, DEFAULTS, IDENTITY, STORAGE
+CREATE FOREIGN TABLE ctl_foreign_table4(LIKE ctl_table INCLUDING ALL) SERVER ctl_s0;
+\d+ ctl_foreign_table4
+
+DROP TABLE ctl_table;
+DROP FOREIGN TABLE ctl_foreign_table1;
+DROP FOREIGN TABLE ctl_foreign_table2;
+DROP FOREIGN TABLE ctl_foreign_table3;
+DROP FOREIGN TABLE ctl_foreign_table4;
+DROP FOREIGN DATA WRAPPER ctl_dummy CASCADE;
--
2.34.1
Re: Proposal to CREATE FOREIGN TABLE LIKE
Fix CI failure of doc build in v1 patch.
Thanks for the patch! I am +1 for this, but I have a few comments:
1/ In the IDENTITY case, the remote side may not be
able to handle the DEFAULT value. See the example below:
-- on the foreign server
postgres=# CREATE TABLE t2 (id int, c1 text);
CREATE TABLE
-- on the local server
postgres=#
postgres=# CREATE TABLE t1 (id int GENERATED ALWAYS AS IDENTITY, c1 text);
CREATE TABLE
postgres=# CREATE FOREIGN TABLE t2 (LIKE t1 INCLUDING INDEXES) server r1;
CREATE FOREIGN TABLE
postgres=# INSERT INTO t2 (c1) VALUES ('A');
INSERT 0 1
postgres=# SELECT * FROM t2;
id | c1
----+----
| A
(1 row)
This is also the reason foreign tables don't document IDENTITY as valid [1]https://www.postgresql.org/docs/current/sql-createforeigntable.html.
It may even be a bug for it to be allowed with the CREATE FOREIGN TABLE
syntax:
postgres=# CREATE FOREIGN TABLE t3 (id int GENERATED ALWAYS AS
IDENTITY, c1 text) server r1;
CREATE FOREIGN TABLE
postgres=# \d+ t3
Foreign table "public.t3"
Column | Type | Collation | Nullable | Default
| FDW options | Storage | Stats target | Description
--------+---------+-----------+----------+------------------------------+-------------+----------+--------------+-------------
id | integer | | not null | generated always as
identity | | plain | |
c1 | text | | |
| | extended | |
Not-null constraints:
"t3_id_not_null" NOT NULL "id"
Server: r1
2/ As IDENTITY, STORAGE is also allowed on foreign tables, which
does not make much sense either, as the fdw may not be Postgres,
or if it is Postgres, it may have a different STORAGE setting. This is
also not documented. I am inclined to say to not include it either.
I think we should not allow IDENTITY and STORAGE in this patch
as they are not documented [1]https://www.postgresql.org/docs/current/sql-createforeigntable.html as is, and perhaps a separate discussion
to correct the behavior for the CREATE FOREIGN TABLE case.
3/ a minor nit on the comments.
instead of
+ Foreign tables have no real storage in PostgreSQL.
Can it just be
Foreign tables have no storage in PostgreSQL.
the "real" is not needed.
[1]: https://www.postgresql.org/docs/current/sql-createforeigntable.html
--
Regards,
Sami
Re: Proposal to CREATE FOREIGN TABLE LIKE
On Feb 8, 2025 at 12:55 +0800, Sami Imseih <samimseih@gmail.com>, wrote:
Fix CI failure of doc build in v1 patch.
Thanks for the patch! I am +1 for this, but I have a few comments:
Hi, tanks for review.
1/ In the IDENTITY case, the remote side may not be
able to handle the DEFAULT value.
Yes, and done.
2/ As IDENTITY, STORAGE is also allowed on foreign tables, which
does not make much sense either, as the fdw may not be Postgres,
or if it is Postgres, it may have a different STORAGE setting. This is
also not documented. I am inclined to say to not include it either.
Done.
I think we should not allow IDENTITY and STORAGE in this patch
as they are not documented [1] as is, and perhaps a separate discussion
to correct the behavior for the CREATE FOREIGN TABLE case.
Yes, however, I found we have doc to tell users and they actually could ALTER FOREIGN TABLE to SET STORAGE...
There are several inconsistencies, I have a summary in Inconsistency between Compression and Storage for Foreign Tables[0] /messages/by-id/6cecef0e-ee14-473c-bb0a-6aa61f539a66@Spark
We could discuss and fix them there.
3/ a minor nit on the comments.
instead of
+ Foreign tables have no real storage in PostgreSQL.
Can it just be
Foreign tables have no storage in PostgreSQL.
the "real" is not needed.
Done.
Patch V2 addressed the comments.
[0]: /messages/by-id/6cecef0e-ee14-473c-bb0a-6aa61f539a66@Spark
--
Zhang Mingli
HashData
Attachments:
v2-0001-CREATE-FOREIGN-TABLE-LIKE.patchapplication/octet-streamDownload
From 9ca09b68bade7b5f97009cd47c99d6634ef475ad Mon Sep 17 00:00:00 2001
From: Zhang Mingli <avamingli@gmail.com>
Date: Sat, 8 Feb 2025 00:46:17 +0800
Subject: [PATCH] CREATE FOREIGN TABLE LIKE
Added support for CREATE FOREIGN TABLE LIKE to enable the creation of
foreign tables based on the column definitions, constraints of existing
source tables.
This feature mirrors the behavior of CREATE TABLE LIKE,
but ignores inapplicable options such as INCLUDING INDEXES,
INCLUDING COMPRESSION, INCLUDING IDENTITY, INCLUDING STORAGE
for foreign tables.
Authored-by: Zhang Mingli avamingli@gmail.com
---
doc/src/sgml/ref/create_foreign_table.sgml | 118 ++++++++++++-
src/backend/parser/parse_utilcmd.c | 28 +--
.../regress/expected/create_table_like.out | 165 ++++++++++++++++++
src/test/regress/sql/create_table_like.sql | 57 ++++++
4 files changed, 356 insertions(+), 12 deletions(-)
diff --git a/doc/src/sgml/ref/create_foreign_table.sgml b/doc/src/sgml/ref/create_foreign_table.sgml
index 0dcd9ca6f8..e9ab0a2b53 100644
--- a/doc/src/sgml/ref/create_foreign_table.sgml
+++ b/doc/src/sgml/ref/create_foreign_table.sgml
@@ -23,7 +23,8 @@ PostgreSQL documentation
<synopsis>
CREATE FOREIGN TABLE [ IF NOT EXISTS ] <replaceable class="parameter">table_name</replaceable> ( [
{ <replaceable class="parameter">column_name</replaceable> <replaceable class="parameter">data_type</replaceable> [ OPTIONS ( <replaceable class="parameter">option</replaceable> '<replaceable class="parameter">value</replaceable>' [, ... ] ) ] [ COLLATE <replaceable>collation</replaceable> ] [ <replaceable class="parameter">column_constraint</replaceable> [ ... ] ]
- | <replaceable>table_constraint</replaceable> }
+ | <replaceable>table_constraint</replaceable>
+ | LIKE <replaceable>source_table</replaceable> [ <replaceable>like_option</replaceable> ... ] }
[, ... ]
] )
[ INHERITS ( <replaceable>parent_table</replaceable> [, ... ] ) ]
@@ -57,6 +58,10 @@ CREATE FOREIGN TABLE [ IF NOT EXISTS ] <replaceable class="parameter">table_name
CHECK ( <replaceable class="parameter">expression</replaceable> ) [ NO INHERIT ] }
[ ENFORCED | NOT ENFORCED ]
+<phrase>and <replaceable class="parameter">like_option</replaceable> is:</phrase>
+
+{ INCLUDING | EXCLUDING } { COMMENTS | CONSTRAINTS | DEFAULTS | GENERATED | STATISTICS | ALL }
+
<phrase>and <replaceable class="parameter">partition_bound_spec</replaceable> is:</phrase>
IN ( <replaceable class="parameter">partition_bound_expr</replaceable> [, ...] ) |
@@ -191,6 +196,117 @@ WITH ( MODULUS <replaceable class="parameter">numeric_literal</replaceable>, REM
</listitem>
</varlistentry>
+ <varlistentry>
+ <term><literal>LIKE <replaceable>source_table</replaceable> [ <replaceable>like_option</replaceable> ... ]</literal></term>
+ <listitem>
+ <para>
+ The <literal>LIKE</literal> clause specifies a table from which
+ the new table automatically copies all column names, their data types,
+ and their not-null constraints.
+ </para>
+ <para>
+ Unlike <literal>INHERITS</literal>, the new table and original table
+ are completely decoupled after creation is complete. Changes to the
+ original table will not be applied to the new table, and it is not
+ possible to include data of the new table in scans of the original
+ table.
+ </para>
+ <para>
+ Also unlike <literal>INHERITS</literal>, columns and
+ constraints copied by <literal>LIKE</literal> are not merged with similarly
+ named columns and constraints.
+ If the same name is specified explicitly or in another
+ <literal>LIKE</literal> clause, an error is signaled.
+ </para>
+ <para>
+ The optional <replaceable>like_option</replaceable> clauses specify
+ which additional properties of the original table to copy. Specifying
+ <literal>INCLUDING</literal> copies the property, specifying
+ <literal>EXCLUDING</literal> omits the property.
+ <literal>EXCLUDING</literal> is the default. If multiple specifications
+ are made for the same kind of object, the last one is used. The
+ available options are:
+
+ <variablelist>
+ <varlistentry>
+ <term><literal>INCLUDING COMMENTS</literal></term>
+ <listitem>
+ <para>
+ Comments for the copied columns, constraints, and indexes will be
+ copied. The default behavior is to exclude comments, resulting in
+ the copied columns and constraints in the new table having no
+ comments.
+ </para>
+ </listitem>
+ </varlistentry>
+
+ <varlistentry>
+ <term><literal>INCLUDING CONSTRAINTS</literal></term>
+ <listitem>
+ <para>
+ <literal>CHECK</literal> constraints will be copied. No distinction
+ is made between column constraints and table constraints. Not-null
+ constraints are always copied to the new table.
+ </para>
+ </listitem>
+ </varlistentry>
+
+ <varlistentry>
+ <term><literal>INCLUDING DEFAULTS</literal></term>
+ <listitem>
+ <para>
+ Default expressions for the copied column definitions will be
+ copied. Otherwise, default expressions are not copied, resulting in
+ the copied columns in the new table having null defaults. Note that
+ copying defaults that call database-modification functions, such as
+ <function>nextval</function>, may create a functional linkage
+ between the original and new tables.
+ </para>
+ </listitem>
+ </varlistentry>
+
+ <varlistentry>
+ <term><literal>INCLUDING GENERATED</literal></term>
+ <listitem>
+ <para>
+ Any generation expressions of copied column definitions will be
+ copied. By default, new columns will be regular base columns.
+ </para>
+ </listitem>
+ </varlistentry>
+
+ <varlistentry>
+ <term><literal>INCLUDING STATISTICS</literal></term>
+ <listitem>
+ <para>
+ Extended statistics are copied to the new table.
+ </para>
+ </listitem>
+ </varlistentry>
+
+ <varlistentry>
+ <term><literal>INCLUDING ALL</literal></term>
+ <listitem>
+ <para>
+ <literal>INCLUDING ALL</literal> is an abbreviated form selecting
+ all the available individual options. (It could be useful to write
+ individual <literal>EXCLUDING</literal> clauses after
+ <literal>INCLUDING ALL</literal> to select all but some specific
+ options.)
+ </para>
+ </listitem>
+ </varlistentry>
+ </variablelist>
+ </para>
+
+ <para>
+ Foreign tables have no real storage in PostgreSQL.
+ Inapplicable options: <literal>INCLUDING INDEXES</literal>, <literal>INCLUDING STORAGE</literal>,
+ <literal>INCLUDING COMPRESSION</literal>, <literal>INCLUDING IDENTITY</literal> are ignored.
+ </para>
+ </listitem>
+ </varlistentry>
+
<varlistentry>
<term><literal>CONSTRAINT <replaceable class="parameter">constraint_name</replaceable></literal></term>
<listitem>
diff --git a/src/backend/parser/parse_utilcmd.c b/src/backend/parser/parse_utilcmd.c
index ca028d2a66..f6db43e034 100644
--- a/src/backend/parser/parse_utilcmd.c
+++ b/src/backend/parser/parse_utilcmd.c
@@ -1117,6 +1117,14 @@ transformTableConstraint(CreateStmtContext *cxt, Constraint *constraint)
* process at this point, add the TableLikeClause to cxt->likeclauses, which
* will cause utility.c to call expandTableLikeClause() after the new
* table has been created.
+ *
+ * For foreign tables, they have no storage in Postgres.
+ * Inapplicable options are ignored:
+ * CREATE_TABLE_LIKE_COMPRESSION
+ * CREATE_TABLE_LIKE_IDENTITY
+ * CREATE_TABLE_LIKE_STORAGE
+ * CREATE_TABLE_LIKE_INDEXES
+ * while INDEXES is handled in expandTableLikeClause() later.
*/
static void
transformTableLikeClause(CreateStmtContext *cxt, TableLikeClause *table_like_clause)
@@ -1131,12 +1139,6 @@ transformTableLikeClause(CreateStmtContext *cxt, TableLikeClause *table_like_cla
setup_parser_errposition_callback(&pcbstate, cxt->pstate,
table_like_clause->relation->location);
- /* we could support LIKE in many cases, but worry about it another day */
- if (cxt->isforeign)
- ereport(ERROR,
- (errcode(ERRCODE_FEATURE_NOT_SUPPORTED),
- errmsg("LIKE is not supported for creating foreign tables")));
-
/* Open the relation referenced by the LIKE clause */
relation = relation_openrv(table_like_clause->relation, AccessShareLock);
@@ -1217,7 +1219,8 @@ transformTableLikeClause(CreateStmtContext *cxt, TableLikeClause *table_like_cla
* Copy identity if requested
*/
if (attribute->attidentity &&
- (table_like_clause->options & CREATE_TABLE_LIKE_IDENTITY))
+ (table_like_clause->options & CREATE_TABLE_LIKE_IDENTITY) &&
+ !cxt->isforeign)
{
Oid seq_relid;
List *seq_options;
@@ -1236,14 +1239,16 @@ transformTableLikeClause(CreateStmtContext *cxt, TableLikeClause *table_like_cla
}
/* Likewise, copy storage if requested */
- if (table_like_clause->options & CREATE_TABLE_LIKE_STORAGE)
+ if ((table_like_clause->options & CREATE_TABLE_LIKE_STORAGE) &&
+ !cxt->isforeign)
def->storage = attribute->attstorage;
else
def->storage = 0;
/* Likewise, copy compression if requested */
- if ((table_like_clause->options & CREATE_TABLE_LIKE_COMPRESSION) != 0
- && CompressionMethodIsValid(attribute->attcompression))
+ if ((table_like_clause->options & CREATE_TABLE_LIKE_COMPRESSION) != 0 &&
+ CompressionMethodIsValid(attribute->attcompression) &&
+ !cxt->isforeign)
def->compression =
pstrdup(GetCompressionMethodName(attribute->attcompression));
else
@@ -1522,7 +1527,8 @@ expandTableLikeClause(RangeVar *heapRel, TableLikeClause *table_like_clause)
* Process indexes if required.
*/
if ((table_like_clause->options & CREATE_TABLE_LIKE_INDEXES) &&
- relation->rd_rel->relhasindex)
+ relation->rd_rel->relhasindex &&
+ childrel->rd_rel->relkind != RELKIND_FOREIGN_TABLE)
{
List *parent_indexes;
ListCell *l;
diff --git a/src/test/regress/expected/create_table_like.out b/src/test/regress/expected/create_table_like.out
index e061389135..4048cd19db 100644
--- a/src/test/regress/expected/create_table_like.out
+++ b/src/test/regress/expected/create_table_like.out
@@ -563,3 +563,168 @@ DROP TYPE ctlty1;
DROP VIEW ctlv1;
DROP TABLE IF EXISTS ctlt4, ctlt10, ctlt11, ctlt11a, ctlt12;
NOTICE: table "ctlt10" does not exist, skipping
+--
+-- CREATE FOREIGN TABLE LIKE
+--
+CREATE FOREIGN DATA WRAPPER ctl_dummy;
+CREATE SERVER ctl_s0 FOREIGN DATA WRAPPER ctl_dummy;
+CREATE TABLE ctl_table(a int DEFAULT 1, b text,
+ c int GENERATED ALWAYS AS (a * 2) STORED,
+ d bigint GENERATED ALWAYS AS IDENTITY);
+CREATE INDEX ctl_table_a_key ON ctl_table(a);
+COMMENT ON COLUMN ctl_table.b IS 'Column b';
+CREATE STATISTICS ctl_table_stat ON a,b FROM ctl_table;
+ALTER TABLE ctl_table add constraint foo CHECK (b = 'text');
+\d+ ctl_table
+ Table "public.ctl_table"
+ Column | Type | Collation | Nullable | Default | Storage | Stats target | Description
+--------+---------+-----------+----------+------------------------------------+----------+--------------+-------------
+ a | integer | | | 1 | plain | |
+ b | text | | | | extended | | Column b
+ c | integer | | | generated always as (a * 2) stored | plain | |
+ d | bigint | | not null | generated always as identity | plain | |
+Indexes:
+ "ctl_table_a_key" btree (a)
+Check constraints:
+ "foo" CHECK (b = 'text'::text)
+Statistics objects:
+ "public.ctl_table_stat" ON a, b FROM ctl_table
+Not-null constraints:
+ "ctl_table_d_not_null" NOT NULL "d"
+
+-- Test EXCLUDING ALL by default
+CREATE FOREIGN TABLE ctl_foreign_table1(LIKE ctl_table) SERVER ctl_s0;
+\d+ ctl_foreign_table1
+ Foreign table "public.ctl_foreign_table1"
+ Column | Type | Collation | Nullable | Default | FDW options | Storage | Stats target | Description
+--------+---------+-----------+----------+---------+-------------+----------+--------------+-------------
+ a | integer | | | | | plain | |
+ b | text | | | | | extended | |
+ c | integer | | | | | plain | |
+ d | bigint | | not null | | | plain | |
+Not-null constraints:
+ "ctl_table_d_not_null" NOT NULL "d"
+Server: ctl_s0
+
+-- Test INCLUDING INDEXES does't take effect
+CREATE FOREIGN TABLE ctl_foreign_table2(LIKE ctl_table INCLUDING INDEXES) SERVER ctl_s0;
+\d+ ctl_foreign_table2
+ Foreign table "public.ctl_foreign_table2"
+ Column | Type | Collation | Nullable | Default | FDW options | Storage | Stats target | Description
+--------+---------+-----------+----------+---------+-------------+----------+--------------+-------------
+ a | integer | | | | | plain | |
+ b | text | | | | | extended | |
+ c | integer | | | | | plain | |
+ d | bigint | | not null | | | plain | |
+Not-null constraints:
+ "ctl_table_d_not_null" NOT NULL "d"
+Server: ctl_s0
+
+-- Test INCLUDING IDENTITY does't take effect
+CREATE FOREIGN TABLE ctl_foreign_table3(LIKE ctl_table INCLUDING IDENTITY) SERVER ctl_s0;
+\d+ ctl_foreign_table3
+ Foreign table "public.ctl_foreign_table3"
+ Column | Type | Collation | Nullable | Default | FDW options | Storage | Stats target | Description
+--------+---------+-----------+----------+---------+-------------+----------+--------------+-------------
+ a | integer | | | | | plain | |
+ b | text | | | | | extended | |
+ c | integer | | | | | plain | |
+ d | bigint | | not null | | | plain | |
+Not-null constraints:
+ "ctl_table_d_not_null" NOT NULL "d"
+Server: ctl_s0
+
+-- Test INCLUDING COMPRESSION does't take effect
+\set HIDE_TOAST_COMPRESSION false
+ALTER TABLE ctl_table ALTER COLUMN b TYPE varchar;
+ALTER TABLE ctl_table ALTER COLUMN b SET COMPRESSION pglz;
+\d+ ctl_table
+ Table "public.ctl_table"
+ Column | Type | Collation | Nullable | Default | Storage | Compression | Stats target | Description
+--------+-------------------+-----------+----------+------------------------------------+----------+-------------+--------------+-------------
+ a | integer | | | 1 | plain | | |
+ b | character varying | | | | extended | pglz | | Column b
+ c | integer | | | generated always as (a * 2) stored | plain | | |
+ d | bigint | | not null | generated always as identity | plain | | |
+Indexes:
+ "ctl_table_a_key" btree (a)
+Check constraints:
+ "foo" CHECK (b::text = 'text'::text)
+Statistics objects:
+ "public.ctl_table_stat" ON a, b FROM ctl_table
+Not-null constraints:
+ "ctl_table_d_not_null" NOT NULL "d"
+
+CREATE FOREIGN TABLE ctl_foreign_table4(LIKE ctl_table INCLUDING COMPRESSION) SERVER ctl_s0;
+\d+ ctl_foreign_table4
+ Foreign table "public.ctl_foreign_table4"
+ Column | Type | Collation | Nullable | Default | FDW options | Storage | Stats target | Description
+--------+-------------------+-----------+----------+---------+-------------+----------+--------------+-------------
+ a | integer | | | | | plain | |
+ b | character varying | | | | | extended | |
+ c | integer | | | | | plain | |
+ d | bigint | | not null | | | plain | |
+Not-null constraints:
+ "ctl_table_d_not_null" NOT NULL "d"
+Server: ctl_s0
+
+\set HIDE_TOAST_COMPRESSION true
+-- Test INCLUDING STORAGE does't take effect
+ALTER TABLE ctl_table ALTER COLUMN b SET STORAGE external;
+\d+ ctl_table
+ Table "public.ctl_table"
+ Column | Type | Collation | Nullable | Default | Storage | Stats target | Description
+--------+-------------------+-----------+----------+------------------------------------+----------+--------------+-------------
+ a | integer | | | 1 | plain | |
+ b | character varying | | | | external | | Column b
+ c | integer | | | generated always as (a * 2) stored | plain | |
+ d | bigint | | not null | generated always as identity | plain | |
+Indexes:
+ "ctl_table_a_key" btree (a)
+Check constraints:
+ "foo" CHECK (b::text = 'text'::text)
+Statistics objects:
+ "public.ctl_table_stat" ON a, b FROM ctl_table
+Not-null constraints:
+ "ctl_table_d_not_null" NOT NULL "d"
+
+CREATE FOREIGN TABLE ctl_foreign_table5(LIKE ctl_table INCLUDING STORAGE) SERVER ctl_s0;
+\d+ ctl_foreign_table5
+ Foreign table "public.ctl_foreign_table5"
+ Column | Type | Collation | Nullable | Default | FDW options | Storage | Stats target | Description
+--------+-------------------+-----------+----------+---------+-------------+----------+--------------+-------------
+ a | integer | | | | | plain | |
+ b | character varying | | | | | extended | |
+ c | integer | | | | | plain | |
+ d | bigint | | not null | | | plain | |
+Not-null constraints:
+ "ctl_table_d_not_null" NOT NULL "d"
+Server: ctl_s0
+
+-- Test valid like_options take effect:
+CREATE FOREIGN TABLE ctl_foreign_table6(LIKE ctl_table INCLUDING ALL) SERVER ctl_s0;
+\d+ ctl_foreign_table6
+ Foreign table "public.ctl_foreign_table6"
+ Column | Type | Collation | Nullable | Default | FDW options | Storage | Stats target | Description
+--------+-------------------+-----------+----------+------------------------------------+-------------+----------+--------------+-------------
+ a | integer | | | 1 | | plain | |
+ b | character varying | | | | | extended | | Column b
+ c | integer | | | generated always as (a * 2) stored | | plain | |
+ d | bigint | | not null | | | plain | |
+Check constraints:
+ "foo" CHECK (b::text = 'text'::text)
+Statistics objects:
+ "public.ctl_foreign_table6_a_b_stat" ON a, b FROM ctl_foreign_table6
+Not-null constraints:
+ "ctl_table_d_not_null" NOT NULL "d"
+Server: ctl_s0
+
+DROP TABLE ctl_table;
+DROP FOREIGN TABLE ctl_foreign_table1;
+DROP FOREIGN TABLE ctl_foreign_table2;
+DROP FOREIGN TABLE ctl_foreign_table3;
+DROP FOREIGN TABLE ctl_foreign_table4;
+DROP FOREIGN TABLE ctl_foreign_table5;
+DROP FOREIGN TABLE ctl_foreign_table6;
+DROP FOREIGN DATA WRAPPER ctl_dummy CASCADE;
+NOTICE: drop cascades to server ctl_s0
diff --git a/src/test/regress/sql/create_table_like.sql b/src/test/regress/sql/create_table_like.sql
index a41f8b83d7..ea586af438 100644
--- a/src/test/regress/sql/create_table_like.sql
+++ b/src/test/regress/sql/create_table_like.sql
@@ -225,3 +225,60 @@ DROP SEQUENCE ctlseq1;
DROP TYPE ctlty1;
DROP VIEW ctlv1;
DROP TABLE IF EXISTS ctlt4, ctlt10, ctlt11, ctlt11a, ctlt12;
+
+--
+-- CREATE FOREIGN TABLE LIKE
+--
+CREATE FOREIGN DATA WRAPPER ctl_dummy;
+CREATE SERVER ctl_s0 FOREIGN DATA WRAPPER ctl_dummy;
+
+CREATE TABLE ctl_table(a int DEFAULT 1, b text,
+ c int GENERATED ALWAYS AS (a * 2) STORED,
+ d bigint GENERATED ALWAYS AS IDENTITY);
+
+CREATE INDEX ctl_table_a_key ON ctl_table(a);
+COMMENT ON COLUMN ctl_table.b IS 'Column b';
+CREATE STATISTICS ctl_table_stat ON a,b FROM ctl_table;
+ALTER TABLE ctl_table add constraint foo CHECK (b = 'text');
+\d+ ctl_table
+
+-- Test EXCLUDING ALL by default
+CREATE FOREIGN TABLE ctl_foreign_table1(LIKE ctl_table) SERVER ctl_s0;
+\d+ ctl_foreign_table1
+
+-- Test INCLUDING INDEXES does't take effect
+CREATE FOREIGN TABLE ctl_foreign_table2(LIKE ctl_table INCLUDING INDEXES) SERVER ctl_s0;
+\d+ ctl_foreign_table2
+
+-- Test INCLUDING IDENTITY does't take effect
+CREATE FOREIGN TABLE ctl_foreign_table3(LIKE ctl_table INCLUDING IDENTITY) SERVER ctl_s0;
+\d+ ctl_foreign_table3
+
+-- Test INCLUDING COMPRESSION does't take effect
+\set HIDE_TOAST_COMPRESSION false
+ALTER TABLE ctl_table ALTER COLUMN b TYPE varchar;
+ALTER TABLE ctl_table ALTER COLUMN b SET COMPRESSION pglz;
+\d+ ctl_table
+CREATE FOREIGN TABLE ctl_foreign_table4(LIKE ctl_table INCLUDING COMPRESSION) SERVER ctl_s0;
+\d+ ctl_foreign_table4
+\set HIDE_TOAST_COMPRESSION true
+
+-- Test INCLUDING STORAGE does't take effect
+ALTER TABLE ctl_table ALTER COLUMN b SET STORAGE external;
+\d+ ctl_table
+CREATE FOREIGN TABLE ctl_foreign_table5(LIKE ctl_table INCLUDING STORAGE) SERVER ctl_s0;
+\d+ ctl_foreign_table5
+
+
+-- Test valid like_options take effect:
+CREATE FOREIGN TABLE ctl_foreign_table6(LIKE ctl_table INCLUDING ALL) SERVER ctl_s0;
+\d+ ctl_foreign_table6
+
+DROP TABLE ctl_table;
+DROP FOREIGN TABLE ctl_foreign_table1;
+DROP FOREIGN TABLE ctl_foreign_table2;
+DROP FOREIGN TABLE ctl_foreign_table3;
+DROP FOREIGN TABLE ctl_foreign_table4;
+DROP FOREIGN TABLE ctl_foreign_table5;
+DROP FOREIGN TABLE ctl_foreign_table6;
+DROP FOREIGN DATA WRAPPER ctl_dummy CASCADE;
--
2.34.1
Re: Proposal to CREATE FOREIGN TABLE LIKE
Patch V2 addressed the comments.
Overall this LGTM.
I still see a "no real storage" in v2 that should be removed
from the documentation.
+ Foreign tables have no real storage in PostgreSQL.
+ Inapplicable options: <literal>INCLUDING INDEXES</literal>,
<literal>INCLUDING STORAGE</literal>,I think the test coverage to check for the negative conditions only is
enough.
Regards,
Sami
Re: Proposal to CREATE FOREIGN TABLE LIKE
On Feb 11, 2025 at 08:14 +0800, Sami Imseih <samimseih@gmail.com>, wrote:
Patch V2 addressed the comments.
Overall this LGTM.
I still see a "no real storage" in v2 that should be removed
from the documentation.+ Foreign tables have no real storage in PostgreSQL. + Inapplicable options: <literal>INCLUDING INDEXES</literal>, <literal>INCLUDING STORAGE</literal>,
Oh, I corrected another one in the code comments, but I forgot about this one.
Done in patch v3.
I think the test coverage to check for the negative conditions only is
enough.
Hmm... I copied from the cases in the same file for each option.
There's no harm in having more tests, how about we keep them?
--
Zhang Mingli
HashData
Attachments:
v3-0001-CREATE-FOREIGN-TABLE-LIKE.patchapplication/octet-streamDownload
From 5e7fe83b4f684aad0179a91520c8eb901459de85 Mon Sep 17 00:00:00 2001
From: Zhang Mingli <avamingli@gmail.com>
Date: Tue, 11 Feb 2025 22:38:49 +0800
Subject: [PATCH] CREATE FOREIGN TABLE LIKE
Added support for CREATE FOREIGN TABLE LIKE to enable the creation of
foreign tables based on the column definitions, constraints of existing
source tables.
This feature mirrors the behavior of CREATE TABLE LIKE,
but ignores inapplicable options such as INCLUDING INDEXES,
INCLUDING COMPRESSION, INCLUDING IDENTITY, INCLUDING STORAGE
for foreign tables.
Authored-by: Zhang Mingli avamingli@gmail.com
---
doc/src/sgml/ref/create_foreign_table.sgml | 118 ++++++++++++-
src/backend/parser/parse_utilcmd.c | 28 +--
.../regress/expected/create_table_like.out | 165 ++++++++++++++++++
src/test/regress/sql/create_table_like.sql | 57 ++++++
4 files changed, 356 insertions(+), 12 deletions(-)
diff --git a/doc/src/sgml/ref/create_foreign_table.sgml b/doc/src/sgml/ref/create_foreign_table.sgml
index 0dcd9ca6f8..5bc0a11009 100644
--- a/doc/src/sgml/ref/create_foreign_table.sgml
+++ b/doc/src/sgml/ref/create_foreign_table.sgml
@@ -23,7 +23,8 @@ PostgreSQL documentation
<synopsis>
CREATE FOREIGN TABLE [ IF NOT EXISTS ] <replaceable class="parameter">table_name</replaceable> ( [
{ <replaceable class="parameter">column_name</replaceable> <replaceable class="parameter">data_type</replaceable> [ OPTIONS ( <replaceable class="parameter">option</replaceable> '<replaceable class="parameter">value</replaceable>' [, ... ] ) ] [ COLLATE <replaceable>collation</replaceable> ] [ <replaceable class="parameter">column_constraint</replaceable> [ ... ] ]
- | <replaceable>table_constraint</replaceable> }
+ | <replaceable>table_constraint</replaceable>
+ | LIKE <replaceable>source_table</replaceable> [ <replaceable>like_option</replaceable> ... ] }
[, ... ]
] )
[ INHERITS ( <replaceable>parent_table</replaceable> [, ... ] ) ]
@@ -57,6 +58,10 @@ CREATE FOREIGN TABLE [ IF NOT EXISTS ] <replaceable class="parameter">table_name
CHECK ( <replaceable class="parameter">expression</replaceable> ) [ NO INHERIT ] }
[ ENFORCED | NOT ENFORCED ]
+<phrase>and <replaceable class="parameter">like_option</replaceable> is:</phrase>
+
+{ INCLUDING | EXCLUDING } { COMMENTS | CONSTRAINTS | DEFAULTS | GENERATED | STATISTICS | ALL }
+
<phrase>and <replaceable class="parameter">partition_bound_spec</replaceable> is:</phrase>
IN ( <replaceable class="parameter">partition_bound_expr</replaceable> [, ...] ) |
@@ -191,6 +196,117 @@ WITH ( MODULUS <replaceable class="parameter">numeric_literal</replaceable>, REM
</listitem>
</varlistentry>
+ <varlistentry>
+ <term><literal>LIKE <replaceable>source_table</replaceable> [ <replaceable>like_option</replaceable> ... ]</literal></term>
+ <listitem>
+ <para>
+ The <literal>LIKE</literal> clause specifies a table from which
+ the new table automatically copies all column names, their data types,
+ and their not-null constraints.
+ </para>
+ <para>
+ Unlike <literal>INHERITS</literal>, the new table and original table
+ are completely decoupled after creation is complete. Changes to the
+ original table will not be applied to the new table, and it is not
+ possible to include data of the new table in scans of the original
+ table.
+ </para>
+ <para>
+ Also unlike <literal>INHERITS</literal>, columns and
+ constraints copied by <literal>LIKE</literal> are not merged with similarly
+ named columns and constraints.
+ If the same name is specified explicitly or in another
+ <literal>LIKE</literal> clause, an error is signaled.
+ </para>
+ <para>
+ The optional <replaceable>like_option</replaceable> clauses specify
+ which additional properties of the original table to copy. Specifying
+ <literal>INCLUDING</literal> copies the property, specifying
+ <literal>EXCLUDING</literal> omits the property.
+ <literal>EXCLUDING</literal> is the default. If multiple specifications
+ are made for the same kind of object, the last one is used. The
+ available options are:
+
+ <variablelist>
+ <varlistentry>
+ <term><literal>INCLUDING COMMENTS</literal></term>
+ <listitem>
+ <para>
+ Comments for the copied columns, constraints, and indexes will be
+ copied. The default behavior is to exclude comments, resulting in
+ the copied columns and constraints in the new table having no
+ comments.
+ </para>
+ </listitem>
+ </varlistentry>
+
+ <varlistentry>
+ <term><literal>INCLUDING CONSTRAINTS</literal></term>
+ <listitem>
+ <para>
+ <literal>CHECK</literal> constraints will be copied. No distinction
+ is made between column constraints and table constraints. Not-null
+ constraints are always copied to the new table.
+ </para>
+ </listitem>
+ </varlistentry>
+
+ <varlistentry>
+ <term><literal>INCLUDING DEFAULTS</literal></term>
+ <listitem>
+ <para>
+ Default expressions for the copied column definitions will be
+ copied. Otherwise, default expressions are not copied, resulting in
+ the copied columns in the new table having null defaults. Note that
+ copying defaults that call database-modification functions, such as
+ <function>nextval</function>, may create a functional linkage
+ between the original and new tables.
+ </para>
+ </listitem>
+ </varlistentry>
+
+ <varlistentry>
+ <term><literal>INCLUDING GENERATED</literal></term>
+ <listitem>
+ <para>
+ Any generation expressions of copied column definitions will be
+ copied. By default, new columns will be regular base columns.
+ </para>
+ </listitem>
+ </varlistentry>
+
+ <varlistentry>
+ <term><literal>INCLUDING STATISTICS</literal></term>
+ <listitem>
+ <para>
+ Extended statistics are copied to the new table.
+ </para>
+ </listitem>
+ </varlistentry>
+
+ <varlistentry>
+ <term><literal>INCLUDING ALL</literal></term>
+ <listitem>
+ <para>
+ <literal>INCLUDING ALL</literal> is an abbreviated form selecting
+ all the available individual options. (It could be useful to write
+ individual <literal>EXCLUDING</literal> clauses after
+ <literal>INCLUDING ALL</literal> to select all but some specific
+ options.)
+ </para>
+ </listitem>
+ </varlistentry>
+ </variablelist>
+ </para>
+
+ <para>
+ Foreign tables have no storage in PostgreSQL.
+ Inapplicable options: <literal>INCLUDING INDEXES</literal>, <literal>INCLUDING STORAGE</literal>,
+ <literal>INCLUDING COMPRESSION</literal>, <literal>INCLUDING IDENTITY</literal> are ignored.
+ </para>
+ </listitem>
+ </varlistentry>
+
<varlistentry>
<term><literal>CONSTRAINT <replaceable class="parameter">constraint_name</replaceable></literal></term>
<listitem>
diff --git a/src/backend/parser/parse_utilcmd.c b/src/backend/parser/parse_utilcmd.c
index ca028d2a66..f6db43e034 100644
--- a/src/backend/parser/parse_utilcmd.c
+++ b/src/backend/parser/parse_utilcmd.c
@@ -1117,6 +1117,14 @@ transformTableConstraint(CreateStmtContext *cxt, Constraint *constraint)
* process at this point, add the TableLikeClause to cxt->likeclauses, which
* will cause utility.c to call expandTableLikeClause() after the new
* table has been created.
+ *
+ * For foreign tables, they have no storage in Postgres.
+ * Inapplicable options are ignored:
+ * CREATE_TABLE_LIKE_COMPRESSION
+ * CREATE_TABLE_LIKE_IDENTITY
+ * CREATE_TABLE_LIKE_STORAGE
+ * CREATE_TABLE_LIKE_INDEXES
+ * while INDEXES is handled in expandTableLikeClause() later.
*/
static void
transformTableLikeClause(CreateStmtContext *cxt, TableLikeClause *table_like_clause)
@@ -1131,12 +1139,6 @@ transformTableLikeClause(CreateStmtContext *cxt, TableLikeClause *table_like_cla
setup_parser_errposition_callback(&pcbstate, cxt->pstate,
table_like_clause->relation->location);
- /* we could support LIKE in many cases, but worry about it another day */
- if (cxt->isforeign)
- ereport(ERROR,
- (errcode(ERRCODE_FEATURE_NOT_SUPPORTED),
- errmsg("LIKE is not supported for creating foreign tables")));
-
/* Open the relation referenced by the LIKE clause */
relation = relation_openrv(table_like_clause->relation, AccessShareLock);
@@ -1217,7 +1219,8 @@ transformTableLikeClause(CreateStmtContext *cxt, TableLikeClause *table_like_cla
* Copy identity if requested
*/
if (attribute->attidentity &&
- (table_like_clause->options & CREATE_TABLE_LIKE_IDENTITY))
+ (table_like_clause->options & CREATE_TABLE_LIKE_IDENTITY) &&
+ !cxt->isforeign)
{
Oid seq_relid;
List *seq_options;
@@ -1236,14 +1239,16 @@ transformTableLikeClause(CreateStmtContext *cxt, TableLikeClause *table_like_cla
}
/* Likewise, copy storage if requested */
- if (table_like_clause->options & CREATE_TABLE_LIKE_STORAGE)
+ if ((table_like_clause->options & CREATE_TABLE_LIKE_STORAGE) &&
+ !cxt->isforeign)
def->storage = attribute->attstorage;
else
def->storage = 0;
/* Likewise, copy compression if requested */
- if ((table_like_clause->options & CREATE_TABLE_LIKE_COMPRESSION) != 0
- && CompressionMethodIsValid(attribute->attcompression))
+ if ((table_like_clause->options & CREATE_TABLE_LIKE_COMPRESSION) != 0 &&
+ CompressionMethodIsValid(attribute->attcompression) &&
+ !cxt->isforeign)
def->compression =
pstrdup(GetCompressionMethodName(attribute->attcompression));
else
@@ -1522,7 +1527,8 @@ expandTableLikeClause(RangeVar *heapRel, TableLikeClause *table_like_clause)
* Process indexes if required.
*/
if ((table_like_clause->options & CREATE_TABLE_LIKE_INDEXES) &&
- relation->rd_rel->relhasindex)
+ relation->rd_rel->relhasindex &&
+ childrel->rd_rel->relkind != RELKIND_FOREIGN_TABLE)
{
List *parent_indexes;
ListCell *l;
diff --git a/src/test/regress/expected/create_table_like.out b/src/test/regress/expected/create_table_like.out
index e061389135..4048cd19db 100644
--- a/src/test/regress/expected/create_table_like.out
+++ b/src/test/regress/expected/create_table_like.out
@@ -563,3 +563,168 @@ DROP TYPE ctlty1;
DROP VIEW ctlv1;
DROP TABLE IF EXISTS ctlt4, ctlt10, ctlt11, ctlt11a, ctlt12;
NOTICE: table "ctlt10" does not exist, skipping
+--
+-- CREATE FOREIGN TABLE LIKE
+--
+CREATE FOREIGN DATA WRAPPER ctl_dummy;
+CREATE SERVER ctl_s0 FOREIGN DATA WRAPPER ctl_dummy;
+CREATE TABLE ctl_table(a int DEFAULT 1, b text,
+ c int GENERATED ALWAYS AS (a * 2) STORED,
+ d bigint GENERATED ALWAYS AS IDENTITY);
+CREATE INDEX ctl_table_a_key ON ctl_table(a);
+COMMENT ON COLUMN ctl_table.b IS 'Column b';
+CREATE STATISTICS ctl_table_stat ON a,b FROM ctl_table;
+ALTER TABLE ctl_table add constraint foo CHECK (b = 'text');
+\d+ ctl_table
+ Table "public.ctl_table"
+ Column | Type | Collation | Nullable | Default | Storage | Stats target | Description
+--------+---------+-----------+----------+------------------------------------+----------+--------------+-------------
+ a | integer | | | 1 | plain | |
+ b | text | | | | extended | | Column b
+ c | integer | | | generated always as (a * 2) stored | plain | |
+ d | bigint | | not null | generated always as identity | plain | |
+Indexes:
+ "ctl_table_a_key" btree (a)
+Check constraints:
+ "foo" CHECK (b = 'text'::text)
+Statistics objects:
+ "public.ctl_table_stat" ON a, b FROM ctl_table
+Not-null constraints:
+ "ctl_table_d_not_null" NOT NULL "d"
+
+-- Test EXCLUDING ALL by default
+CREATE FOREIGN TABLE ctl_foreign_table1(LIKE ctl_table) SERVER ctl_s0;
+\d+ ctl_foreign_table1
+ Foreign table "public.ctl_foreign_table1"
+ Column | Type | Collation | Nullable | Default | FDW options | Storage | Stats target | Description
+--------+---------+-----------+----------+---------+-------------+----------+--------------+-------------
+ a | integer | | | | | plain | |
+ b | text | | | | | extended | |
+ c | integer | | | | | plain | |
+ d | bigint | | not null | | | plain | |
+Not-null constraints:
+ "ctl_table_d_not_null" NOT NULL "d"
+Server: ctl_s0
+
+-- Test INCLUDING INDEXES does't take effect
+CREATE FOREIGN TABLE ctl_foreign_table2(LIKE ctl_table INCLUDING INDEXES) SERVER ctl_s0;
+\d+ ctl_foreign_table2
+ Foreign table "public.ctl_foreign_table2"
+ Column | Type | Collation | Nullable | Default | FDW options | Storage | Stats target | Description
+--------+---------+-----------+----------+---------+-------------+----------+--------------+-------------
+ a | integer | | | | | plain | |
+ b | text | | | | | extended | |
+ c | integer | | | | | plain | |
+ d | bigint | | not null | | | plain | |
+Not-null constraints:
+ "ctl_table_d_not_null" NOT NULL "d"
+Server: ctl_s0
+
+-- Test INCLUDING IDENTITY does't take effect
+CREATE FOREIGN TABLE ctl_foreign_table3(LIKE ctl_table INCLUDING IDENTITY) SERVER ctl_s0;
+\d+ ctl_foreign_table3
+ Foreign table "public.ctl_foreign_table3"
+ Column | Type | Collation | Nullable | Default | FDW options | Storage | Stats target | Description
+--------+---------+-----------+----------+---------+-------------+----------+--------------+-------------
+ a | integer | | | | | plain | |
+ b | text | | | | | extended | |
+ c | integer | | | | | plain | |
+ d | bigint | | not null | | | plain | |
+Not-null constraints:
+ "ctl_table_d_not_null" NOT NULL "d"
+Server: ctl_s0
+
+-- Test INCLUDING COMPRESSION does't take effect
+\set HIDE_TOAST_COMPRESSION false
+ALTER TABLE ctl_table ALTER COLUMN b TYPE varchar;
+ALTER TABLE ctl_table ALTER COLUMN b SET COMPRESSION pglz;
+\d+ ctl_table
+ Table "public.ctl_table"
+ Column | Type | Collation | Nullable | Default | Storage | Compression | Stats target | Description
+--------+-------------------+-----------+----------+------------------------------------+----------+-------------+--------------+-------------
+ a | integer | | | 1 | plain | | |
+ b | character varying | | | | extended | pglz | | Column b
+ c | integer | | | generated always as (a * 2) stored | plain | | |
+ d | bigint | | not null | generated always as identity | plain | | |
+Indexes:
+ "ctl_table_a_key" btree (a)
+Check constraints:
+ "foo" CHECK (b::text = 'text'::text)
+Statistics objects:
+ "public.ctl_table_stat" ON a, b FROM ctl_table
+Not-null constraints:
+ "ctl_table_d_not_null" NOT NULL "d"
+
+CREATE FOREIGN TABLE ctl_foreign_table4(LIKE ctl_table INCLUDING COMPRESSION) SERVER ctl_s0;
+\d+ ctl_foreign_table4
+ Foreign table "public.ctl_foreign_table4"
+ Column | Type | Collation | Nullable | Default | FDW options | Storage | Stats target | Description
+--------+-------------------+-----------+----------+---------+-------------+----------+--------------+-------------
+ a | integer | | | | | plain | |
+ b | character varying | | | | | extended | |
+ c | integer | | | | | plain | |
+ d | bigint | | not null | | | plain | |
+Not-null constraints:
+ "ctl_table_d_not_null" NOT NULL "d"
+Server: ctl_s0
+
+\set HIDE_TOAST_COMPRESSION true
+-- Test INCLUDING STORAGE does't take effect
+ALTER TABLE ctl_table ALTER COLUMN b SET STORAGE external;
+\d+ ctl_table
+ Table "public.ctl_table"
+ Column | Type | Collation | Nullable | Default | Storage | Stats target | Description
+--------+-------------------+-----------+----------+------------------------------------+----------+--------------+-------------
+ a | integer | | | 1 | plain | |
+ b | character varying | | | | external | | Column b
+ c | integer | | | generated always as (a * 2) stored | plain | |
+ d | bigint | | not null | generated always as identity | plain | |
+Indexes:
+ "ctl_table_a_key" btree (a)
+Check constraints:
+ "foo" CHECK (b::text = 'text'::text)
+Statistics objects:
+ "public.ctl_table_stat" ON a, b FROM ctl_table
+Not-null constraints:
+ "ctl_table_d_not_null" NOT NULL "d"
+
+CREATE FOREIGN TABLE ctl_foreign_table5(LIKE ctl_table INCLUDING STORAGE) SERVER ctl_s0;
+\d+ ctl_foreign_table5
+ Foreign table "public.ctl_foreign_table5"
+ Column | Type | Collation | Nullable | Default | FDW options | Storage | Stats target | Description
+--------+-------------------+-----------+----------+---------+-------------+----------+--------------+-------------
+ a | integer | | | | | plain | |
+ b | character varying | | | | | extended | |
+ c | integer | | | | | plain | |
+ d | bigint | | not null | | | plain | |
+Not-null constraints:
+ "ctl_table_d_not_null" NOT NULL "d"
+Server: ctl_s0
+
+-- Test valid like_options take effect:
+CREATE FOREIGN TABLE ctl_foreign_table6(LIKE ctl_table INCLUDING ALL) SERVER ctl_s0;
+\d+ ctl_foreign_table6
+ Foreign table "public.ctl_foreign_table6"
+ Column | Type | Collation | Nullable | Default | FDW options | Storage | Stats target | Description
+--------+-------------------+-----------+----------+------------------------------------+-------------+----------+--------------+-------------
+ a | integer | | | 1 | | plain | |
+ b | character varying | | | | | extended | | Column b
+ c | integer | | | generated always as (a * 2) stored | | plain | |
+ d | bigint | | not null | | | plain | |
+Check constraints:
+ "foo" CHECK (b::text = 'text'::text)
+Statistics objects:
+ "public.ctl_foreign_table6_a_b_stat" ON a, b FROM ctl_foreign_table6
+Not-null constraints:
+ "ctl_table_d_not_null" NOT NULL "d"
+Server: ctl_s0
+
+DROP TABLE ctl_table;
+DROP FOREIGN TABLE ctl_foreign_table1;
+DROP FOREIGN TABLE ctl_foreign_table2;
+DROP FOREIGN TABLE ctl_foreign_table3;
+DROP FOREIGN TABLE ctl_foreign_table4;
+DROP FOREIGN TABLE ctl_foreign_table5;
+DROP FOREIGN TABLE ctl_foreign_table6;
+DROP FOREIGN DATA WRAPPER ctl_dummy CASCADE;
+NOTICE: drop cascades to server ctl_s0
diff --git a/src/test/regress/sql/create_table_like.sql b/src/test/regress/sql/create_table_like.sql
index a41f8b83d7..ea586af438 100644
--- a/src/test/regress/sql/create_table_like.sql
+++ b/src/test/regress/sql/create_table_like.sql
@@ -225,3 +225,60 @@ DROP SEQUENCE ctlseq1;
DROP TYPE ctlty1;
DROP VIEW ctlv1;
DROP TABLE IF EXISTS ctlt4, ctlt10, ctlt11, ctlt11a, ctlt12;
+
+--
+-- CREATE FOREIGN TABLE LIKE
+--
+CREATE FOREIGN DATA WRAPPER ctl_dummy;
+CREATE SERVER ctl_s0 FOREIGN DATA WRAPPER ctl_dummy;
+
+CREATE TABLE ctl_table(a int DEFAULT 1, b text,
+ c int GENERATED ALWAYS AS (a * 2) STORED,
+ d bigint GENERATED ALWAYS AS IDENTITY);
+
+CREATE INDEX ctl_table_a_key ON ctl_table(a);
+COMMENT ON COLUMN ctl_table.b IS 'Column b';
+CREATE STATISTICS ctl_table_stat ON a,b FROM ctl_table;
+ALTER TABLE ctl_table add constraint foo CHECK (b = 'text');
+\d+ ctl_table
+
+-- Test EXCLUDING ALL by default
+CREATE FOREIGN TABLE ctl_foreign_table1(LIKE ctl_table) SERVER ctl_s0;
+\d+ ctl_foreign_table1
+
+-- Test INCLUDING INDEXES does't take effect
+CREATE FOREIGN TABLE ctl_foreign_table2(LIKE ctl_table INCLUDING INDEXES) SERVER ctl_s0;
+\d+ ctl_foreign_table2
+
+-- Test INCLUDING IDENTITY does't take effect
+CREATE FOREIGN TABLE ctl_foreign_table3(LIKE ctl_table INCLUDING IDENTITY) SERVER ctl_s0;
+\d+ ctl_foreign_table3
+
+-- Test INCLUDING COMPRESSION does't take effect
+\set HIDE_TOAST_COMPRESSION false
+ALTER TABLE ctl_table ALTER COLUMN b TYPE varchar;
+ALTER TABLE ctl_table ALTER COLUMN b SET COMPRESSION pglz;
+\d+ ctl_table
+CREATE FOREIGN TABLE ctl_foreign_table4(LIKE ctl_table INCLUDING COMPRESSION) SERVER ctl_s0;
+\d+ ctl_foreign_table4
+\set HIDE_TOAST_COMPRESSION true
+
+-- Test INCLUDING STORAGE does't take effect
+ALTER TABLE ctl_table ALTER COLUMN b SET STORAGE external;
+\d+ ctl_table
+CREATE FOREIGN TABLE ctl_foreign_table5(LIKE ctl_table INCLUDING STORAGE) SERVER ctl_s0;
+\d+ ctl_foreign_table5
+
+
+-- Test valid like_options take effect:
+CREATE FOREIGN TABLE ctl_foreign_table6(LIKE ctl_table INCLUDING ALL) SERVER ctl_s0;
+\d+ ctl_foreign_table6
+
+DROP TABLE ctl_table;
+DROP FOREIGN TABLE ctl_foreign_table1;
+DROP FOREIGN TABLE ctl_foreign_table2;
+DROP FOREIGN TABLE ctl_foreign_table3;
+DROP FOREIGN TABLE ctl_foreign_table4;
+DROP FOREIGN TABLE ctl_foreign_table5;
+DROP FOREIGN TABLE ctl_foreign_table6;
+DROP FOREIGN DATA WRAPPER ctl_dummy CASCADE;
--
2.34.1
Re: Proposal to CREATE FOREIGN TABLE LIKE
+ Foreign tables have no real storage in PostgreSQL. + Inapplicable options: <literal>INCLUDING INDEXES</literal>, <literal>INCLUDING STORAGE</literal>,Oh, I corrected another one in the code comments, but I forgot about this one.
Done in patch v3.
I attached v4 with some slight modifications to the wording, otherwise
this looks good.
I think the test coverage to check for the negative conditions only is
enough.Hmm... I copied from the cases in the same file for each option.
There's no harm in having more tests, how about we keep them?
I agree. I was just saying the test cases you provided are
enough. No changes needed for the tests.
I have no further comments.
Regards,
Sami
Attachments:
v4-0001-CREATE-FOREIGN-TABLE-LIKE.patchapplication/octet-stream; name=v4-0001-CREATE-FOREIGN-TABLE-LIKE.patchDownload
From d8ebbf455c4c402c6e5103a0424411bfcef93a13 Mon Sep 17 00:00:00 2001
From: Zhang Mingli <avamingli@gmail.com>
Date: Tue, 11 Feb 2025 15:51:58 +0000
Subject: [PATCH v4 1/1] CREATE FOREIGN TABLE LIKE
Added support for CREATE FOREIGN TABLE LIKE to enable the creation of
foreign tables based on the column definitions, constraints of existing
source tables.
This feature mirrors the behavior of CREATE TABLE LIKE,
but ignores inapplicable options such as INCLUDING INDEXES,
INCLUDING COMPRESSION, INCLUDING IDENTITY, INCLUDING STORAGE
for foreign tables.
Authored-by: Zhang Mingli avamingli@gmail.com
---
doc/src/sgml/ref/create_foreign_table.sgml | 118 ++++++++++++-
src/backend/parser/parse_utilcmd.c | 28 +--
.../regress/expected/create_table_like.out | 165 ++++++++++++++++++
src/test/regress/sql/create_table_like.sql | 57 ++++++
4 files changed, 356 insertions(+), 12 deletions(-)
diff --git a/doc/src/sgml/ref/create_foreign_table.sgml b/doc/src/sgml/ref/create_foreign_table.sgml
index e0b0e075c2..12e6c1817f 100644
--- a/doc/src/sgml/ref/create_foreign_table.sgml
+++ b/doc/src/sgml/ref/create_foreign_table.sgml
@@ -23,7 +23,8 @@ PostgreSQL documentation
<synopsis>
CREATE FOREIGN TABLE [ IF NOT EXISTS ] <replaceable class="parameter">table_name</replaceable> ( [
{ <replaceable class="parameter">column_name</replaceable> <replaceable class="parameter">data_type</replaceable> [ OPTIONS ( <replaceable class="parameter">option</replaceable> '<replaceable class="parameter">value</replaceable>' [, ... ] ) ] [ COLLATE <replaceable>collation</replaceable> ] [ <replaceable class="parameter">column_constraint</replaceable> [ ... ] ]
- | <replaceable>table_constraint</replaceable> }
+ | <replaceable>table_constraint</replaceable>
+ | LIKE <replaceable>source_table</replaceable> [ <replaceable>like_option</replaceable> ... ] }
[, ... ]
] )
[ INHERITS ( <replaceable>parent_table</replaceable> [, ... ] ) ]
@@ -57,6 +58,10 @@ CREATE FOREIGN TABLE [ IF NOT EXISTS ] <replaceable class="parameter">table_name
CHECK ( <replaceable class="parameter">expression</replaceable> ) [ NO INHERIT ] }
[ ENFORCED | NOT ENFORCED ]
+<phrase>and <replaceable class="parameter">like_option</replaceable> is:</phrase>
+
+{ INCLUDING | EXCLUDING } { COMMENTS | CONSTRAINTS | DEFAULTS | GENERATED | STATISTICS | ALL }
+
<phrase>and <replaceable class="parameter">partition_bound_spec</replaceable> is:</phrase>
IN ( <replaceable class="parameter">partition_bound_expr</replaceable> [, ...] ) |
@@ -191,6 +196,117 @@ WITH ( MODULUS <replaceable class="parameter">numeric_literal</replaceable>, REM
</listitem>
</varlistentry>
+ <varlistentry>
+ <term><literal>LIKE <replaceable>source_table</replaceable> [ <replaceable>like_option</replaceable> ... ]</literal></term>
+ <listitem>
+ <para>
+ The <literal>LIKE</literal> clause specifies a table from which
+ the new table automatically copies all column names, their data types,
+ and their not-null constraints.
+ </para>
+ <para>
+ Unlike <literal>INHERITS</literal>, the new table and original table
+ are completely decoupled after creation is complete. Changes to the
+ original table will not be applied to the new table, and it is not
+ possible to include data of the new table in scans of the original
+ table.
+ </para>
+ <para>
+ Also unlike <literal>INHERITS</literal>, columns and
+ constraints copied by <literal>LIKE</literal> are not merged with similarly
+ named columns and constraints.
+ If the same name is specified explicitly or in another
+ <literal>LIKE</literal> clause, an error is signaled.
+ </para>
+ <para>
+ The optional <replaceable>like_option</replaceable> clauses specify
+ which additional properties of the original table to copy. Specifying
+ <literal>INCLUDING</literal> copies the property, specifying
+ <literal>EXCLUDING</literal> omits the property.
+ <literal>EXCLUDING</literal> is the default. If multiple specifications
+ are made for the same kind of object, the last one is used. The
+ available options are:
+
+ <variablelist>
+ <varlistentry>
+ <term><literal>INCLUDING COMMENTS</literal></term>
+ <listitem>
+ <para>
+ Comments for the copied columns, constraints, and indexes will be
+ copied. The default behavior is to exclude comments, resulting in
+ the copied columns and constraints in the new table having no
+ comments.
+ </para>
+ </listitem>
+ </varlistentry>
+
+ <varlistentry>
+ <term><literal>INCLUDING CONSTRAINTS</literal></term>
+ <listitem>
+ <para>
+ <literal>CHECK</literal> constraints will be copied. No distinction
+ is made between column constraints and table constraints. Not-null
+ constraints are always copied to the new table.
+ </para>
+ </listitem>
+ </varlistentry>
+
+ <varlistentry>
+ <term><literal>INCLUDING DEFAULTS</literal></term>
+ <listitem>
+ <para>
+ Default expressions for the copied column definitions will be
+ copied. Otherwise, default expressions are not copied, resulting in
+ the copied columns in the new table having null defaults. Note that
+ copying defaults that call database-modification functions, such as
+ <function>nextval</function>, may create a functional linkage
+ between the original and new tables.
+ </para>
+ </listitem>
+ </varlistentry>
+
+ <varlistentry>
+ <term><literal>INCLUDING GENERATED</literal></term>
+ <listitem>
+ <para>
+ Any generation expressions of copied column definitions will be
+ copied. By default, new columns will be regular base columns.
+ </para>
+ </listitem>
+ </varlistentry>
+
+ <varlistentry>
+ <term><literal>INCLUDING STATISTICS</literal></term>
+ <listitem>
+ <para>
+ Extended statistics are copied to the new table.
+ </para>
+ </listitem>
+ </varlistentry>
+
+ <varlistentry>
+ <term><literal>INCLUDING ALL</literal></term>
+ <listitem>
+ <para>
+ <literal>INCLUDING ALL</literal> is an abbreviated form selecting
+ all the available individual options. (It could be useful to write
+ individual <literal>EXCLUDING</literal> clauses after
+ <literal>INCLUDING ALL</literal> to select all but some specific
+ options.)
+ </para>
+ </listitem>
+ </varlistentry>
+ </variablelist>
+ </para>
+
+ <para>
+ Foreign tables have no storage of their own, therefore the options <literal>INCLUDING INDEXES</literal>,
+ <literal>INCLUDING STORAGE</literal>, <literal>INCLUDING COMPRESSION</literal> and <literal>INCLUDING IDENTITY</literal>
+ are inapplicable.
+ </para>
+ </listitem>
+ </varlistentry>
+
<varlistentry>
<term><literal>CONSTRAINT <replaceable class="parameter">constraint_name</replaceable></literal></term>
<listitem>
diff --git a/src/backend/parser/parse_utilcmd.c b/src/backend/parser/parse_utilcmd.c
index eb7716cd84..03deb9aba8 100644
--- a/src/backend/parser/parse_utilcmd.c
+++ b/src/backend/parser/parse_utilcmd.c
@@ -1131,6 +1131,14 @@ transformTableConstraint(CreateStmtContext *cxt, Constraint *constraint)
* process at this point, add the TableLikeClause to cxt->likeclauses, which
* will cause utility.c to call expandTableLikeClause() after the new
* table has been created.
+ *
+ * For foreign tables, they have no storage in Postgres.
+ * Inapplicable options are ignored:
+ * CREATE_TABLE_LIKE_COMPRESSION
+ * CREATE_TABLE_LIKE_IDENTITY
+ * CREATE_TABLE_LIKE_STORAGE
+ * CREATE_TABLE_LIKE_INDEXES
+ * while INDEXES is handled in expandTableLikeClause() later.
*/
static void
transformTableLikeClause(CreateStmtContext *cxt, TableLikeClause *table_like_clause)
@@ -1145,12 +1153,6 @@ transformTableLikeClause(CreateStmtContext *cxt, TableLikeClause *table_like_cla
setup_parser_errposition_callback(&pcbstate, cxt->pstate,
table_like_clause->relation->location);
- /* we could support LIKE in many cases, but worry about it another day */
- if (cxt->isforeign)
- ereport(ERROR,
- (errcode(ERRCODE_FEATURE_NOT_SUPPORTED),
- errmsg("LIKE is not supported for creating foreign tables")));
-
/* Open the relation referenced by the LIKE clause */
relation = relation_openrv(table_like_clause->relation, AccessShareLock);
@@ -1231,7 +1233,8 @@ transformTableLikeClause(CreateStmtContext *cxt, TableLikeClause *table_like_cla
* Copy identity if requested
*/
if (attribute->attidentity &&
- (table_like_clause->options & CREATE_TABLE_LIKE_IDENTITY))
+ (table_like_clause->options & CREATE_TABLE_LIKE_IDENTITY) &&
+ !cxt->isforeign)
{
Oid seq_relid;
List *seq_options;
@@ -1250,14 +1253,16 @@ transformTableLikeClause(CreateStmtContext *cxt, TableLikeClause *table_like_cla
}
/* Likewise, copy storage if requested */
- if (table_like_clause->options & CREATE_TABLE_LIKE_STORAGE)
+ if ((table_like_clause->options & CREATE_TABLE_LIKE_STORAGE) &&
+ !cxt->isforeign)
def->storage = attribute->attstorage;
else
def->storage = 0;
/* Likewise, copy compression if requested */
- if ((table_like_clause->options & CREATE_TABLE_LIKE_COMPRESSION) != 0
- && CompressionMethodIsValid(attribute->attcompression))
+ if ((table_like_clause->options & CREATE_TABLE_LIKE_COMPRESSION) != 0 &&
+ CompressionMethodIsValid(attribute->attcompression) &&
+ !cxt->isforeign)
def->compression =
pstrdup(GetCompressionMethodName(attribute->attcompression));
else
@@ -1536,7 +1541,8 @@ expandTableLikeClause(RangeVar *heapRel, TableLikeClause *table_like_clause)
* Process indexes if required.
*/
if ((table_like_clause->options & CREATE_TABLE_LIKE_INDEXES) &&
- relation->rd_rel->relhasindex)
+ relation->rd_rel->relhasindex &&
+ childrel->rd_rel->relkind != RELKIND_FOREIGN_TABLE)
{
List *parent_indexes;
ListCell *l;
diff --git a/src/test/regress/expected/create_table_like.out b/src/test/regress/expected/create_table_like.out
index 2cebe38243..396c98256e 100644
--- a/src/test/regress/expected/create_table_like.out
+++ b/src/test/regress/expected/create_table_like.out
@@ -566,3 +566,168 @@ DROP TYPE ctlty1;
DROP VIEW ctlv1;
DROP TABLE IF EXISTS ctlt4, ctlt10, ctlt11, ctlt11a, ctlt12;
NOTICE: table "ctlt10" does not exist, skipping
+--
+-- CREATE FOREIGN TABLE LIKE
+--
+CREATE FOREIGN DATA WRAPPER ctl_dummy;
+CREATE SERVER ctl_s0 FOREIGN DATA WRAPPER ctl_dummy;
+CREATE TABLE ctl_table(a int DEFAULT 1, b text,
+ c int GENERATED ALWAYS AS (a * 2) STORED,
+ d bigint GENERATED ALWAYS AS IDENTITY);
+CREATE INDEX ctl_table_a_key ON ctl_table(a);
+COMMENT ON COLUMN ctl_table.b IS 'Column b';
+CREATE STATISTICS ctl_table_stat ON a,b FROM ctl_table;
+ALTER TABLE ctl_table add constraint foo CHECK (b = 'text');
+\d+ ctl_table
+ Table "public.ctl_table"
+ Column | Type | Collation | Nullable | Default | Storage | Stats target | Description
+--------+---------+-----------+----------+------------------------------------+----------+--------------+-------------
+ a | integer | | | 1 | plain | |
+ b | text | | | | extended | | Column b
+ c | integer | | | generated always as (a * 2) stored | plain | |
+ d | bigint | | not null | generated always as identity | plain | |
+Indexes:
+ "ctl_table_a_key" btree (a)
+Check constraints:
+ "foo" CHECK (b = 'text'::text)
+Statistics objects:
+ "public.ctl_table_stat" ON a, b FROM ctl_table
+Not-null constraints:
+ "ctl_table_d_not_null" NOT NULL "d"
+
+-- Test EXCLUDING ALL by default
+CREATE FOREIGN TABLE ctl_foreign_table1(LIKE ctl_table) SERVER ctl_s0;
+\d+ ctl_foreign_table1
+ Foreign table "public.ctl_foreign_table1"
+ Column | Type | Collation | Nullable | Default | FDW options | Storage | Stats target | Description
+--------+---------+-----------+----------+---------+-------------+----------+--------------+-------------
+ a | integer | | | | | plain | |
+ b | text | | | | | extended | |
+ c | integer | | | | | plain | |
+ d | bigint | | not null | | | plain | |
+Not-null constraints:
+ "ctl_table_d_not_null" NOT NULL "d"
+Server: ctl_s0
+
+-- Test INCLUDING INDEXES does't take effect
+CREATE FOREIGN TABLE ctl_foreign_table2(LIKE ctl_table INCLUDING INDEXES) SERVER ctl_s0;
+\d+ ctl_foreign_table2
+ Foreign table "public.ctl_foreign_table2"
+ Column | Type | Collation | Nullable | Default | FDW options | Storage | Stats target | Description
+--------+---------+-----------+----------+---------+-------------+----------+--------------+-------------
+ a | integer | | | | | plain | |
+ b | text | | | | | extended | |
+ c | integer | | | | | plain | |
+ d | bigint | | not null | | | plain | |
+Not-null constraints:
+ "ctl_table_d_not_null" NOT NULL "d"
+Server: ctl_s0
+
+-- Test INCLUDING IDENTITY does't take effect
+CREATE FOREIGN TABLE ctl_foreign_table3(LIKE ctl_table INCLUDING IDENTITY) SERVER ctl_s0;
+\d+ ctl_foreign_table3
+ Foreign table "public.ctl_foreign_table3"
+ Column | Type | Collation | Nullable | Default | FDW options | Storage | Stats target | Description
+--------+---------+-----------+----------+---------+-------------+----------+--------------+-------------
+ a | integer | | | | | plain | |
+ b | text | | | | | extended | |
+ c | integer | | | | | plain | |
+ d | bigint | | not null | | | plain | |
+Not-null constraints:
+ "ctl_table_d_not_null" NOT NULL "d"
+Server: ctl_s0
+
+-- Test INCLUDING COMPRESSION does't take effect
+\set HIDE_TOAST_COMPRESSION false
+ALTER TABLE ctl_table ALTER COLUMN b TYPE varchar;
+ALTER TABLE ctl_table ALTER COLUMN b SET COMPRESSION pglz;
+\d+ ctl_table
+ Table "public.ctl_table"
+ Column | Type | Collation | Nullable | Default | Storage | Compression | Stats target | Description
+--------+-------------------+-----------+----------+------------------------------------+----------+-------------+--------------+-------------
+ a | integer | | | 1 | plain | | |
+ b | character varying | | | | extended | pglz | | Column b
+ c | integer | | | generated always as (a * 2) stored | plain | | |
+ d | bigint | | not null | generated always as identity | plain | | |
+Indexes:
+ "ctl_table_a_key" btree (a)
+Check constraints:
+ "foo" CHECK (b::text = 'text'::text)
+Statistics objects:
+ "public.ctl_table_stat" ON a, b FROM ctl_table
+Not-null constraints:
+ "ctl_table_d_not_null" NOT NULL "d"
+
+CREATE FOREIGN TABLE ctl_foreign_table4(LIKE ctl_table INCLUDING COMPRESSION) SERVER ctl_s0;
+\d+ ctl_foreign_table4
+ Foreign table "public.ctl_foreign_table4"
+ Column | Type | Collation | Nullable | Default | FDW options | Storage | Stats target | Description
+--------+-------------------+-----------+----------+---------+-------------+----------+--------------+-------------
+ a | integer | | | | | plain | |
+ b | character varying | | | | | extended | |
+ c | integer | | | | | plain | |
+ d | bigint | | not null | | | plain | |
+Not-null constraints:
+ "ctl_table_d_not_null" NOT NULL "d"
+Server: ctl_s0
+
+\set HIDE_TOAST_COMPRESSION true
+-- Test INCLUDING STORAGE does't take effect
+ALTER TABLE ctl_table ALTER COLUMN b SET STORAGE external;
+\d+ ctl_table
+ Table "public.ctl_table"
+ Column | Type | Collation | Nullable | Default | Storage | Stats target | Description
+--------+-------------------+-----------+----------+------------------------------------+----------+--------------+-------------
+ a | integer | | | 1 | plain | |
+ b | character varying | | | | external | | Column b
+ c | integer | | | generated always as (a * 2) stored | plain | |
+ d | bigint | | not null | generated always as identity | plain | |
+Indexes:
+ "ctl_table_a_key" btree (a)
+Check constraints:
+ "foo" CHECK (b::text = 'text'::text)
+Statistics objects:
+ "public.ctl_table_stat" ON a, b FROM ctl_table
+Not-null constraints:
+ "ctl_table_d_not_null" NOT NULL "d"
+
+CREATE FOREIGN TABLE ctl_foreign_table5(LIKE ctl_table INCLUDING STORAGE) SERVER ctl_s0;
+\d+ ctl_foreign_table5
+ Foreign table "public.ctl_foreign_table5"
+ Column | Type | Collation | Nullable | Default | FDW options | Storage | Stats target | Description
+--------+-------------------+-----------+----------+---------+-------------+----------+--------------+-------------
+ a | integer | | | | | plain | |
+ b | character varying | | | | | extended | |
+ c | integer | | | | | plain | |
+ d | bigint | | not null | | | plain | |
+Not-null constraints:
+ "ctl_table_d_not_null" NOT NULL "d"
+Server: ctl_s0
+
+-- Test valid like_options take effect:
+CREATE FOREIGN TABLE ctl_foreign_table6(LIKE ctl_table INCLUDING ALL) SERVER ctl_s0;
+\d+ ctl_foreign_table6
+ Foreign table "public.ctl_foreign_table6"
+ Column | Type | Collation | Nullable | Default | FDW options | Storage | Stats target | Description
+--------+-------------------+-----------+----------+------------------------------------+-------------+----------+--------------+-------------
+ a | integer | | | 1 | | plain | |
+ b | character varying | | | | | extended | | Column b
+ c | integer | | | generated always as (a * 2) stored | | plain | |
+ d | bigint | | not null | | | plain | |
+Check constraints:
+ "foo" CHECK (b::text = 'text'::text)
+Statistics objects:
+ "public.ctl_foreign_table6_a_b_stat" ON a, b FROM ctl_foreign_table6
+Not-null constraints:
+ "ctl_table_d_not_null" NOT NULL "d"
+Server: ctl_s0
+
+DROP TABLE ctl_table;
+DROP FOREIGN TABLE ctl_foreign_table1;
+DROP FOREIGN TABLE ctl_foreign_table2;
+DROP FOREIGN TABLE ctl_foreign_table3;
+DROP FOREIGN TABLE ctl_foreign_table4;
+DROP FOREIGN TABLE ctl_foreign_table5;
+DROP FOREIGN TABLE ctl_foreign_table6;
+DROP FOREIGN DATA WRAPPER ctl_dummy CASCADE;
+NOTICE: drop cascades to server ctl_s0
diff --git a/src/test/regress/sql/create_table_like.sql b/src/test/regress/sql/create_table_like.sql
index 63a6030365..285d42d5f2 100644
--- a/src/test/regress/sql/create_table_like.sql
+++ b/src/test/regress/sql/create_table_like.sql
@@ -225,3 +225,60 @@ DROP SEQUENCE ctlseq1;
DROP TYPE ctlty1;
DROP VIEW ctlv1;
DROP TABLE IF EXISTS ctlt4, ctlt10, ctlt11, ctlt11a, ctlt12;
+
+--
+-- CREATE FOREIGN TABLE LIKE
+--
+CREATE FOREIGN DATA WRAPPER ctl_dummy;
+CREATE SERVER ctl_s0 FOREIGN DATA WRAPPER ctl_dummy;
+
+CREATE TABLE ctl_table(a int DEFAULT 1, b text,
+ c int GENERATED ALWAYS AS (a * 2) STORED,
+ d bigint GENERATED ALWAYS AS IDENTITY);
+
+CREATE INDEX ctl_table_a_key ON ctl_table(a);
+COMMENT ON COLUMN ctl_table.b IS 'Column b';
+CREATE STATISTICS ctl_table_stat ON a,b FROM ctl_table;
+ALTER TABLE ctl_table add constraint foo CHECK (b = 'text');
+\d+ ctl_table
+
+-- Test EXCLUDING ALL by default
+CREATE FOREIGN TABLE ctl_foreign_table1(LIKE ctl_table) SERVER ctl_s0;
+\d+ ctl_foreign_table1
+
+-- Test INCLUDING INDEXES does't take effect
+CREATE FOREIGN TABLE ctl_foreign_table2(LIKE ctl_table INCLUDING INDEXES) SERVER ctl_s0;
+\d+ ctl_foreign_table2
+
+-- Test INCLUDING IDENTITY does't take effect
+CREATE FOREIGN TABLE ctl_foreign_table3(LIKE ctl_table INCLUDING IDENTITY) SERVER ctl_s0;
+\d+ ctl_foreign_table3
+
+-- Test INCLUDING COMPRESSION does't take effect
+\set HIDE_TOAST_COMPRESSION false
+ALTER TABLE ctl_table ALTER COLUMN b TYPE varchar;
+ALTER TABLE ctl_table ALTER COLUMN b SET COMPRESSION pglz;
+\d+ ctl_table
+CREATE FOREIGN TABLE ctl_foreign_table4(LIKE ctl_table INCLUDING COMPRESSION) SERVER ctl_s0;
+\d+ ctl_foreign_table4
+\set HIDE_TOAST_COMPRESSION true
+
+-- Test INCLUDING STORAGE does't take effect
+ALTER TABLE ctl_table ALTER COLUMN b SET STORAGE external;
+\d+ ctl_table
+CREATE FOREIGN TABLE ctl_foreign_table5(LIKE ctl_table INCLUDING STORAGE) SERVER ctl_s0;
+\d+ ctl_foreign_table5
+
+
+-- Test valid like_options take effect:
+CREATE FOREIGN TABLE ctl_foreign_table6(LIKE ctl_table INCLUDING ALL) SERVER ctl_s0;
+\d+ ctl_foreign_table6
+
+DROP TABLE ctl_table;
+DROP FOREIGN TABLE ctl_foreign_table1;
+DROP FOREIGN TABLE ctl_foreign_table2;
+DROP FOREIGN TABLE ctl_foreign_table3;
+DROP FOREIGN TABLE ctl_foreign_table4;
+DROP FOREIGN TABLE ctl_foreign_table5;
+DROP FOREIGN TABLE ctl_foreign_table6;
+DROP FOREIGN DATA WRAPPER ctl_dummy CASCADE;
--
2.47.1
Re: Proposal to CREATE FOREIGN TABLE LIKE
On Tue, Feb 11, 2025 at 10:07:48AM -0600, Sami Imseih wrote:
I agree. I was just saying the test cases you provided are
enough. No changes needed for the tests.I have no further comments.
The checks you are adding in the parse analysis of the LIKE clauses is
surprisingly light.
+ * For foreign tables, they have no storage in Postgres.
+ * Inapplicable options are ignored.Wording is a bit strange here.
+ * CREATE_TABLE_LIKE_COMPRESSION
+ * CREATE_TABLE_LIKE_IDENTITY
+ * CREATE_TABLE_LIKE_STORAGE
+ * CREATE_TABLE_LIKE_INDEXES
[...]
+DROP FOREIGN TABLE ctl_foreign_table1;
+DROP FOREIGN TABLE ctl_foreign_table2;
+DROP FOREIGN TABLE ctl_foreign_table3;
+DROP FOREIGN TABLE ctl_foreign_table4;
+DROP FOREIGN TABLE ctl_foreign_table5;
+DROP FOREIGN TABLE ctl_foreign_table6;What's the point of creating that many tables, one for each of the
four INCLUDING options you are testing? It is possible to stack all
of them in a single CREATE TABLE command, still is that really
necessary if we have coverage with INCLUDING ALL and EXCLUDING ALL
(perhaps write it directly in the CREATE query rather than assume that
it is the default) as these are all-or-nothing switches for all the
option values. Of course let's be careful with HIDE_TOAST_COMPRESSION
if need be.
Perhaps the original table should have a primary key, also to track
the fact that the NOT NULL constraint is always copied but its related
index is not? Identity columns assign a NOT NULL constraint, as
documented these are always copied. Just wanted to point out that
this works the same way for your implementation with foreign tables,
so perhaps we should have a test for that.
Glad to see that you did not forget about statistics. I didn't recall
that these were OK for foreign tables, TBH.
expandTableLikeClause() depends on the "constr" built in the first
phase of transformTableLikeClause() to bypass the parts related to
generated, default and constraints properties. This dependency
between both routines should be documented, I guess. Hmm.. Not sure
where.
--
Michael
Re: Proposal to CREATE FOREIGN TABLE LIKE
On Feb 17, 2025 at 15:24 +0800, Michael Paquier <michael@paquier.xyz>, wrote:
+ * For foreign tables, they have no storage in Postgres. + * Inapplicable options are ignored.Wording is a bit strange here.
Hi, is this better?
* Foreign tables do not store data in Postgres.
* Any options that are not applicable for foreign tables will be ignored:
+ * CREATE_TABLE_LIKE_COMPRESSION + * CREATE_TABLE_LIKE_IDENTITY + * CREATE_TABLE_LIKE_STORAGE + * CREATE_TABLE_LIKE_INDEXES [...] +DROP FOREIGN TABLE ctl_foreign_table1; +DROP FOREIGN TABLE ctl_foreign_table2; +DROP FOREIGN TABLE ctl_foreign_table3; +DROP FOREIGN TABLE ctl_foreign_table4; +DROP FOREIGN TABLE ctl_foreign_table5; +DROP FOREIGN TABLE ctl_foreign_table6;What's the point of creating that many tables, one for each of the
four INCLUDING options you are testing? It is possible to stack all
of them in a single CREATE TABLE command, still is that really
necessary if we have coverage with INCLUDING ALL and EXCLUDING ALL
(perhaps write it directly in the CREATE query rather than assume that
it is the default) as these are all-or-nothing switches for all the
option values. Of course let's be careful with HIDE_TOAST_COMPRESSION
if need be.
I usually follow the cases within the same file; for each independent option, it becomes easier to identify which options are valid or invalid.
However, if you believe consolidating them into one is better, I’m fine with that. Updated.
Perhaps the original table should have a primary key, also to track
the fact that the NOT NULL constraint is always copied but its related
index is not? Identity columns assign a NOT NULL constraint, as
documented these are always copied. Just wanted to point out that
this works the same way for your implementation with foreign tables,
so perhaps we should have a test for that.
Done, although there is already one in column d.
Glad to see that you did not forget about statistics. I didn't recall
that these were OK for foreign tables, TBH.
I also didn't realize this until I wrote this patch. This could be useful for the planner？
expandTableLikeClause() depends on the "constr" built in the first
phase of transformTableLikeClause() to bypass the parts related to
generated, default and constraints properties. This dependency
between both routines should be documented, I guess. Hmm.. Not sure
where.
Comments are addressed except for this one, it might be worth considering another patch.
--
Zhang Mingli
HashData
Attachments:
v5-0001-CREATE-FOREIGN-TABLE-LIKE.patchapplication/octet-streamDownload
From ef05c075ff6390712d6c32c455d9fe50c0004b54 Mon Sep 17 00:00:00 2001
From: Zhang Mingli <avamingli@gmail.com>
Date: Tue, 11 Feb 2025 22:38:49 +0800
Subject: [PATCH] CREATE FOREIGN TABLE LIKE
Added support for CREATE FOREIGN TABLE LIKE to enable the creation of
foreign tables based on the column definitions, constraints of existing
source tables.
This feature mirrors the behavior of CREATE TABLE LIKE,
but ignores inapplicable options such as INCLUDING INDEXES,
INCLUDING COMPRESSION, INCLUDING IDENTITY, INCLUDING STORAGE
for foreign tables.
Authored-by: Zhang Mingli avamingli@gmail.com
---
doc/src/sgml/ref/create_foreign_table.sgml | 118 +++++++++++++++++-
src/backend/parser/parse_utilcmd.c | 28 +++--
.../regress/expected/create_table_like.out | 77 ++++++++++++
src/test/regress/sql/create_table_like.sql | 34 +++++
4 files changed, 245 insertions(+), 12 deletions(-)
diff --git a/doc/src/sgml/ref/create_foreign_table.sgml b/doc/src/sgml/ref/create_foreign_table.sgml
index 0dcd9ca6f8..5bc0a11009 100644
--- a/doc/src/sgml/ref/create_foreign_table.sgml
+++ b/doc/src/sgml/ref/create_foreign_table.sgml
@@ -23,7 +23,8 @@ PostgreSQL documentation
<synopsis>
CREATE FOREIGN TABLE [ IF NOT EXISTS ] <replaceable class="parameter">table_name</replaceable> ( [
{ <replaceable class="parameter">column_name</replaceable> <replaceable class="parameter">data_type</replaceable> [ OPTIONS ( <replaceable class="parameter">option</replaceable> '<replaceable class="parameter">value</replaceable>' [, ... ] ) ] [ COLLATE <replaceable>collation</replaceable> ] [ <replaceable class="parameter">column_constraint</replaceable> [ ... ] ]
- | <replaceable>table_constraint</replaceable> }
+ | <replaceable>table_constraint</replaceable>
+ | LIKE <replaceable>source_table</replaceable> [ <replaceable>like_option</replaceable> ... ] }
[, ... ]
] )
[ INHERITS ( <replaceable>parent_table</replaceable> [, ... ] ) ]
@@ -57,6 +58,10 @@ CREATE FOREIGN TABLE [ IF NOT EXISTS ] <replaceable class="parameter">table_name
CHECK ( <replaceable class="parameter">expression</replaceable> ) [ NO INHERIT ] }
[ ENFORCED | NOT ENFORCED ]
+<phrase>and <replaceable class="parameter">like_option</replaceable> is:</phrase>
+
+{ INCLUDING | EXCLUDING } { COMMENTS | CONSTRAINTS | DEFAULTS | GENERATED | STATISTICS | ALL }
+
<phrase>and <replaceable class="parameter">partition_bound_spec</replaceable> is:</phrase>
IN ( <replaceable class="parameter">partition_bound_expr</replaceable> [, ...] ) |
@@ -191,6 +196,117 @@ WITH ( MODULUS <replaceable class="parameter">numeric_literal</replaceable>, REM
</listitem>
</varlistentry>
+ <varlistentry>
+ <term><literal>LIKE <replaceable>source_table</replaceable> [ <replaceable>like_option</replaceable> ... ]</literal></term>
+ <listitem>
+ <para>
+ The <literal>LIKE</literal> clause specifies a table from which
+ the new table automatically copies all column names, their data types,
+ and their not-null constraints.
+ </para>
+ <para>
+ Unlike <literal>INHERITS</literal>, the new table and original table
+ are completely decoupled after creation is complete. Changes to the
+ original table will not be applied to the new table, and it is not
+ possible to include data of the new table in scans of the original
+ table.
+ </para>
+ <para>
+ Also unlike <literal>INHERITS</literal>, columns and
+ constraints copied by <literal>LIKE</literal> are not merged with similarly
+ named columns and constraints.
+ If the same name is specified explicitly or in another
+ <literal>LIKE</literal> clause, an error is signaled.
+ </para>
+ <para>
+ The optional <replaceable>like_option</replaceable> clauses specify
+ which additional properties of the original table to copy. Specifying
+ <literal>INCLUDING</literal> copies the property, specifying
+ <literal>EXCLUDING</literal> omits the property.
+ <literal>EXCLUDING</literal> is the default. If multiple specifications
+ are made for the same kind of object, the last one is used. The
+ available options are:
+
+ <variablelist>
+ <varlistentry>
+ <term><literal>INCLUDING COMMENTS</literal></term>
+ <listitem>
+ <para>
+ Comments for the copied columns, constraints, and indexes will be
+ copied. The default behavior is to exclude comments, resulting in
+ the copied columns and constraints in the new table having no
+ comments.
+ </para>
+ </listitem>
+ </varlistentry>
+
+ <varlistentry>
+ <term><literal>INCLUDING CONSTRAINTS</literal></term>
+ <listitem>
+ <para>
+ <literal>CHECK</literal> constraints will be copied. No distinction
+ is made between column constraints and table constraints. Not-null
+ constraints are always copied to the new table.
+ </para>
+ </listitem>
+ </varlistentry>
+
+ <varlistentry>
+ <term><literal>INCLUDING DEFAULTS</literal></term>
+ <listitem>
+ <para>
+ Default expressions for the copied column definitions will be
+ copied. Otherwise, default expressions are not copied, resulting in
+ the copied columns in the new table having null defaults. Note that
+ copying defaults that call database-modification functions, such as
+ <function>nextval</function>, may create a functional linkage
+ between the original and new tables.
+ </para>
+ </listitem>
+ </varlistentry>
+
+ <varlistentry>
+ <term><literal>INCLUDING GENERATED</literal></term>
+ <listitem>
+ <para>
+ Any generation expressions of copied column definitions will be
+ copied. By default, new columns will be regular base columns.
+ </para>
+ </listitem>
+ </varlistentry>
+
+ <varlistentry>
+ <term><literal>INCLUDING STATISTICS</literal></term>
+ <listitem>
+ <para>
+ Extended statistics are copied to the new table.
+ </para>
+ </listitem>
+ </varlistentry>
+
+ <varlistentry>
+ <term><literal>INCLUDING ALL</literal></term>
+ <listitem>
+ <para>
+ <literal>INCLUDING ALL</literal> is an abbreviated form selecting
+ all the available individual options. (It could be useful to write
+ individual <literal>EXCLUDING</literal> clauses after
+ <literal>INCLUDING ALL</literal> to select all but some specific
+ options.)
+ </para>
+ </listitem>
+ </varlistentry>
+ </variablelist>
+ </para>
+
+ <para>
+ Foreign tables have no storage in PostgreSQL.
+ Inapplicable options: <literal>INCLUDING INDEXES</literal>, <literal>INCLUDING STORAGE</literal>,
+ <literal>INCLUDING COMPRESSION</literal>, <literal>INCLUDING IDENTITY</literal> are ignored.
+ </para>
+ </listitem>
+ </varlistentry>
+
<varlistentry>
<term><literal>CONSTRAINT <replaceable class="parameter">constraint_name</replaceable></literal></term>
<listitem>
diff --git a/src/backend/parser/parse_utilcmd.c b/src/backend/parser/parse_utilcmd.c
index ca028d2a66..443bf2534f 100644
--- a/src/backend/parser/parse_utilcmd.c
+++ b/src/backend/parser/parse_utilcmd.c
@@ -1117,6 +1117,14 @@ transformTableConstraint(CreateStmtContext *cxt, Constraint *constraint)
* process at this point, add the TableLikeClause to cxt->likeclauses, which
* will cause utility.c to call expandTableLikeClause() after the new
* table has been created.
+ *
+ * Foreign tables do not store data in Postgres.
+ * Any options that are not applicable for foreign tables will be ignored:
+ * CREATE_TABLE_LIKE_COMPRESSION
+ * CREATE_TABLE_LIKE_IDENTITY
+ * CREATE_TABLE_LIKE_STORAGE
+ * CREATE_TABLE_LIKE_INDEXES
+ * while INDEXES is handled in expandTableLikeClause() later.
*/
static void
transformTableLikeClause(CreateStmtContext *cxt, TableLikeClause *table_like_clause)
@@ -1131,12 +1139,6 @@ transformTableLikeClause(CreateStmtContext *cxt, TableLikeClause *table_like_cla
setup_parser_errposition_callback(&pcbstate, cxt->pstate,
table_like_clause->relation->location);
- /* we could support LIKE in many cases, but worry about it another day */
- if (cxt->isforeign)
- ereport(ERROR,
- (errcode(ERRCODE_FEATURE_NOT_SUPPORTED),
- errmsg("LIKE is not supported for creating foreign tables")));
-
/* Open the relation referenced by the LIKE clause */
relation = relation_openrv(table_like_clause->relation, AccessShareLock);
@@ -1217,7 +1219,8 @@ transformTableLikeClause(CreateStmtContext *cxt, TableLikeClause *table_like_cla
* Copy identity if requested
*/
if (attribute->attidentity &&
- (table_like_clause->options & CREATE_TABLE_LIKE_IDENTITY))
+ (table_like_clause->options & CREATE_TABLE_LIKE_IDENTITY) &&
+ !cxt->isforeign)
{
Oid seq_relid;
List *seq_options;
@@ -1236,14 +1239,16 @@ transformTableLikeClause(CreateStmtContext *cxt, TableLikeClause *table_like_cla
}
/* Likewise, copy storage if requested */
- if (table_like_clause->options & CREATE_TABLE_LIKE_STORAGE)
+ if ((table_like_clause->options & CREATE_TABLE_LIKE_STORAGE) &&
+ !cxt->isforeign)
def->storage = attribute->attstorage;
else
def->storage = 0;
/* Likewise, copy compression if requested */
- if ((table_like_clause->options & CREATE_TABLE_LIKE_COMPRESSION) != 0
- && CompressionMethodIsValid(attribute->attcompression))
+ if ((table_like_clause->options & CREATE_TABLE_LIKE_COMPRESSION) != 0 &&
+ CompressionMethodIsValid(attribute->attcompression) &&
+ !cxt->isforeign)
def->compression =
pstrdup(GetCompressionMethodName(attribute->attcompression));
else
@@ -1522,7 +1527,8 @@ expandTableLikeClause(RangeVar *heapRel, TableLikeClause *table_like_clause)
* Process indexes if required.
*/
if ((table_like_clause->options & CREATE_TABLE_LIKE_INDEXES) &&
- relation->rd_rel->relhasindex)
+ relation->rd_rel->relhasindex &&
+ childrel->rd_rel->relkind != RELKIND_FOREIGN_TABLE)
{
List *parent_indexes;
ListCell *l;
diff --git a/src/test/regress/expected/create_table_like.out b/src/test/regress/expected/create_table_like.out
index e061389135..c38118a8ad 100644
--- a/src/test/regress/expected/create_table_like.out
+++ b/src/test/regress/expected/create_table_like.out
@@ -563,3 +563,80 @@ DROP TYPE ctlty1;
DROP VIEW ctlv1;
DROP TABLE IF EXISTS ctlt4, ctlt10, ctlt11, ctlt11a, ctlt12;
NOTICE: table "ctlt10" does not exist, skipping
+--
+-- CREATE FOREIGN TABLE LIKE
+--
+CREATE FOREIGN DATA WRAPPER ctl_dummy;
+CREATE SERVER ctl_s0 FOREIGN DATA WRAPPER ctl_dummy;
+CREATE TABLE ctl_table(a int primary key, b varchar COMPRESSION pglz,
+ c int GENERATED ALWAYS AS (a * 2) STORED,
+ d bigint GENERATED ALWAYS AS IDENTITY,
+ e int default 1);
+CREATE INDEX ctl_table_a_key ON ctl_table(a);
+COMMENT ON COLUMN ctl_table.b IS 'Column b';
+CREATE STATISTICS ctl_table_stat ON a,b FROM ctl_table;
+ALTER TABLE ctl_table add constraint foo CHECK (b = 'text');
+\d+ ctl_table
+ Table "public.ctl_table"
+ Column | Type | Collation | Nullable | Default | Storage | Stats target | Description
+--------+-------------------+-----------+----------+------------------------------------+----------+--------------+-------------
+ a | integer | | not null | | plain | |
+ b | character varying | | | | extended | | Column b
+ c | integer | | | generated always as (a * 2) stored | plain | |
+ d | bigint | | not null | generated always as identity | plain | |
+ e | integer | | | 1 | plain | |
+Indexes:
+ "ctl_table_pkey" PRIMARY KEY, btree (a)
+ "ctl_table_a_key" btree (a)
+Check constraints:
+ "foo" CHECK (b::text = 'text'::text)
+Statistics objects:
+ "public.ctl_table_stat" ON a, b FROM ctl_table
+Not-null constraints:
+ "ctl_table_a_not_null" NOT NULL "a"
+ "ctl_table_d_not_null" NOT NULL "d"
+
+-- Test EXCLUDING ALL
+CREATE FOREIGN TABLE ctl_foreign_table1(LIKE ctl_table EXCLUDING ALL) SERVER ctl_s0;
+\d+ ctl_foreign_table1
+ Foreign table "public.ctl_foreign_table1"
+ Column | Type | Collation | Nullable | Default | FDW options | Storage | Stats target | Description
+--------+-------------------+-----------+----------+---------+-------------+----------+--------------+-------------
+ a | integer | | not null | | | plain | |
+ b | character varying | | | | | extended | |
+ c | integer | | | | | plain | |
+ d | bigint | | not null | | | plain | |
+ e | integer | | | | | plain | |
+Not-null constraints:
+ "ctl_table_a_not_null" NOT NULL "a"
+ "ctl_table_d_not_null" NOT NULL "d"
+Server: ctl_s0
+
+\set HIDE_TOAST_COMPRESSION false
+-- Test INCLUDING ALL
+-- INDEXES, IDENTITY, COMPRESSION, STORAGE are not copied.
+CREATE FOREIGN TABLE ctl_foreign_table2(LIKE ctl_table INCLUDING ALL) SERVER ctl_s0;
+\d+ ctl_foreign_table2
+ Foreign table "public.ctl_foreign_table2"
+ Column | Type | Collation | Nullable | Default | FDW options | Storage | Stats target | Description
+--------+-------------------+-----------+----------+------------------------------------+-------------+----------+--------------+-------------
+ a | integer | | not null | | | plain | |
+ b | character varying | | | | | extended | | Column b
+ c | integer | | | generated always as (a * 2) stored | | plain | |
+ d | bigint | | not null | | | plain | |
+ e | integer | | | 1 | | plain | |
+Check constraints:
+ "foo" CHECK (b::text = 'text'::text)
+Statistics objects:
+ "public.ctl_foreign_table2_a_b_stat" ON a, b FROM ctl_foreign_table2
+Not-null constraints:
+ "ctl_table_a_not_null" NOT NULL "a"
+ "ctl_table_d_not_null" NOT NULL "d"
+Server: ctl_s0
+
+\set HIDE_TOAST_COMPRESSION true
+DROP TABLE ctl_table;
+DROP FOREIGN TABLE ctl_foreign_table1;
+DROP FOREIGN TABLE ctl_foreign_table2;
+DROP FOREIGN DATA WRAPPER ctl_dummy CASCADE;
+NOTICE: drop cascades to server ctl_s0
diff --git a/src/test/regress/sql/create_table_like.sql b/src/test/regress/sql/create_table_like.sql
index a41f8b83d7..98ca8ac060 100644
--- a/src/test/regress/sql/create_table_like.sql
+++ b/src/test/regress/sql/create_table_like.sql
@@ -225,3 +225,37 @@ DROP SEQUENCE ctlseq1;
DROP TYPE ctlty1;
DROP VIEW ctlv1;
DROP TABLE IF EXISTS ctlt4, ctlt10, ctlt11, ctlt11a, ctlt12;
+
+--
+-- CREATE FOREIGN TABLE LIKE
+--
+CREATE FOREIGN DATA WRAPPER ctl_dummy;
+CREATE SERVER ctl_s0 FOREIGN DATA WRAPPER ctl_dummy;
+
+CREATE TABLE ctl_table(a int primary key, b varchar COMPRESSION pglz,
+ c int GENERATED ALWAYS AS (a * 2) STORED,
+ d bigint GENERATED ALWAYS AS IDENTITY,
+ e int default 1);
+
+CREATE INDEX ctl_table_a_key ON ctl_table(a);
+COMMENT ON COLUMN ctl_table.b IS 'Column b';
+CREATE STATISTICS ctl_table_stat ON a,b FROM ctl_table;
+ALTER TABLE ctl_table add constraint foo CHECK (b = 'text');
+
+\d+ ctl_table
+
+-- Test EXCLUDING ALL
+CREATE FOREIGN TABLE ctl_foreign_table1(LIKE ctl_table EXCLUDING ALL) SERVER ctl_s0;
+\d+ ctl_foreign_table1
+
+\set HIDE_TOAST_COMPRESSION false
+-- Test INCLUDING ALL
+-- INDEXES, IDENTITY, COMPRESSION, STORAGE are not copied.
+CREATE FOREIGN TABLE ctl_foreign_table2(LIKE ctl_table INCLUDING ALL) SERVER ctl_s0;
+\d+ ctl_foreign_table2
+\set HIDE_TOAST_COMPRESSION true
+
+DROP TABLE ctl_table;
+DROP FOREIGN TABLE ctl_foreign_table1;
+DROP FOREIGN TABLE ctl_foreign_table2;
+DROP FOREIGN DATA WRAPPER ctl_dummy CASCADE;
--
2.34.1
Re: Proposal to CREATE FOREIGN TABLE LIKE
On Mon, Feb 17, 2025 at 07:14:59PM +0800, Zhang Mingli wrote:
On Feb 17, 2025 at 15:24 +0800, Michael Paquier <michael@paquier.xyz>, wrote:
+ * For foreign tables, they have no storage in Postgres. + * Inapplicable options are ignored.Wording is a bit strange here.
* Foreign tables do not store data in Postgres.
* Any options that are not applicable for foreign tables will be ignored:
I would do something like that, perhaps, though I could get that
people don't like this suggestion:
"Some options are ignored. For example, as foreign tables have no
storage, these options have no effect: storage, compression, identity
and indexes. Similarly, INCLUDING INDEXES is ignored from a view."
I usually follow the cases within the same file; for each
independent option, it becomes easier to identify which options are
valid or invalid. However, if you believe consolidating them into
one is better, I’m fine with that. Updated.
It does not matter much in this case, IMO, what matters is to make
sure that the four additional checks you are adding in the
post-parsing paths are correctly ignored. Passing down an INCLUDING
ALL does that just fine as you double-check the state of the table
with a \d+ meta-command.
I also didn't realize this until I wrote this patch. This could be
useful for the planner？
Constraints can be used as hints in the planner when working on
foreign tables. I'm pretty sure that this is the same reason here,
seeing that this is supported since v10 where statistics have been
introduced. I would need to dig more into the code, but that's not
really the point for this thread..
expandTableLikeClause() depends on the "constr" built in the first
phase of transformTableLikeClause() to bypass the parts related to
generated, default and constraints properties. This dependency
between both routines should be documented, I guess. Hmm.. Not sure
where.Comments are addressed except for this one, it might be worth
considering another patch.
Yeah, not entirely sure. It does not prevent your patch to work, and
it is already documented that the "expand" part is done as-is because
it depends on the "transform". So perhaps it's just OK like this. I
withdraw my comment.
+ Inapplicable options: <literal>INCLUDING INDEXES</literal>, <literal>INCLUDING STORAGE</literal>,
+ <literal>INCLUDING COMPRESSION</literal>, <literal>INCLUDING IDENTITY</literal> are ignored.I would remove this paragraph, actually. The options supported are
listed by your patch, and that would be one area less to update if a
new INCLUDING flavor is added.
Copy-pasting the details of how the LIKE options work to the
create_foreign_table.sgml page is OK for me, and perhaps this will
diverge a bit from the CREATE TABLE part. One thing is that LIKE is
not part of the SQL specification for CREATE FOREIGN TABLE. Perhaps
this should be mentioned at the bottom of the page under the
"compatibility" section?
--
Michael
Re: Proposal to CREATE FOREIGN TABLE LIKE
On Feb 18, 2025 at 08:49 +0800, Michael Paquier <michael@paquier.xyz>, wrote:
On Mon, Feb 17, 2025 at 07:14:59PM +0800, Zhang Mingli wrote:
On Feb 17, 2025 at 15:24 +0800, Michael Paquier <michael@paquier.xyz>, wrote:
+ * For foreign tables, they have no storage in Postgres. + * Inapplicable options are ignored.Wording is a bit strange here.
* Foreign tables do not store data in Postgres.
* Any options that are not applicable for foreign tables will be ignored:I would do something like that, perhaps, though I could get that
people don't like this suggestion:
"Some options are ignored. For example, as foreign tables have no
storage, these options have no effect: storage, compression, identity
and indexes. Similarly, INCLUDING INDEXES is ignored from a view."
OK.
I also didn't realize this until I wrote this patch. This could be
useful for the planner？Constraints can be used as hints in the planner when working on
foreign tables. I'm pretty sure that this is the same reason here,
seeing that this is supported since v10 where statistics have been
introduced. I would need to dig more into the code, but that's not
really the point for this thread..
Agree.
+ Inapplicable options: <literal>INCLUDING INDEXES</literal>, <literal>INCLUDING STORAGE</literal>, + <literal>INCLUDING COMPRESSION</literal>, <literal>INCLUDING IDENTITY</literal> are ignored.I would remove this paragraph, actually. The options supported are
listed by your patch, and that would be one area less to update if a
new INCLUDING flavor is added.
OK.
Copy-pasting the details of how the LIKE options work to the
create_foreign_table.sgml page is OK for me, and perhaps this will
diverge a bit from the CREATE TABLE part. One thing is that LIKE is
not part of the SQL specification for CREATE FOREIGN TABLE. Perhaps
this should be mentioned at the bottom of the page under the
"compatibility" section?
Good point.
Will address the comments later, thanks for review!
--
Zhang Mingli
HashData
Re: Proposal to CREATE FOREIGN TABLE LIKE
On Feb 18, 2025 at 09:54 +0800, Zhang Mingli <zmlpostgres@gmail.com>, wrote:
Will address the comments later, thanks for review!
Done in patch v6.
--
Zhang Mingli
HashData
Attachments:
v6-0001-CREATE-FOREIGN-TABLE-LIKE.patchapplication/octet-streamDownload
From 4832047f1af898e5c952708c8eac4a04baaffc35 Mon Sep 17 00:00:00 2001
From: Zhang Mingli <avamingli@gmail.com>
Date: Tue, 11 Feb 2025 22:38:49 +0800
Subject: [PATCH] CREATE FOREIGN TABLE LIKE
Added support for CREATE FOREIGN TABLE LIKE to enable the creation of
foreign tables based on the column definitions, constraints of existing
source tables.
This feature mirrors the behavior of CREATE TABLE LIKE,
but ignores inapplicable options such as INCLUDING INDEXES,
INCLUDING COMPRESSION, INCLUDING IDENTITY, INCLUDING STORAGE
for foreign tables.
Authored-by: Zhang Mingli avamingli@gmail.com
---
doc/src/sgml/ref/create_foreign_table.sgml | 121 +++++++++++++++++-
src/backend/parser/parse_utilcmd.c | 24 ++--
.../regress/expected/create_table_like.out | 77 +++++++++++
src/test/regress/sql/create_table_like.sql | 34 +++++
4 files changed, 244 insertions(+), 12 deletions(-)
diff --git a/doc/src/sgml/ref/create_foreign_table.sgml b/doc/src/sgml/ref/create_foreign_table.sgml
index 0dcd9ca6f8..09450e343a 100644
--- a/doc/src/sgml/ref/create_foreign_table.sgml
+++ b/doc/src/sgml/ref/create_foreign_table.sgml
@@ -23,7 +23,8 @@ PostgreSQL documentation
<synopsis>
CREATE FOREIGN TABLE [ IF NOT EXISTS ] <replaceable class="parameter">table_name</replaceable> ( [
{ <replaceable class="parameter">column_name</replaceable> <replaceable class="parameter">data_type</replaceable> [ OPTIONS ( <replaceable class="parameter">option</replaceable> '<replaceable class="parameter">value</replaceable>' [, ... ] ) ] [ COLLATE <replaceable>collation</replaceable> ] [ <replaceable class="parameter">column_constraint</replaceable> [ ... ] ]
- | <replaceable>table_constraint</replaceable> }
+ | <replaceable>table_constraint</replaceable>
+ | LIKE <replaceable>source_table</replaceable> [ <replaceable>like_option</replaceable> ... ] }
[, ... ]
] )
[ INHERITS ( <replaceable>parent_table</replaceable> [, ... ] ) ]
@@ -57,6 +58,10 @@ CREATE FOREIGN TABLE [ IF NOT EXISTS ] <replaceable class="parameter">table_name
CHECK ( <replaceable class="parameter">expression</replaceable> ) [ NO INHERIT ] }
[ ENFORCED | NOT ENFORCED ]
+<phrase>and <replaceable class="parameter">like_option</replaceable> is:</phrase>
+
+{ INCLUDING | EXCLUDING } { COMMENTS | CONSTRAINTS | DEFAULTS | GENERATED | STATISTICS | ALL }
+
<phrase>and <replaceable class="parameter">partition_bound_spec</replaceable> is:</phrase>
IN ( <replaceable class="parameter">partition_bound_expr</replaceable> [, ...] ) |
@@ -191,6 +196,111 @@ WITH ( MODULUS <replaceable class="parameter">numeric_literal</replaceable>, REM
</listitem>
</varlistentry>
+ <varlistentry>
+ <term><literal>LIKE <replaceable>source_table</replaceable> [ <replaceable>like_option</replaceable> ... ]</literal></term>
+ <listitem>
+ <para>
+ The <literal>LIKE</literal> clause specifies a table from which
+ the new table automatically copies all column names, their data types,
+ and their not-null constraints.
+ </para>
+ <para>
+ Unlike <literal>INHERITS</literal>, the new table and original table
+ are completely decoupled after creation is complete. Changes to the
+ original table will not be applied to the new table, and it is not
+ possible to include data of the new table in scans of the original
+ table.
+ </para>
+ <para>
+ Also unlike <literal>INHERITS</literal>, columns and
+ constraints copied by <literal>LIKE</literal> are not merged with similarly
+ named columns and constraints.
+ If the same name is specified explicitly or in another
+ <literal>LIKE</literal> clause, an error is signaled.
+ </para>
+ <para>
+ The optional <replaceable>like_option</replaceable> clauses specify
+ which additional properties of the original table to copy. Specifying
+ <literal>INCLUDING</literal> copies the property, specifying
+ <literal>EXCLUDING</literal> omits the property.
+ <literal>EXCLUDING</literal> is the default. If multiple specifications
+ are made for the same kind of object, the last one is used. The
+ available options are:
+
+ <variablelist>
+ <varlistentry>
+ <term><literal>INCLUDING COMMENTS</literal></term>
+ <listitem>
+ <para>
+ Comments for the copied columns, constraints, and indexes will be
+ copied. The default behavior is to exclude comments, resulting in
+ the copied columns and constraints in the new table having no
+ comments.
+ </para>
+ </listitem>
+ </varlistentry>
+
+ <varlistentry>
+ <term><literal>INCLUDING CONSTRAINTS</literal></term>
+ <listitem>
+ <para>
+ <literal>CHECK</literal> constraints will be copied. No distinction
+ is made between column constraints and table constraints. Not-null
+ constraints are always copied to the new table.
+ </para>
+ </listitem>
+ </varlistentry>
+
+ <varlistentry>
+ <term><literal>INCLUDING DEFAULTS</literal></term>
+ <listitem>
+ <para>
+ Default expressions for the copied column definitions will be
+ copied. Otherwise, default expressions are not copied, resulting in
+ the copied columns in the new table having null defaults. Note that
+ copying defaults that call database-modification functions, such as
+ <function>nextval</function>, may create a functional linkage
+ between the original and new tables.
+ </para>
+ </listitem>
+ </varlistentry>
+
+ <varlistentry>
+ <term><literal>INCLUDING GENERATED</literal></term>
+ <listitem>
+ <para>
+ Any generation expressions of copied column definitions will be
+ copied. By default, new columns will be regular base columns.
+ </para>
+ </listitem>
+ </varlistentry>
+
+ <varlistentry>
+ <term><literal>INCLUDING STATISTICS</literal></term>
+ <listitem>
+ <para>
+ Extended statistics are copied to the new table.
+ </para>
+ </listitem>
+ </varlistentry>
+
+ <varlistentry>
+ <term><literal>INCLUDING ALL</literal></term>
+ <listitem>
+ <para>
+ <literal>INCLUDING ALL</literal> is an abbreviated form selecting
+ all the available individual options. (It could be useful to write
+ individual <literal>EXCLUDING</literal> clauses after
+ <literal>INCLUDING ALL</literal> to select all but some specific
+ options.)
+ </para>
+ </listitem>
+ </varlistentry>
+ </variablelist>
+ </para>
+ </listitem>
+ </varlistentry>
+
<varlistentry>
<term><literal>CONSTRAINT <replaceable class="parameter">constraint_name</replaceable></literal></term>
<listitem>
@@ -447,6 +557,15 @@ CREATE FOREIGN TABLE measurement_y2016m07
defined by <productname>PostgreSQL</productname>, is nonstandard.
</para>
+ <refsect2>
+ <title><literal>LIKE</literal> Clause</title>
+
+ <para>
+ The <literal>LIKE</literal> clause is a
+ <productname>PostgreSQL</productname> extension.
+ </para>
+ </refsect2>
+
</refsect1>
<refsect1>
diff --git a/src/backend/parser/parse_utilcmd.c b/src/backend/parser/parse_utilcmd.c
index ca028d2a66..6d966229e5 100644
--- a/src/backend/parser/parse_utilcmd.c
+++ b/src/backend/parser/parse_utilcmd.c
@@ -1117,6 +1117,10 @@ transformTableConstraint(CreateStmtContext *cxt, Constraint *constraint)
* process at this point, add the TableLikeClause to cxt->likeclauses, which
* will cause utility.c to call expandTableLikeClause() after the new
* table has been created.
+ *
+ * Some options are ignored. For example, as foreign tables have no
+ * storage, these options have no effect: storage, compression, identity
+ * and indexes. Similarly, INCLUDING INDEXES is ignored from a view.
*/
static void
transformTableLikeClause(CreateStmtContext *cxt, TableLikeClause *table_like_clause)
@@ -1131,12 +1135,6 @@ transformTableLikeClause(CreateStmtContext *cxt, TableLikeClause *table_like_cla
setup_parser_errposition_callback(&pcbstate, cxt->pstate,
table_like_clause->relation->location);
- /* we could support LIKE in many cases, but worry about it another day */
- if (cxt->isforeign)
- ereport(ERROR,
- (errcode(ERRCODE_FEATURE_NOT_SUPPORTED),
- errmsg("LIKE is not supported for creating foreign tables")));
-
/* Open the relation referenced by the LIKE clause */
relation = relation_openrv(table_like_clause->relation, AccessShareLock);
@@ -1217,7 +1215,8 @@ transformTableLikeClause(CreateStmtContext *cxt, TableLikeClause *table_like_cla
* Copy identity if requested
*/
if (attribute->attidentity &&
- (table_like_clause->options & CREATE_TABLE_LIKE_IDENTITY))
+ (table_like_clause->options & CREATE_TABLE_LIKE_IDENTITY) &&
+ !cxt->isforeign)
{
Oid seq_relid;
List *seq_options;
@@ -1236,14 +1235,16 @@ transformTableLikeClause(CreateStmtContext *cxt, TableLikeClause *table_like_cla
}
/* Likewise, copy storage if requested */
- if (table_like_clause->options & CREATE_TABLE_LIKE_STORAGE)
+ if ((table_like_clause->options & CREATE_TABLE_LIKE_STORAGE) &&
+ !cxt->isforeign)
def->storage = attribute->attstorage;
else
def->storage = 0;
/* Likewise, copy compression if requested */
- if ((table_like_clause->options & CREATE_TABLE_LIKE_COMPRESSION) != 0
- && CompressionMethodIsValid(attribute->attcompression))
+ if ((table_like_clause->options & CREATE_TABLE_LIKE_COMPRESSION) != 0 &&
+ CompressionMethodIsValid(attribute->attcompression) &&
+ !cxt->isforeign)
def->compression =
pstrdup(GetCompressionMethodName(attribute->attcompression));
else
@@ -1522,7 +1523,8 @@ expandTableLikeClause(RangeVar *heapRel, TableLikeClause *table_like_clause)
* Process indexes if required.
*/
if ((table_like_clause->options & CREATE_TABLE_LIKE_INDEXES) &&
- relation->rd_rel->relhasindex)
+ relation->rd_rel->relhasindex &&
+ childrel->rd_rel->relkind != RELKIND_FOREIGN_TABLE)
{
List *parent_indexes;
ListCell *l;
diff --git a/src/test/regress/expected/create_table_like.out b/src/test/regress/expected/create_table_like.out
index e061389135..c38118a8ad 100644
--- a/src/test/regress/expected/create_table_like.out
+++ b/src/test/regress/expected/create_table_like.out
@@ -563,3 +563,80 @@ DROP TYPE ctlty1;
DROP VIEW ctlv1;
DROP TABLE IF EXISTS ctlt4, ctlt10, ctlt11, ctlt11a, ctlt12;
NOTICE: table "ctlt10" does not exist, skipping
+--
+-- CREATE FOREIGN TABLE LIKE
+--
+CREATE FOREIGN DATA WRAPPER ctl_dummy;
+CREATE SERVER ctl_s0 FOREIGN DATA WRAPPER ctl_dummy;
+CREATE TABLE ctl_table(a int primary key, b varchar COMPRESSION pglz,
+ c int GENERATED ALWAYS AS (a * 2) STORED,
+ d bigint GENERATED ALWAYS AS IDENTITY,
+ e int default 1);
+CREATE INDEX ctl_table_a_key ON ctl_table(a);
+COMMENT ON COLUMN ctl_table.b IS 'Column b';
+CREATE STATISTICS ctl_table_stat ON a,b FROM ctl_table;
+ALTER TABLE ctl_table add constraint foo CHECK (b = 'text');
+\d+ ctl_table
+ Table "public.ctl_table"
+ Column | Type | Collation | Nullable | Default | Storage | Stats target | Description
+--------+-------------------+-----------+----------+------------------------------------+----------+--------------+-------------
+ a | integer | | not null | | plain | |
+ b | character varying | | | | extended | | Column b
+ c | integer | | | generated always as (a * 2) stored | plain | |
+ d | bigint | | not null | generated always as identity | plain | |
+ e | integer | | | 1 | plain | |
+Indexes:
+ "ctl_table_pkey" PRIMARY KEY, btree (a)
+ "ctl_table_a_key" btree (a)
+Check constraints:
+ "foo" CHECK (b::text = 'text'::text)
+Statistics objects:
+ "public.ctl_table_stat" ON a, b FROM ctl_table
+Not-null constraints:
+ "ctl_table_a_not_null" NOT NULL "a"
+ "ctl_table_d_not_null" NOT NULL "d"
+
+-- Test EXCLUDING ALL
+CREATE FOREIGN TABLE ctl_foreign_table1(LIKE ctl_table EXCLUDING ALL) SERVER ctl_s0;
+\d+ ctl_foreign_table1
+ Foreign table "public.ctl_foreign_table1"
+ Column | Type | Collation | Nullable | Default | FDW options | Storage | Stats target | Description
+--------+-------------------+-----------+----------+---------+-------------+----------+--------------+-------------
+ a | integer | | not null | | | plain | |
+ b | character varying | | | | | extended | |
+ c | integer | | | | | plain | |
+ d | bigint | | not null | | | plain | |
+ e | integer | | | | | plain | |
+Not-null constraints:
+ "ctl_table_a_not_null" NOT NULL "a"
+ "ctl_table_d_not_null" NOT NULL "d"
+Server: ctl_s0
+
+\set HIDE_TOAST_COMPRESSION false
+-- Test INCLUDING ALL
+-- INDEXES, IDENTITY, COMPRESSION, STORAGE are not copied.
+CREATE FOREIGN TABLE ctl_foreign_table2(LIKE ctl_table INCLUDING ALL) SERVER ctl_s0;
+\d+ ctl_foreign_table2
+ Foreign table "public.ctl_foreign_table2"
+ Column | Type | Collation | Nullable | Default | FDW options | Storage | Stats target | Description
+--------+-------------------+-----------+----------+------------------------------------+-------------+----------+--------------+-------------
+ a | integer | | not null | | | plain | |
+ b | character varying | | | | | extended | | Column b
+ c | integer | | | generated always as (a * 2) stored | | plain | |
+ d | bigint | | not null | | | plain | |
+ e | integer | | | 1 | | plain | |
+Check constraints:
+ "foo" CHECK (b::text = 'text'::text)
+Statistics objects:
+ "public.ctl_foreign_table2_a_b_stat" ON a, b FROM ctl_foreign_table2
+Not-null constraints:
+ "ctl_table_a_not_null" NOT NULL "a"
+ "ctl_table_d_not_null" NOT NULL "d"
+Server: ctl_s0
+
+\set HIDE_TOAST_COMPRESSION true
+DROP TABLE ctl_table;
+DROP FOREIGN TABLE ctl_foreign_table1;
+DROP FOREIGN TABLE ctl_foreign_table2;
+DROP FOREIGN DATA WRAPPER ctl_dummy CASCADE;
+NOTICE: drop cascades to server ctl_s0
diff --git a/src/test/regress/sql/create_table_like.sql b/src/test/regress/sql/create_table_like.sql
index a41f8b83d7..98ca8ac060 100644
--- a/src/test/regress/sql/create_table_like.sql
+++ b/src/test/regress/sql/create_table_like.sql
@@ -225,3 +225,37 @@ DROP SEQUENCE ctlseq1;
DROP TYPE ctlty1;
DROP VIEW ctlv1;
DROP TABLE IF EXISTS ctlt4, ctlt10, ctlt11, ctlt11a, ctlt12;
+
+--
+-- CREATE FOREIGN TABLE LIKE
+--
+CREATE FOREIGN DATA WRAPPER ctl_dummy;
+CREATE SERVER ctl_s0 FOREIGN DATA WRAPPER ctl_dummy;
+
+CREATE TABLE ctl_table(a int primary key, b varchar COMPRESSION pglz,
+ c int GENERATED ALWAYS AS (a * 2) STORED,
+ d bigint GENERATED ALWAYS AS IDENTITY,
+ e int default 1);
+
+CREATE INDEX ctl_table_a_key ON ctl_table(a);
+COMMENT ON COLUMN ctl_table.b IS 'Column b';
+CREATE STATISTICS ctl_table_stat ON a,b FROM ctl_table;
+ALTER TABLE ctl_table add constraint foo CHECK (b = 'text');
+
+\d+ ctl_table
+
+-- Test EXCLUDING ALL
+CREATE FOREIGN TABLE ctl_foreign_table1(LIKE ctl_table EXCLUDING ALL) SERVER ctl_s0;
+\d+ ctl_foreign_table1
+
+\set HIDE_TOAST_COMPRESSION false
+-- Test INCLUDING ALL
+-- INDEXES, IDENTITY, COMPRESSION, STORAGE are not copied.
+CREATE FOREIGN TABLE ctl_foreign_table2(LIKE ctl_table INCLUDING ALL) SERVER ctl_s0;
+\d+ ctl_foreign_table2
+\set HIDE_TOAST_COMPRESSION true
+
+DROP TABLE ctl_table;
+DROP FOREIGN TABLE ctl_foreign_table1;
+DROP FOREIGN TABLE ctl_foreign_table2;
+DROP FOREIGN DATA WRAPPER ctl_dummy CASCADE;
--
2.34.1
Re: Proposal to CREATE FOREIGN TABLE LIKE
On Feb 18, 2025 at 09:54 +0800, Zhang Mingli <zmlpostgres@gmail.com>, wrote:
Will address the comments later, thanks for review!
Done in patch v6.
--
Zhang Mingli
HashData
Attachments:
v6-0001-CREATE-FOREIGN-TABLE-LIKE.patchapplication/octet-streamDownload
From 4832047f1af898e5c952708c8eac4a04baaffc35 Mon Sep 17 00:00:00 2001
From: Zhang Mingli <avamingli@gmail.com>
Date: Tue, 11 Feb 2025 22:38:49 +0800
Subject: [PATCH] CREATE FOREIGN TABLE LIKE
Added support for CREATE FOREIGN TABLE LIKE to enable the creation of
foreign tables based on the column definitions, constraints of existing
source tables.
This feature mirrors the behavior of CREATE TABLE LIKE,
but ignores inapplicable options such as INCLUDING INDEXES,
INCLUDING COMPRESSION, INCLUDING IDENTITY, INCLUDING STORAGE
for foreign tables.
Authored-by: Zhang Mingli avamingli@gmail.com
---
doc/src/sgml/ref/create_foreign_table.sgml | 121 +++++++++++++++++-
src/backend/parser/parse_utilcmd.c | 24 ++--
.../regress/expected/create_table_like.out | 77 +++++++++++
src/test/regress/sql/create_table_like.sql | 34 +++++
4 files changed, 244 insertions(+), 12 deletions(-)
diff --git a/doc/src/sgml/ref/create_foreign_table.sgml b/doc/src/sgml/ref/create_foreign_table.sgml
index 0dcd9ca6f8..09450e343a 100644
--- a/doc/src/sgml/ref/create_foreign_table.sgml
+++ b/doc/src/sgml/ref/create_foreign_table.sgml
@@ -23,7 +23,8 @@ PostgreSQL documentation
<synopsis>
CREATE FOREIGN TABLE [ IF NOT EXISTS ] <replaceable class="parameter">table_name</replaceable> ( [
{ <replaceable class="parameter">column_name</replaceable> <replaceable class="parameter">data_type</replaceable> [ OPTIONS ( <replaceable class="parameter">option</replaceable> '<replaceable class="parameter">value</replaceable>' [, ... ] ) ] [ COLLATE <replaceable>collation</replaceable> ] [ <replaceable class="parameter">column_constraint</replaceable> [ ... ] ]
- | <replaceable>table_constraint</replaceable> }
+ | <replaceable>table_constraint</replaceable>
+ | LIKE <replaceable>source_table</replaceable> [ <replaceable>like_option</replaceable> ... ] }
[, ... ]
] )
[ INHERITS ( <replaceable>parent_table</replaceable> [, ... ] ) ]
@@ -57,6 +58,10 @@ CREATE FOREIGN TABLE [ IF NOT EXISTS ] <replaceable class="parameter">table_name
CHECK ( <replaceable class="parameter">expression</replaceable> ) [ NO INHERIT ] }
[ ENFORCED | NOT ENFORCED ]
+<phrase>and <replaceable class="parameter">like_option</replaceable> is:</phrase>
+
+{ INCLUDING | EXCLUDING } { COMMENTS | CONSTRAINTS | DEFAULTS | GENERATED | STATISTICS | ALL }
+
<phrase>and <replaceable class="parameter">partition_bound_spec</replaceable> is:</phrase>
IN ( <replaceable class="parameter">partition_bound_expr</replaceable> [, ...] ) |
@@ -191,6 +196,111 @@ WITH ( MODULUS <replaceable class="parameter">numeric_literal</replaceable>, REM
</listitem>
</varlistentry>
+ <varlistentry>
+ <term><literal>LIKE <replaceable>source_table</replaceable> [ <replaceable>like_option</replaceable> ... ]</literal></term>
+ <listitem>
+ <para>
+ The <literal>LIKE</literal> clause specifies a table from which
+ the new table automatically copies all column names, their data types,
+ and their not-null constraints.
+ </para>
+ <para>
+ Unlike <literal>INHERITS</literal>, the new table and original table
+ are completely decoupled after creation is complete. Changes to the
+ original table will not be applied to the new table, and it is not
+ possible to include data of the new table in scans of the original
+ table.
+ </para>
+ <para>
+ Also unlike <literal>INHERITS</literal>, columns and
+ constraints copied by <literal>LIKE</literal> are not merged with similarly
+ named columns and constraints.
+ If the same name is specified explicitly or in another
+ <literal>LIKE</literal> clause, an error is signaled.
+ </para>
+ <para>
+ The optional <replaceable>like_option</replaceable> clauses specify
+ which additional properties of the original table to copy. Specifying
+ <literal>INCLUDING</literal> copies the property, specifying
+ <literal>EXCLUDING</literal> omits the property.
+ <literal>EXCLUDING</literal> is the default. If multiple specifications
+ are made for the same kind of object, the last one is used. The
+ available options are:
+
+ <variablelist>
+ <varlistentry>
+ <term><literal>INCLUDING COMMENTS</literal></term>
+ <listitem>
+ <para>
+ Comments for the copied columns, constraints, and indexes will be
+ copied. The default behavior is to exclude comments, resulting in
+ the copied columns and constraints in the new table having no
+ comments.
+ </para>
+ </listitem>
+ </varlistentry>
+
+ <varlistentry>
+ <term><literal>INCLUDING CONSTRAINTS</literal></term>
+ <listitem>
+ <para>
+ <literal>CHECK</literal> constraints will be copied. No distinction
+ is made between column constraints and table constraints. Not-null
+ constraints are always copied to the new table.
+ </para>
+ </listitem>
+ </varlistentry>
+
+ <varlistentry>
+ <term><literal>INCLUDING DEFAULTS</literal></term>
+ <listitem>
+ <para>
+ Default expressions for the copied column definitions will be
+ copied. Otherwise, default expressions are not copied, resulting in
+ the copied columns in the new table having null defaults. Note that
+ copying defaults that call database-modification functions, such as
+ <function>nextval</function>, may create a functional linkage
+ between the original and new tables.
+ </para>
+ </listitem>
+ </varlistentry>
+
+ <varlistentry>
+ <term><literal>INCLUDING GENERATED</literal></term>
+ <listitem>
+ <para>
+ Any generation expressions of copied column definitions will be
+ copied. By default, new columns will be regular base columns.
+ </para>
+ </listitem>
+ </varlistentry>
+
+ <varlistentry>
+ <term><literal>INCLUDING STATISTICS</literal></term>
+ <listitem>
+ <para>
+ Extended statistics are copied to the new table.
+ </para>
+ </listitem>
+ </varlistentry>
+
+ <varlistentry>
+ <term><literal>INCLUDING ALL</literal></term>
+ <listitem>
+ <para>
+ <literal>INCLUDING ALL</literal> is an abbreviated form selecting
+ all the available individual options. (It could be useful to write
+ individual <literal>EXCLUDING</literal> clauses after
+ <literal>INCLUDING ALL</literal> to select all but some specific
+ options.)
+ </para>
+ </listitem>
+ </varlistentry>
+ </variablelist>
+ </para>
+ </listitem>
+ </varlistentry>
+
<varlistentry>
<term><literal>CONSTRAINT <replaceable class="parameter">constraint_name</replaceable></literal></term>
<listitem>
@@ -447,6 +557,15 @@ CREATE FOREIGN TABLE measurement_y2016m07
defined by <productname>PostgreSQL</productname>, is nonstandard.
</para>
+ <refsect2>
+ <title><literal>LIKE</literal> Clause</title>
+
+ <para>
+ The <literal>LIKE</literal> clause is a
+ <productname>PostgreSQL</productname> extension.
+ </para>
+ </refsect2>
+
</refsect1>
<refsect1>
diff --git a/src/backend/parser/parse_utilcmd.c b/src/backend/parser/parse_utilcmd.c
index ca028d2a66..6d966229e5 100644
--- a/src/backend/parser/parse_utilcmd.c
+++ b/src/backend/parser/parse_utilcmd.c
@@ -1117,6 +1117,10 @@ transformTableConstraint(CreateStmtContext *cxt, Constraint *constraint)
* process at this point, add the TableLikeClause to cxt->likeclauses, which
* will cause utility.c to call expandTableLikeClause() after the new
* table has been created.
+ *
+ * Some options are ignored. For example, as foreign tables have no
+ * storage, these options have no effect: storage, compression, identity
+ * and indexes. Similarly, INCLUDING INDEXES is ignored from a view.
*/
static void
transformTableLikeClause(CreateStmtContext *cxt, TableLikeClause *table_like_clause)
@@ -1131,12 +1135,6 @@ transformTableLikeClause(CreateStmtContext *cxt, TableLikeClause *table_like_cla
setup_parser_errposition_callback(&pcbstate, cxt->pstate,
table_like_clause->relation->location);
- /* we could support LIKE in many cases, but worry about it another day */
- if (cxt->isforeign)
- ereport(ERROR,
- (errcode(ERRCODE_FEATURE_NOT_SUPPORTED),
- errmsg("LIKE is not supported for creating foreign tables")));
-
/* Open the relation referenced by the LIKE clause */
relation = relation_openrv(table_like_clause->relation, AccessShareLock);
@@ -1217,7 +1215,8 @@ transformTableLikeClause(CreateStmtContext *cxt, TableLikeClause *table_like_cla
* Copy identity if requested
*/
if (attribute->attidentity &&
- (table_like_clause->options & CREATE_TABLE_LIKE_IDENTITY))
+ (table_like_clause->options & CREATE_TABLE_LIKE_IDENTITY) &&
+ !cxt->isforeign)
{
Oid seq_relid;
List *seq_options;
@@ -1236,14 +1235,16 @@ transformTableLikeClause(CreateStmtContext *cxt, TableLikeClause *table_like_cla
}
/* Likewise, copy storage if requested */
- if (table_like_clause->options & CREATE_TABLE_LIKE_STORAGE)
+ if ((table_like_clause->options & CREATE_TABLE_LIKE_STORAGE) &&
+ !cxt->isforeign)
def->storage = attribute->attstorage;
else
def->storage = 0;
/* Likewise, copy compression if requested */
- if ((table_like_clause->options & CREATE_TABLE_LIKE_COMPRESSION) != 0
- && CompressionMethodIsValid(attribute->attcompression))
+ if ((table_like_clause->options & CREATE_TABLE_LIKE_COMPRESSION) != 0 &&
+ CompressionMethodIsValid(attribute->attcompression) &&
+ !cxt->isforeign)
def->compression =
pstrdup(GetCompressionMethodName(attribute->attcompression));
else
@@ -1522,7 +1523,8 @@ expandTableLikeClause(RangeVar *heapRel, TableLikeClause *table_like_clause)
* Process indexes if required.
*/
if ((table_like_clause->options & CREATE_TABLE_LIKE_INDEXES) &&
- relation->rd_rel->relhasindex)
+ relation->rd_rel->relhasindex &&
+ childrel->rd_rel->relkind != RELKIND_FOREIGN_TABLE)
{
List *parent_indexes;
ListCell *l;
diff --git a/src/test/regress/expected/create_table_like.out b/src/test/regress/expected/create_table_like.out
index e061389135..c38118a8ad 100644
--- a/src/test/regress/expected/create_table_like.out
+++ b/src/test/regress/expected/create_table_like.out
@@ -563,3 +563,80 @@ DROP TYPE ctlty1;
DROP VIEW ctlv1;
DROP TABLE IF EXISTS ctlt4, ctlt10, ctlt11, ctlt11a, ctlt12;
NOTICE: table "ctlt10" does not exist, skipping
+--
+-- CREATE FOREIGN TABLE LIKE
+--
+CREATE FOREIGN DATA WRAPPER ctl_dummy;
+CREATE SERVER ctl_s0 FOREIGN DATA WRAPPER ctl_dummy;
+CREATE TABLE ctl_table(a int primary key, b varchar COMPRESSION pglz,
+ c int GENERATED ALWAYS AS (a * 2) STORED,
+ d bigint GENERATED ALWAYS AS IDENTITY,
+ e int default 1);
+CREATE INDEX ctl_table_a_key ON ctl_table(a);
+COMMENT ON COLUMN ctl_table.b IS 'Column b';
+CREATE STATISTICS ctl_table_stat ON a,b FROM ctl_table;
+ALTER TABLE ctl_table add constraint foo CHECK (b = 'text');
+\d+ ctl_table
+ Table "public.ctl_table"
+ Column | Type | Collation | Nullable | Default | Storage | Stats target | Description
+--------+-------------------+-----------+----------+------------------------------------+----------+--------------+-------------
+ a | integer | | not null | | plain | |
+ b | character varying | | | | extended | | Column b
+ c | integer | | | generated always as (a * 2) stored | plain | |
+ d | bigint | | not null | generated always as identity | plain | |
+ e | integer | | | 1 | plain | |
+Indexes:
+ "ctl_table_pkey" PRIMARY KEY, btree (a)
+ "ctl_table_a_key" btree (a)
+Check constraints:
+ "foo" CHECK (b::text = 'text'::text)
+Statistics objects:
+ "public.ctl_table_stat" ON a, b FROM ctl_table
+Not-null constraints:
+ "ctl_table_a_not_null" NOT NULL "a"
+ "ctl_table_d_not_null" NOT NULL "d"
+
+-- Test EXCLUDING ALL
+CREATE FOREIGN TABLE ctl_foreign_table1(LIKE ctl_table EXCLUDING ALL) SERVER ctl_s0;
+\d+ ctl_foreign_table1
+ Foreign table "public.ctl_foreign_table1"
+ Column | Type | Collation | Nullable | Default | FDW options | Storage | Stats target | Description
+--------+-------------------+-----------+----------+---------+-------------+----------+--------------+-------------
+ a | integer | | not null | | | plain | |
+ b | character varying | | | | | extended | |
+ c | integer | | | | | plain | |
+ d | bigint | | not null | | | plain | |
+ e | integer | | | | | plain | |
+Not-null constraints:
+ "ctl_table_a_not_null" NOT NULL "a"
+ "ctl_table_d_not_null" NOT NULL "d"
+Server: ctl_s0
+
+\set HIDE_TOAST_COMPRESSION false
+-- Test INCLUDING ALL
+-- INDEXES, IDENTITY, COMPRESSION, STORAGE are not copied.
+CREATE FOREIGN TABLE ctl_foreign_table2(LIKE ctl_table INCLUDING ALL) SERVER ctl_s0;
+\d+ ctl_foreign_table2
+ Foreign table "public.ctl_foreign_table2"
+ Column | Type | Collation | Nullable | Default | FDW options | Storage | Stats target | Description
+--------+-------------------+-----------+----------+------------------------------------+-------------+----------+--------------+-------------
+ a | integer | | not null | | | plain | |
+ b | character varying | | | | | extended | | Column b
+ c | integer | | | generated always as (a * 2) stored | | plain | |
+ d | bigint | | not null | | | plain | |
+ e | integer | | | 1 | | plain | |
+Check constraints:
+ "foo" CHECK (b::text = 'text'::text)
+Statistics objects:
+ "public.ctl_foreign_table2_a_b_stat" ON a, b FROM ctl_foreign_table2
+Not-null constraints:
+ "ctl_table_a_not_null" NOT NULL "a"
+ "ctl_table_d_not_null" NOT NULL "d"
+Server: ctl_s0
+
+\set HIDE_TOAST_COMPRESSION true
+DROP TABLE ctl_table;
+DROP FOREIGN TABLE ctl_foreign_table1;
+DROP FOREIGN TABLE ctl_foreign_table2;
+DROP FOREIGN DATA WRAPPER ctl_dummy CASCADE;
+NOTICE: drop cascades to server ctl_s0
diff --git a/src/test/regress/sql/create_table_like.sql b/src/test/regress/sql/create_table_like.sql
index a41f8b83d7..98ca8ac060 100644
--- a/src/test/regress/sql/create_table_like.sql
+++ b/src/test/regress/sql/create_table_like.sql
@@ -225,3 +225,37 @@ DROP SEQUENCE ctlseq1;
DROP TYPE ctlty1;
DROP VIEW ctlv1;
DROP TABLE IF EXISTS ctlt4, ctlt10, ctlt11, ctlt11a, ctlt12;
+
+--
+-- CREATE FOREIGN TABLE LIKE
+--
+CREATE FOREIGN DATA WRAPPER ctl_dummy;
+CREATE SERVER ctl_s0 FOREIGN DATA WRAPPER ctl_dummy;
+
+CREATE TABLE ctl_table(a int primary key, b varchar COMPRESSION pglz,
+ c int GENERATED ALWAYS AS (a * 2) STORED,
+ d bigint GENERATED ALWAYS AS IDENTITY,
+ e int default 1);
+
+CREATE INDEX ctl_table_a_key ON ctl_table(a);
+COMMENT ON COLUMN ctl_table.b IS 'Column b';
+CREATE STATISTICS ctl_table_stat ON a,b FROM ctl_table;
+ALTER TABLE ctl_table add constraint foo CHECK (b = 'text');
+
+\d+ ctl_table
+
+-- Test EXCLUDING ALL
+CREATE FOREIGN TABLE ctl_foreign_table1(LIKE ctl_table EXCLUDING ALL) SERVER ctl_s0;
+\d+ ctl_foreign_table1
+
+\set HIDE_TOAST_COMPRESSION false
+-- Test INCLUDING ALL
+-- INDEXES, IDENTITY, COMPRESSION, STORAGE are not copied.
+CREATE FOREIGN TABLE ctl_foreign_table2(LIKE ctl_table INCLUDING ALL) SERVER ctl_s0;
+\d+ ctl_foreign_table2
+\set HIDE_TOAST_COMPRESSION true
+
+DROP TABLE ctl_table;
+DROP FOREIGN TABLE ctl_foreign_table1;
+DROP FOREIGN TABLE ctl_foreign_table2;
+DROP FOREIGN DATA WRAPPER ctl_dummy CASCADE;
--
2.34.1
Re: Proposal to CREATE FOREIGN TABLE LIKE
On Tue, Feb 18, 2025 at 08:43:02PM +0800, Zhang Mingli wrote:
On Feb 18, 2025 at 09:54 +0800, Zhang Mingli <zmlpostgres@gmail.com>, wrote:
Will address the comments later, thanks for review!
Done in patch v6.
There was a hole in the tests for the option LIKE_STORAGE. Removing
the check for it in transformTableLikeClause() did now show a diff in
the tests. In the case of foreign tables, extended for storage is a
correct choice when using a text type for an attribute. It makes more
sense to use something like "main" on the origin table, then check
that the foreign table uses "extended", for example.
\d+ for a foreign table has no compression field, so using
HIDE_TOAST_COMPRESSION has no meaning. Removing the check for the
option LIKE_COMPRESSION leads to no diffs in the regression tests.
The other two restrictions for indexes and identity were OK.
The docs are fine after a closer look, relying mostly on the clauses
supported by the CREATE FOREIGN TABLE command, tweaked a bit the part
at the bottom where LIKE is not part of the standard.
And applied.
--
Michael
Re: Proposal to CREATE FOREIGN TABLE LIKE
On Feb 19, 2025 at 14:53 +0800, Michael Paquier <michael@paquier.xyz>, wrote:
There was a hole in the tests for the option LIKE_STORAGE. Removing
the check for it in transformTableLikeClause() did now show a diff in
the tests. In the case of foreign tables, extended for storage is a
correct choice when using a text type for an attribute. It makes more
sense to use something like "main" on the origin table, then check
that the foreign table uses "extended", for example.
You're right.
That was my mistake when I squashed the independent `like_options` cases into the two cases (`INCLUDING ALL`/`EXCLUDING ALL`) ,
particularly where there is an `ALTER STORAGE` before creating the foreign table, which shows the STORAGE difference.
Thanks for the correction.
\d+ for a foreign table has no compression field, so using
HIDE_TOAST_COMPRESSION has no meaning. Removing the check for the
option LIKE_COMPRESSION leads to no diffs in the regression tests.The other two restrictions for indexes and identity were OK.
The docs are fine after a closer look, relying mostly on the clauses
supported by the CREATE FOREIGN TABLE command, tweaked a bit the part
at the bottom where LIKE is not part of the standard.And applied.
Thanks.
--
Zhang Mingli
HashData