On Thu, 20 Jul 2023 at 16:31, Alvaro Herrera <alvhe...@alvh.no-ip.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