public inbox for [email protected]  

help / color / mirror / Atom feed

From: Zhang Mingli <[email protected]>
To: PostgreSQL Hackers <[email protected]>
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

view thread (2+ messages)

reply

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: [email protected]
  Cc: [email protected], [email protected]
  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 agora; see mirroring instructions
for how to clone and mirror all data and code used for this inbox