Re: cataloguing NOT NULL constraints

On Thu, 20 Jul 2023 at 16:31, Alvaro Herrera <alvherre(at)alvh(dot)no-ip(dot)org> wrote:
>
> On 2023-Jul-13, Dean Rasheed wrote:
>
> > I see that it's already been discussed, but I don't like the fact that
> > there is no way to get hold of the new constraint names in psql. I
> > think for the purposes of dropping named constraints, and also
> > possible future stuff like NOT VALID / DEFERRABLE constraints, having
> > some way to get their names will be important.
>
> Yeah, so there are two proposals:
>
> 1. Have \d+ replace the "not null" literal in the \d+ display with the
> constraint name; if the column is not nullable because of the primary
> key, it says "(primary key)" instead. There's a patch for this in the
> thread somewhere.
>
> 2. Same, but use \d++ for this purpose
>
> Using ++ would be a novelty in psql, so I'm hesitant to make that an
> integral part of the current proposal. However, to me (2) seems to most
> comfortable way forward, because while you're right that people do need
> the constraint name from time to time, this is very seldom the case, so
> polluting \d+ might inconvenience people for not much gain. And people
> didn't like having the constraint name in \d+.
>
> Do you have an opinion on these ideas?
>

Hmm, I don't particularly like that approach, because I think it will
be difficult to cram any additional details into the table, and also I
don't know whether having multiple not null constraints for a
particular column can be entirely ruled out.

I may well be in the minority here, but I think the best way is to
list them in a separate footer section, in the same way as CHECK
constraints, allowing other constraint properties to be included. So
it might look something like:

\d foo
Table "public.foo"
Column | Type | Collation | Nullable | Default
--------+---------+-----------+----------+---------
a | integer | | not null |
b | integer | | not null |
c | integer | | not null |
d | integer | | not null |
Indexes:
"foo_pkey" PRIMARY KEY, btree (a, b)
Check constraints:
"foo_a_check" CHECK (a > 0)
"foo_b_check" CHECK (b > 0) NO INHERIT NOT VALID
Not null constraints:
"foo_c_not_null" NOT NULL c
"foo_d_not_null" NOT NULL d NO INHERIT

As for CHECK constraints, the contents of each constraint line would
match the "table_constraint" SQL syntax needed to reconstruct the
constraint. Doing it this way allows for things like NOT VALID and
DEFERRABLE to be added in the future.

I think that's probably too verbose for a plain "\d", but I think it
would be OK with "\d+".

Regards,
Dean