Re: Retail DDL

On 2025-08-18 Mo 9:57 AM, Tom Lane wrote:
> Jelte Fennema-Nio <postgres(at)jeltef(dot)nl> writes:
>> On Sat, 16 Aug 2025 at 16:23, Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)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".
> 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?)

Yes, I agree. A separate metacommand would make more sense. Maybe
something like \sdx where x is some object type designator. (sd stands
for show ddd or show definition)

>
> But the real issue is what to print. In the case of a table, should
> we also show its indexes? What about foreign keys to or from other
> tables? If it's a partitioned table, what about the partitions?
> I'm not sure this is as simple as it seems.
>
>

Agreed it's not simple, but that doesn't mean we should not do it.
Tables are the most obviously complex case. I'm inclined to say foreign
keys to but not from, and also include indexes. But maybe we can provide
several flavors, by allowing some function options, e.g.

   \sdt would show the basic table def without FKs or secondary
indexes, and

   \sdt+ would show everything

Or we could get more fine-grained.

cheers

andrew

--
Andrew Dunstan
EDB: https://www.enterprisedb.com