Received: from malur.postgresql.org ([217.196.149.56]) by arkaria.postgresql.org with esmtps (TLS1.3:ECDHE_RSA_AES_256_GCM_SHA384:256) (Exim 4.92) (envelope-from ) id 1pxT7O-0005tJ-Mg for pgsql-general@arkaria.postgresql.org; Fri, 12 May 2023 13:47:34 +0000 Received: from localhost ([127.0.0.1] helo=malur.postgresql.org) by malur.postgresql.org with esmtp (Exim 4.92) (envelope-from ) id 1pxT7N-0006IW-Ju for pgsql-general@arkaria.postgresql.org; Fri, 12 May 2023 13:47:33 +0000 Received: from magus.postgresql.org ([2a02:c0:301:0:ffff::29]) by malur.postgresql.org with esmtps (TLS1.3:ECDHE_RSA_AES_256_GCM_SHA384:256) (Exim 4.92) (envelope-from ) id 1pxT7M-0006IN-Uc for pgsql-general@lists.postgresql.org; Fri, 12 May 2023 13:47:33 +0000 Received: from tamriel.snowman.net ([2001:470:e38f::11]) by magus.postgresql.org with esmtps (TLS1.3) tls TLS_ECDHE_RSA_WITH_AES_256_GCM_SHA384 (Exim 4.94.2) (envelope-from ) id 1pxT7J-002DQc-8n for pgsql-general@lists.postgresql.org; Fri, 12 May 2023 13:47:32 +0000 Received: by tamriel.snowman.net (Postfix, from userid 1000) id 4AB365F7B7; Fri, 12 May 2023 09:47:23 -0400 (EDT) DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=snowman.net; s=dkim; t=1683899243; h=from:from:reply-to:subject:subject:date:date:message-id:message-id: to:to:cc:cc:mime-version:mime-version:content-type:content-type: in-reply-to:in-reply-to:references:references; bh=IrWVDcEWmHEYyYQZGnAUskfxalfp570FLg+dQKbcVrc=; b=WWTggr+sWRe/RfuT882Hqa2xm1/mW6brMLqABCxgw2lg5/qDxs7TkHpS1J+0zbc33m3/C/ GFLLOBA65oCzcxWtkoWhqadCGDjI2SMLLZRbWQOKa2gX0/WbzwJQ4Z9o+ec4EcA6hyOU6Y pLvPpal+sW0uNBtZU1AzvudWmj0qrcs= Date: Fri, 12 May 2023 09:47:23 -0400 From: Stephen Frost To: Nathaniel Sabanski Cc: pgsql-general@lists.postgresql.org Subject: Re: Adding SHOW CREATE TABLE Message-ID: References: MIME-Version: 1.0 Content-Type: multipart/signed; micalg=pgp-sha512; protocol="application/pgp-signature"; boundary="1hU8u1owuJ22vBEw" Content-Disposition: inline In-Reply-To: User-Agent: Mutt/2.1.4 (2021-12-11) List-Id: List-Help: List-Subscribe: List-Post: List-Owner: List-Archive: Archived-At: Precedence: bulk --1hU8u1owuJ22vBEw Content-Type: text/plain; charset=us-ascii Content-Disposition: inline Content-Transfer-Encoding: quoted-printable Greetings, * Nathaniel Sabanski (sabanski.n@gmail.com) wrote: > HN had a thread regarding the challenges faced by new users during the > adoption of Postgres in 2023. >=20 > One particular issue that garnered significant votes was the lack of a > "SHOW CREATE TABLE" command, and seems like it would be an easy one to > implement: https://news.ycombinator.com/item?id=3D35908991 >=20 > Considering the popularity of this request and its potential ease of > implementation, I wanted to bring it to your attention, as it would likely > enhance the user experience and alleviate some of the difficulties > encountered by newcomers. This isn't as easy as it seems actually ...=20 Note that using pg_dump for this purpose works quite well and also works to address cross-version issues. Consider that pg_dump v15 is able to connect to v14, v13, v12, v11, and more, and produce a CREATE TABLE command that will work with *v15*. If you connected to a v14 database and did a SHOW CREATE TABLE, there's no guarantee that the CREATE TABLE statement returned would work for PG v15 due to keyword changes and other differences that can cause issues between major versions of PG. Now, that said, we have started ending up with some similar code between pg_dump and postgres_fdw in the form of IMPORT FOREIGN SCHEMA and maybe we should consider if that code could be moved into the common library and made available to pg_dump, postgres_fdw, and as a SHOW CREATE TABLE command with the caveat that the produced CREATE TABLE command may not work with newer versions of PG. There's an interesting question around if we'd consider it a bug worthy of fixing if IMPORT FOREIGN SCHEMA in v14 doesn't work when connecting to a v15 PG instance. Not sure if anyone's contemplated that. There's certainly going to be cases that we wouldn't accept fixing (we wouldn't add some new partitioning strategy to v14 just because it's in v15, for example, to make IMPORT FOREIGN SCHEMA work...). Thanks, Stephen --1hU8u1owuJ22vBEw Content-Type: application/pgp-signature; name="signature.asc" -----BEGIN PGP SIGNATURE----- iQIzBAABCgAdFiEEwf6gbxKhD863zrx/7WyKOINHZFUFAmReQ2gACgkQ7WyKOINH ZFWDhw//d84t/kPYUGsXurIqCKqyNGRPC4jwyjVvpfFiKZO1NvrX19W6FOHHRrRp y+bJixCl/3+YVEdh/Yj6mwv2YDY7beroaxkWbUKSv44AVYpsN0oPapacUYzQ58PY nK7EgkuzuorJPrj5iPHTx/uYWqIW/lLBVugCAjH3jHySywXw8qtEsc+H8SJ3JXPG L96OdGM4aYPdcYwzipohtuS8W2naWUaAJNZ5i6t4sQWPejKg7VrJAae+B/ZEiC93 9djJ3u155HA0VN0HdylRUN9GH8jLTZXhVaLH0XwJukM34cpnjkkxMKBvskGZTBt1 CF5Lmz/oVuiWvA/J2IjJcb6Ltr+MLLDVAtdRVUWr0M9702aRSSKmyFeVUoB2u72M IG9E6kMB4UiQAIT9a3EhIHEbw7QsEaLfCleSGnp5lZydF05FHIurBTY+5hSSQZKP F2lSDVKIo/iURET5F+vO6e2v9UtIap5eZ81t2JNZxNGEBkv5nRnEY0Y1TpG2NWxM J31YbToqnckUgKkSVucD/iigDEAgsyipJYSJ0YCRP8ATP7Y0tMp+pc9nSm+rvUZy n+i2sQvpfk5llcYQuEWoqqnf9KRDSDdrMLiFN1fOEs3KC2vkgVIa8B4lsTe+VV6s hrZry6BxIse9+yceDQp5cG8QtrzjPrsneZOTv9YJF+iHaWJliC0= =Md0o -----END PGP SIGNATURE----- --1hU8u1owuJ22vBEw--