Greetings, * Nathaniel Sabanski (sabanski.n@xxxxxxxxx) wrote: > HN had a thread regarding the challenges faced by new users during the > adoption of Postgres in 2023. > > 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=35908991 > > 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 ... 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
Attachment:
signature.asc
Description: PGP signature
