Thread: Proposal to CREATE FOREIGN TABLE LIKE
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
It would work similarly to
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
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.
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 constraintsI 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.
www.hashdata.xyz