Re: Retail DDL

On Mon, 18 Aug 2025 at 15:57, Tom Lane <tgl@sss.pgh.pa.us> wrote:
> You haven't actually defined what "this" is.  For starters, do you
> really want this output to be included in \d?  Seems like one part
> or the other of such output would be clutter, so I'd be more minded
> to leave \d alone and invent some new command.  (By analogy to \sf,
> maybe \st and so on?)

That makes sense, I don't think I had seen the \s ones before. IMO it
would be super useful to have a \s for every type of object that
currently has a \d (but those don't have to be all added in the same
patchset ofcours, starting with tables seems totally sensible)

> But the real issue is what to print.

I think you're making this sound much harder than it actually is. I
think it would be perfect if it had exactly the same info as \d but in
SQL form instead of some "easy to understand by humans form".  So:

> should we also show its indexes?

Yes

> What about foreign keys to or from other
> tables?

Yes, both from and to

> If it's a partitioned table, what about the partitions?

Definitely not by default, way too much clutter.

I think having a DESCRIBE keyword is probably not what we want, but
adding a pg_get_tabledef function seems totally reasonable. I even
proposed that at some point[1], but apparently never followed up with
Kirk (cc-ed now). It could even have options for all the questions
that you're asking like, so we'd "just" need to decide on the
defaults:

SELECT pg_get_tabledef('my_table', include_indexes => true,
include_partitions => true)

[1]: https://www.postgresql.org/message-id/CAGECzQRuHBs9gjPbvgabQv8XS3QRU9Ex=nH84S_1=wo4POzBzg@mail.gmail.com