Date: Sat, 1 Feb 2025 14:55:12 +0800
From: Zhang Mingli <zmlpostgres@gmail.com>
To: PostgreSQL Hackers <pgsql-hackers@lists.postgresql.org>
Message-ID: <42d3f855-2275-4361-a42a-826172ca2dc4@Spark>
In-Reply-To: <8eac1bb4-bfbf-4b32-b439-adcbdcd9ed98@Spark>
References: <8eac1bb4-bfbf-4b32-b439-adcbdcd9ed98@Spark>
Subject: Proposal to CREATE FOREIGN TABLE LIKE
MIME-Version: 1.0
Content-Type: multipart/alternative; boundary="679dc556_7117cfb_10188"
Archived-At: <https://www.postgresql.org/message-id/42d3f855-2275-4361-a42a-826172ca2dc4%40Spark>
Precedence: bulk

--679dc556_7117cfb_10188
Content-Type: text/plain; charset="utf-8"
Content-Transfer-Encoding: quoted-printable
Content-Disposition: inline

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 Cloud=
berry,
and we often need to create foreign tables that match the column definiti=
ons of local tables.

When dealing with wide tables and lots of those foreign tables, it can ge=
t pretty tedious and mistakes happen easily.
We end up having to troubleshoot errors when querying, which is a hassle.=

Sure, we could use=C2=A0pg=5Fdump=C2=A0to get the table DDL and modify th=
e name, but that just adds more busywork.
CREATE =46OREIGN TABLE LIKE=C2=A0command could save a lot of time and red=
uce errors in the long run.
It would work similarly to=C2=A0CREATE TABLE LIKE, copying the column def=
initions and constraints from the source table.

And since Postgres doesn=E2=80=99t enforce constraints on foreign tables,=
 it=E2=80=99s up to the user to make sure the constraints match the actua=
l data.
https://www.postgresql.org/docs/current/sql-createforeigntable.html

This means that enabling=C2=A0CREATE =46OREIGN TABLE LIKE=C2=A0shouldn=E2=
=80=99t introduce more issues with constraints

I haven=E2=80=99t rush with the codes yet, but it seems like it could be =
straightforward to implement by tweaking the existing limitations:

=60=60=60
static void
transformTableLikeClause(CreateStmtContext *cxt, TableLikeClause *table=5F=
like=5Fclause)
=7B
	...
	if (cxt->isforeign)
		ereport(ERROR,
				(errcode(ERRCODE=5F=46EATURE=5FNOT=5FSUPPORTED),
				 errmsg(=22LIKE is not supported for creating foreign tables=22)));
=7D
=60=60=60

with some test cases and Documents changes.


Zhang Mingli
www.hashdata.xyz

--679dc556_7117cfb_10188
Content-Type: text/html; charset="utf-8"
Content-Transfer-Encoding: quoted-printable
Content-Disposition: inline

<html xmlns=3D=22http://www.w3.org/1999/xhtml=22>
<head>
<title></title>
</head>
<body>
<div name=3D=22messageBodySection=22>
<div dir=3D=22auto=22>Hi, all<br />
<br />
<br />
I wanted to bring up an idea that could really help out.<br />
Our DBA team uses foreign tables for ETL processes in Greenplum and Cloud=
berry,<br />
and we often need to create foreign tables that match the column definiti=
ons of local tables.<br />
<br />
When dealing with wide tables and lots of those foreign tables, it can ge=
t pretty tedious and mistakes happen easily.&=23160;<br />
We end up having to troubleshoot errors when querying, which is a hassle.=
&=23160;<br />
Sure, we could use&=23160;<code>pg=5Fdump</code>&=23160;to get the table =
DDL and modify the name, but that just adds more busywork.<br />
<code>CREATE =46OREIGN TABLE LIKE</code>&=23160;command could save a lot =
of time and reduce errors in the long run.<br />
It would work similarly to&=23160;<code>CREATE TABLE LIKE</code>, copying=
 the column definitions and constraints from the source table.<br />
<br />
And since Postgres doesn=E2=80=99t enforce constraints on foreign tables,=
 it=E2=80=99s up to the user to make sure the constraints match the actua=
l data.&=23160;<br />
<a href=3D=22https://www.postgresql.org/docs/current/sql-createforeigntab=
le.html=22 target=3D=22=5Fblank=22>https://www.postgresql.org/docs/curren=
t/sql-createforeigntable.html</a><br />
<br />
This means that enabling&=23160;<code>CREATE =46OREIGN TABLE LIKE</code>&=
=23160;shouldn=E2=80=99t introduce more issues with constraints<br />
<br />
I haven=E2=80=99t rush with the codes yet, but it seems like it could be =
straightforward to implement by tweaking the existing limitations:&=23160=
;<br />
<br />
=60=60=60<br />
static void<br />
transformTableLikeClause(CreateStmtContext *cxt, TableLikeClause *table=5F=
like=5Fclause)<br />
=7B<br />
<span style=3D=22white-space:pre=22>	...</span><br />
<span style=3D=22white-space:pre=22>	if (cxt-&gt;isforeign)</span><br />
<span style=3D=22white-space:pre=22>		ereport(ERROR,</span><br />
<span style=3D=22white-space:pre=22>				(errcode(ERRCODE=5F=46EATURE=5FNO=
T=5FSUPPORTED),</span><br />
<span style=3D=22white-space:pre=22>				 errmsg(=22LIKE is not supported =
for creating foreign tables=22)));</span><br />
=7D<br />
=60=60=60<br />
<br />
with some test cases and Documents changes.</div>
</div>
<div name=3D=22messageSignatureSection=22><br />
<div class=3D=22match=46ont=22>
<div dir=3D=22auto=22><br /></div>
Zhang Mingli
<div dir=3D=22auto=22><span style=3D=22color: var(--textColor); backgroun=
d-color: var(--backgroundColor);=22>www.hashdata.xyz</span><br /></div>
</div>
</div>
</body>
</html>

--679dc556_7117cfb_10188--