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.
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@xxxxxxxxxxx> wrote:
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
