douglasshuang

douglasshuang

High Performance PostgreSQL for Rails: `NOT NULL` constraint not part of domain as stated (page 90)

The book states the following:

The main difference for domains compared with enums is that the NOT NULL constraint portion is part of the domain.

This is incorrect. The domain has a CHECK constraint for valid non-NULL values, but the NOT NULL constraint is part of the column declaration and can be removed independently of the domain, as follows:

owner@localhost:5432 rideshare_development# ALTER TABLE vehicles ALTER COLUMN status DROP NOT NULL;
ALTER TABLE
owner@localhost:5432 rideshare_development# UPDATE vehicles SET status = NULL;
UPDATE 4
owner@localhost:5432 rideshare_development# SELECT DISTINCT(status) from vehicles;
 status
--------

(1 row)

owner@localhost:5432 rideshare_development# \d vehicles
                                        Table "rideshare.vehicles"
   Column   |              Type              | Collation | Nullable |               Default
------------+--------------------------------+-----------+----------+--------------------------------------
 id         | bigint                         |           | not null | nextval('vehicles_id_seq'::regclass)
 name       | character varying              |           | not null |
 created_at | timestamp(6) without time zone |           | not null |
 updated_at | timestamp(6) without time zone |           | not null |
 status     | vehicle_statuses               |           |          | 'draft'::text
Indexes:
    "vehicles_pkey" PRIMARY KEY, btree (id)
    "index_vehicles_on_name" UNIQUE, btree (name)
Referenced by:
    TABLE "vehicle_reservations" CONSTRAINT "fk_rails_7edc8e666a" FOREIGN KEY (vehicle_id) REFERENCES vehicles(id)

owner@localhost:5432 rideshare_development# \dD vehicle_statuses
                                                           List of domains
  Schema   |       Name       | Type | Collation | Nullable | Default |                             Check
-----------+------------------+------+-----------+----------+---------+---------------------------------------------------------------
 rideshare | vehicle_statuses | text |           |          |         | CHECK (VALUE = ANY (ARRAY['draft'::text, 'published'::text]))
(1 row)

Marked As Solved

andatki

andatki

Author of High Performance PostgreSQL for Rails

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!

Where Next?

Popular Pragmatic Bookshelf topics Top

jimschubert
In Chapter 3, the source for index introduces Config on page 31, followed by more code including tests; Config isn’t introduced until pag...
New
johnp
Hi Brian, Looks like the api for tinydb has changed a little. Noticed while working on chapter 7 that the .purge() call to the db throws...
New
mikecargal
Title: Hands-On Rust (Chapter 11: prefab) Just played a couple of amulet-less games. With a bit of debugging, I believe that your can_p...
New
HarryDeveloper
Hi @venkats, It has been mentioned in the description of ‘Supervisory Job’ title that 2 things as mentioned below result in the same eff...
New
rmurray10127
Title: Intuitive Python: docker run… denied error (page 2) Attempted to run the docker command in both CLI and Powershell PS C:\Users\r...
New
brunogirin
When running tox for the first time, I got the following error: ERROR: InterpreterNotFound: python3.10 I realised that I was running ...
New
jonmac
The allprojects block listed on page 245 produces the following error when syncing gradle: “org.gradle.api.GradleScriptException: A prob...
New
EdBorn
Title: Agile Web Development with Rails 7: (page 70) I am running windows 11 pro with rails 7.0.3 and ruby 3.1.2p20 (2022-04-12 revision...
New
dtonhofer
@parrt In the context of Chapter 4.3, the grammar Java.g4, meant to parse Java 6 compilation units, no longer passes ANTLR (currently 4....
New
SlowburnAZ
Getting an error when installing the dependencies at the start of this chapter: could not compile dependency :exla, "mix compile" failed...
New

Other popular topics Top

siddhant3030
I’m thinking of buying a monitor that I can rotate to use as a vertical monitor? Also, I want to know if someone is using it for program...
New
New
AstonJ
Just done a fresh install of macOS Big Sur and on installing Erlang I am getting: asdf install erlang 23.1.2 Configure failed. checking ...
New
AstonJ
Inspired by this post from @Carter, which languages, frameworks or other tech or tools do you think is killing it right now? :upside_down...
New
AstonJ
I ended up cancelling my Moonlander order as I think it’s just going to be a bit too bulky for me. I think the Planck and the Preonic (o...
New
mafinar
Crystal recently reached version 1. I had been following it for awhile but never got to really learn it. Most languages I picked up out o...
New
rustkas
Intensively researching Erlang books and additional resources on it, I have found that the topic of using Regular Expressions is either c...
New
AstonJ
Saw this on TikTok of all places! :lol: Anyone heard of them before? Lite:
New
New
AnfaengerAlex
Hello, I’m a beginner in Android development and I’m facing an issue with my project setup. In my build.gradle.kts file, I have the foll...
New

Sub Categories: