Using NOT in Postgres CHECK constraints

April 16, 2021 ( last updated : April 16, 2021 )
postgres

For some reason I could not find good information on this (maybe because it’s obvious). But you can add NOT into your Postgres CHECK CONSTRAINTs; this is particularly useful when you find a nice succinct way to express the negation of the constraint you want.

Consider the following scenario:

You have a table that contains pricing data and that data has both a frequency (monthly, daily, etc) and a value in cents. That table may look something like:

CREATE TABLE pricing_data (
    service_name character varying,
    pricing_frequency character varying,
    pricing_value integer
);

When allowing users to edit this information, we don’t want to require that info when creating the record thus adding a NOT NULL constraint is out of the picture. On the other hand, if a user inputs ONE of the pricing values, you want to be sure that the OTHER value is also present.

A constraint such as this would work

ALTER TABLE pricing_data
ADD CONSTRAINT require_pricing_frequency_and_pricing_value CHECK (
    (num_nulls(pricing_frequncy, pricing_value) == 0)
    OR
    (num_nulls(pricing_frequncy, pricing_value) == 2)
)

But that was a bit verbose and I knew there must be a better way; after messing around a bit, I wrote a constraint that validated if both or neither values are NULL:

ALTER TABLE pricing_data
ADD CONSTRAINT require_pricing_frequency_and_pricing_value CHECK (
    num_nulls(pricing_frequency, pricing_value) = 1
)

Unfortunately, this is the opposite of what we actually want.

After searching the internet and finding nothing helpful (though I did learn about EXCLUDE constraints) I tried simply throwing a NOT in my constraint to see what it would do

ALTER TABLE pricing_data
ADD CONSTRAINT require_pricing_frequency_and_pricing_value CHECK (
    NOT (num_nulls(pricing_frequency, pricing_value) = 1)
)

Lo-and-behold, this did what I wanted and I was able to leverage the DB to validate my data! Huzzah!