Hi @douglasshuang. You’re bringing up a great point here. I want to make a correction to the book text based on this to help readers.

Here’s the domain definition in the book:

-- This is the existing domain in the book.
CREATE DOMAIN vehicle_statuses AS TEXT
CONSTRAINT valid_vehicle_statuses
CHECK (VALUE IN ('draft', 'published') );

This omits a NOT NULL constraint in the domain definition. If we were to add a NOT NULL to the domain like this:

-- Create a new domain called vehicle_statuses_not_null and include a NOT NULL
CREATE DOMAIN vehicle_statuses_not_null AS TEXT
CONSTRAINT valid_vehicle_statuses
CHECK (VALUE IN ('draft', 'published') )
NOT NULL;

Then the domain will enforce the not null.

Here’s an example:

-- Add column “status_new_domain”  that uses the domain above with NOT NULL.
-- Empty out the table rows so we can try inserting
ALTER TABLE vehicles
ADD COLUMN status_new_domain vehicle_statuses_not_null;

-- Describe the domains. Notice the nullable property has the NOT NULL constraint.
owner@[local]:5432 rideshare_development# \dD
                                                               List of domains
 Schema   |           Name            | Type | Collation | Nullable | Default |                             Check
-----------+---------------------------+------+-----------+----------+---------+---------------------------------------------------------------
rideshare | vehicle_statuses_not_null | text |           | not null |         | CHECK (VALUE = ANY (ARRAY['draft'::text, 'published'::text]))


-- Describe the vehicles table. Only showing the “status_new_domain” column.
-- Notice here that status_new_domain shows as nullable
-- i.e. we don't see the NOT NULL from the domain, confusing!
\d vehicles
owner@[local]:5432 rideshare_development# \d vehicles
                                           Table "rideshare.vehicles"
     Column       |              Type              | Collation | Nullable |               Default
-------------------+--------------------------------+-----------+----------+--------------------------------------
status_new_domain | vehicle_statuses_not_null      |           |          |

owner@[local]:5432 rideshare_development# insert into vehicles (name) values ('draft');
ERROR:  domain vehicle_statuses_not_null does not allow null values


-- Try to insert a null value in status_new_domain
owner@[local]:5432 rideshare_development# insert into vehicles (name, status_new_domain) values ('draft', null);
ERROR:  domain vehicle_statuses_not_null does not allow null values

If we add an additional NOT NULL to the column on the table, that’s more clear. Then describing the table shows it as NOT NULL.

After considering this more since you’ve brought it up, using not null constraints in Domains to illustrate the difference between an Enum and a Domain type feels like a poor choice for learning materials. It’s useful but more like an edge case.

I would like to expand on this in a better example as a blog post, or possibly in a future iteration of the book. I think focusing on the base data type for a Domain for example, and the behavior and the functionality it brings over an Enum, would be a better example for learning the differences. We could note this gotcha with NOT NULL constraints as the documentation does.

To make a targeted correction in the book, I think we’ll add a NOT NULL constraint to the domain definition though, and a warning about the implicit behavior, to keep the changes minimal.

Docs: PostgreSQL: Documentation: 17: CREATE DOMAIN

What do you think?

Thanks again for noticing this and spending the time to write up this issue!