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
