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 1pxZA1-0007bA-Ef for pgsql-general@arkaria.postgresql.org; Fri, 12 May 2023 20:14:41 +0000 Received: from localhost ([127.0.0.1] helo=malur.postgresql.org) by malur.postgresql.org with esmtp (Exim 4.92) (envelope-from ) id 1pxZA0-0007un-B0 for pgsql-general@arkaria.postgresql.org; Fri, 12 May 2023 20:14:40 +0000 Received: from makus.postgresql.org ([2001:4800:3e1:1::229]) by malur.postgresql.org with esmtps (TLS1.3:ECDHE_RSA_AES_256_GCM_SHA384:256) (Exim 4.92) (envelope-from ) id 1pxZ9z-0007ud-Lb for pgsql-general@lists.postgresql.org; Fri, 12 May 2023 20:14:40 +0000 Received: from tamriel.snowman.net ([70.109.60.50]) by makus.postgresql.org with esmtps (TLS1.3) tls TLS_ECDHE_RSA_WITH_AES_256_GCM_SHA384 (Exim 4.94.2) (envelope-from ) id 1pxZ9s-001zHz-RA for pgsql-general@lists.postgresql.org; Fri, 12 May 2023 20:14:38 +0000 Received: by tamriel.snowman.net (Postfix, from userid 1000) id A10CD5F7B7; Fri, 12 May 2023 16:14:27 -0400 (EDT) DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=snowman.net; s=dkim; t=1683922467; 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=kyVRGfGPZv1L5nWC6HhMuDjAV9+EBJ6QkCanQWVtRf4=; b=M5bRYjvthJodhoq8Ytz8AN4QDtYNpVMVmNeUaIikyCx27VfwB9zC+pdL5z5nryHgPZ22Hg v0RDsOn5RUOvED+XzaG+sGSX1YFrA2EV0jtoJtkkDWNy0vsvL+92nlYhzyYVi3ETsEtDHr jbsvZRVnBSLMwl+jVYdP2Oo2YJObXO4= Date: Fri, 12 May 2023 16:14:27 -0400 From: Stephen Frost To: Nathaniel Sabanski Cc: pgsql-general@lists.postgresql.org Subject: Re: Adding SHOW CREATE TABLE Message-ID: References: <7a5d6750-84fa-59b0-33c1-7a5d26b9bc74@evolvis.org> MIME-Version: 1.0 Content-Type: multipart/signed; micalg=pgp-sha512; protocol="application/pgp-signature"; boundary="T5c1+jqVLZpFKSHf" 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 --T5c1+jqVLZpFKSHf Content-Type: text/plain; charset=us-ascii Content-Disposition: inline Content-Transfer-Encoding: quoted-printable Greetings, * Nathaniel Sabanski (sabanski.n@gmail.com) wrote: > > Perhaps a bit more discussion about what exactly the use-case is would > > be helpful- what would you use this feature for? >=20 > App writers: To facilitate table creation and simplify schema verificatio= n, > without relying on a GUI tool or ORM (or system calls out to pg_dump). Not sure how it would simplify schema verification? > Tool writers: Would drastically cut down the implementation time and > complexity to support Postgres. I am one of the devs of Piccolo ORM (Pyth= on > lib supporting Postgres) and we have a lot of code dedicated to > re-generating the CREATE TABLE statements (creation, during migrations, > etc) that could be done better by Postgres itself. I'm curious- have you compared what you're doing to pg_dump's output? Are you confident that there aren't any distinctions between those that, for whatever reason, need to exist? > Moving to Postgres: It would help ease migrations for developers wanting = to > move from MySQL / Percona / MariaDB to Postgres. Also it's a nice develop= er > experience to see how Postgres generates X table without extra tooling. Seems unlikely that this would actually be all that helpful there- tools like ora2pg and similar know how to query other database systems and write appropriate CREATE TABLE statements for PostgreSQL. > The intention of SHOW CREATE TABLE is not to replace the existing suite of > \d in psql but rather to be a developer friendly complement within SQL > itself. Sure, I get that. Again, would be great to see someone actually work on this. There's already a good chunk of code in core in pg_dump and in the postgres_fdw for doing exactly this and it'd be great to consolidate that and at the same time expose it via SQL. Another possible option would be to add this to libpq, which is used by postgres_fdw, psql, pg_dump, and lots of other drivers and client utilities. If it's all broadly the same, personally I'd prefer it to be in the common library and available as a backend SQL command too, but perhaps there's reasons that it would be easier to implement in libpq instead. Thanks, Stephen --T5c1+jqVLZpFKSHf Content-Type: application/pgp-signature; name="signature.asc" -----BEGIN PGP SIGNATURE----- iQIzBAABCgAdFiEEwf6gbxKhD863zrx/7WyKOINHZFUFAmReniEACgkQ7WyKOINH ZFUhThAAs+6zMT5BvwzIxpQnCy7guJmTC7DpufZbIwCB5yLo66EBh7LWVKOpE6IP iTOv/sn3/iZeAhfy1Jq0knm5CvuJ1cCHt9L8XQ53gjWlb9bsqqwDITPOm23GWn93 Ef4OZ5wHXgTAoClaBDlPZJYI1k+V55AoPbQ+R6MnR/MS4pPFEaJi0oCqfvBaoI88 lcQu6FxhzV0lmCi+xyJMD8A+bo/4H/cnj1w6l0EwvNVQgvYtT4Eja0JdmKvEYDXO bV8BMMtWmvkkMpP2jRVXpRSIvR+Izm2cKKuIc2VIGWJm0Pq1p2s2UymtIaqPM95T oP5aMSySr3gVZlMLcjXtF7NlLtTqn6cvrh3q5DtZvGcdkilRYShNgZPs5AsU9m30 TwO0+k8fR4kaY+bIbg2s6SoviuK2EomUgk0nstSGO/FZA1T0u8fb2Y/Bm9WOo30T V8OjTUtNj/vDulBLCjPdtL3bFtpbrNcEQVvjAV3mbj3epWW7U1wvC3H82XD6fyDJ BkjD5Ae9IzFcMSdriWddTc2gl0R0z5fLxPXVnckPD5g0dTwM8ux7z1G3/q6isKR/ OCjRqm21W6kRjV7G1BjQpYmudI6JjsMcjRvSpGYPFussK2uVph1ozehKIVnnuY78 mQw9XAuVGutY+2BahImLYV2eqYeg51je6yZTie8kPG7iL00ck10= =OQUc -----END PGP SIGNATURE----- --T5c1+jqVLZpFKSHf--