Re: Retail DDL

On Sat, 16 Aug 2025 at 16:23, Tom Lane <tgl@sss.pgh.pa.us> wrote:
> So I don't really buy Álvaro's argument above.  It'd be better
> to design to some concrete requirement that isn't either of
> those.  Unfortunately, it's not clear to me that anyone has
> a concrete use-case in mind that isn't either of those.

I have wanted this MANY times. I've had this as a PG user: I think
it's literally the first thing I did when connecting to a Postgres
server the first time. Coming from MySQL, I wanted to see the exact
table definition, and there it was as easy as "DESCRIBE some_table". I
quickly learned about "\d some_table", but it was not the same. I
could not copy paste the result and slightly modify it to create a new
(but similar) table, which is something I still would like to have to
this day. I do not know table DDL by heart, so often I just want an
example to start from.

I don't think this should be thought of as replacing \d, but it could
greatly improve it. Just like "\d+ some_view" shows the view
definition, "\d+ some_table" could be showing the table definition in
SQL. Not having this in core, will make it impossible for psql to show
such definitions.

Finally I've also wanted this as an extension author: We basically
built something like this for Citus to be able to recreate
shard-tables with the same things as parent tables. And I did a
similar thing in pg_duckdb again. I don't think I would have been able
to use this code verbatim for these usecases (similarly to how pg_dump
couldn't), but I would at least have a good base that I could copy
paste from.