From: Zhang Mingli <zmlpostgres@gmail.com>
To: PostgreSQL Hackers <pgsql-hackers@lists.postgresql.org>
Subject: Proposal to CREATE FOREIGN TABLE LIKE
Date: Sat, 1 Feb 2025 14:55:12 +0800
Message-ID: <42d3f855-2275-4361-a42a-826172ca2dc4@Spark> (raw)
In-Reply-To: <8eac1bb4-bfbf-4b32-b439-adcbdcd9ed98@Spark>
References: <8eac1bb4-bfbf-4b32-b439-adcbdcd9ed98@Spark>
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
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-hackers@postgresql.org
Cc: zmlpostgres@gmail.com, pgsql-hackers@lists.postgresql.org
Subject: Re: Proposal to CREATE FOREIGN TABLE LIKE
In-Reply-To: <42d3f855-2275-4361-a42a-826172ca2dc4@Spark>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox