Hi Álvaro, Thanks for your comments!
On Tue, Jan 20, 2026 at 9:30 PM Álvaro Herrera <[email protected]> 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
