Re: [PATCH] psql: add \dcs to list all constraints

Hi Álvaro,

Thanks for your comments!

On Tue, Jan 20, 2026 at 9:30 PM Álvaro Herrera <alvherre(at)kurilemu(dot)de> wrote:
> Yeah, these results match my own tests -- the query I proposed is a bit
> slower. Not by a lot, but measurably so. (I did ran my disk full by
> running pgbench with the two queries in separate files and the one
> million constraints; temp files for sort are quite large. My query is
> pretty consistently reported to be slower than the other anyway.)

I see that you used pgbench to compare the throughput of the two
queries. I’m glad that you obtained consistent results regardless of
the individual environment.

> > What do you think?
>
> Looks good. I think the constraint types ought to be lower-case and
> fully spelled out, with not-null having an hyphen as discussed
> elsewhere (though I couldn't find the reference), so
> not-null
> primary key
> exclusion
> check
> foreign key
> unique
> trigger

Thanks.
I agree that all constraint types should be displayed in full and
in lowercase.

> I wonder if we should mark temporal constraints especially. (So
> "temporal primary key", etc). Without thinking too much about it, my
> impression is that it'd be useful.

I was not aware of temporal constraints.
Based on the article [1], I ran a quick test on temporal primary keys,
and they appear to be displayed correctly as primary keys:

-- Create employees table with temporal constraints
CREATE TABLE employees (
emp_id INTEGER,
emp_name VARCHAR(100) NOT NULL,
department VARCHAR(50) NOT NULL,
position VARCHAR(50) NOT NULL,
salary DECIMAL(10,2) NOT NULL,
valid_period tstzrange NOT NULL DEFAULT tstzrange(now(), 'infinity', '[)'),
-- Temporal primary key: no overlapping periods for same employee
PRIMARY KEY (emp_id, valid_period WITHOUT OVERLAPS)
);

# \dCNp+
List of constraints
Schema | Table | Type | Name | Definition
--------+----------+-------------+----------------+----------------------------------------------------
public | employees | primary key | employees_pkey | PRIMARY KEY
(emp_id, valid_period WITHOUT OVERLAPS)
(1 row)

I will investigate other types of temporal constraints and consider
adding them to the test cases. Thank you for pointing that out.

The next patch will include the following two fixes:
- Display all constraint types in full and in lowercase.
- Add test cases for temporal constraints.

[1] https://neon.com/postgresql/postgresql-18/temporal-constraints

Regards,
Tatsuro Yamada