On Thu, 2024-05-02 at 08:23 -0700, David G. Johnston wrote:
> Version 2 attached. Still a draft, focused on topic picking and overall structure.
I'm fine with most of the material (ignoring ellipses and typos), except this:
+ The NOT NULL column constraint is largely syntax sugar for the corresponding
+ column IS NOT NULL check constraint, though there are metadata differences
+ described in create table.
I see a substantial difference there:
SELECT conname, contype,
pg_get_expr(conbin, 'not_null'::regclass)
FROM pg_constraint
WHERE conrelid = 'not_null'::regclass;
conname │ contype │ pg_get_expr
══════════════════════╪═════════╪══════════════════
check_null │ c │ (id IS NOT NULL)
not_null_id_not_null │ n │ ∅
(2 rows)
There is also the "attnotnull" column in "pg_attribute".
I didn't try it, but I guess that the performance difference will be measurable.
So I wouldn't call it "syntactic sugar".
Perhaps: The behavior of the NOT NULL constraint is like that of a check
constraint with IS NOT NULL.
Yours,
Laurenz Albe