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)
2 23 2

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

sdmoralesma
Title: Web Development with Clojure, Third Edition - migrations/create not working: p159 When I execute the command: user=> (create-...
5 1083 2
New
lirux
Hi Jamis, I think there’s an issue with a test on chapter 6. I own the ebook, version P1.0 Feb. 2019. This test doesn’t pass for me: ...
10 1451 5
New
patoncrispy
I’m new to Rust and am using this book to learn more as well as to feed my interest in game dev. I’ve just finished the flappy dragon exa...
5 1484 3
New
brunogirin
When trying to run tox in parallel as explained on page 151, I got the following error: tox: error: argument -p/–parallel: expected one...
0 1105 1
New
oaklandgit
Hi, I completed chapter 6 but am getting the following error when running: thread 'main' panicked at 'Failed to load texture: IoError(O...
2 1303 3
New
tkhobbes
After some hassle, I was able to finally run bin/setup, now I have started the rails server but I get this error message right when I vis...
0 1460 5
New
redconfetti
Docker-Machine became part of the Docker Toolbox, which was deprecated in 2020, long after Docker Desktop supported Docker Engine nativel...
0 894 0
New
davetron5000
Hello faithful readers! If you have tried to follow along in the book, you are asked to start up the dev environment via dx/build and ar...
3 1150 19
New
dachristenson
I just bought this book to learn about Android development, and I’m already running into a major issue in Ch. 1, p. 20: “Update activity...
0 2059 6
New
roadbike
From page 13: On Python 3.7, you can install the libraries with pip by running these commands inside a Python venv using Visual Studio ...
1 974 3
New

Other popular topics Top

Devtalk
Hello Devtalk World! Please let us know a little about who you are and where you’re from :nerd_face:
476 5781 112
New
AstonJ
What chair do you have while working… and why? Is there a ‘best’ type of chair or working position for developers?
74 4882 41
New
AstonJ
There’s a whole world of custom keycaps out there that I didn’t know existed! Check out all of our Keycaps threads here: https://forum....
15 8183 19
New
AstonJ
This looks like a stunning keycap set :orange_heart: A LEGENDARY KEYBOARD LIVES ON When you bought an Apple Macintosh computer in the e...
14 6073 7
New
AstonJ
Do the test and post your score :nerd_face: :keyboard: If possible, please add info such as the keyboard you’re using, the layout (Qw...
82 6920 31
New
PragmaticBookshelf
Learn different ways of writing concurrent code in Elixir and increase your application's performance, without sacrificing scalability or...
78 4119 24
New
PragmaticBookshelf
Build highly interactive applications without ever leaving Elixir, the way the experts do. Let LiveView take care of performance, scalabi...
61 3882 14
New
AstonJ
Seems like a lot of people caught it - just wondered whether any of you did? As far as I know I didn’t, but it wouldn’t surprise me if I...
190 3839 79
New
PragmaticBookshelf
Develop, deploy, and debug BEAM applications using BEAMOps: a new paradigm that focuses on scalability, fault tolerance, and owning each ...
40 2292 21
New
sir.laksmana_wenk
I’m able to do the “artistic” part of game-development; character designing/modeling, music, environment modeling, etc. However, I don’t...
14 1353 8
New

Sub Categories: