I believe most users would anticipate a CREATE TABLE statement that aligns with the currently installed version- this is the practical solution for the vast majority.
In situations where a CREATE TABLE statement compatible with an older version of Postgres is required, users can opt for an additional step of using tools like pg_dump or an older version of Postgres itself. This allows them to ensure compatibility without compromising the practicality of the process. On Fri, 12 May 2023 at 06:47, Stephen Frost <sfr...@snowman.net> wrote: > Greetings, > > * Nathaniel Sabanski (sabansk...@gmail.com) 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 >
