Re: Adding SHOW CREATE TABLE

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 <sfrost(at)snowman(dot)net> wrote:

> Greetings,
>
> * Nathaniel Sabanski (sabanski(dot)n(at)gmail(dot)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
>