Share this page: Follow me to be notified of new articles:

Protect your data with Check Constraints in PostgreSQL

Introduction

Check Constraints will ensure that data inserted in a column satisfied a condition.
For example, in a table with a list of "products" you may want to make sure that no product has a negative price.
Checkpoint
Actual picture of the checkpoint system used by PostgreSQL

Checking a number (ex: require a positive price)

The most usefull case of using a Check Constraint is to validate that a price or another type of number is positive.
For example, let's create a table of products checking that the product prive is above 0:
CREATE TEMP TABLE products (
   id serial unique,
   name text,
   price double precision CHECK (price > 0)
);

You can add products like you would do usualy:
gab@test # INSERT INTO products (name, price) VALUES ('1 poud of coffee', 14.55);
INSERT 0 1

If by accident you try to add a product with a negative price, you would get an error message:
gab@gab # INSERT INTO products (name, price) VALUES ('2 pouds of coffee', -28);
ERROR:  new row for relation "products" violates check constraint "products_price_check"
DETAIL:  Failing row contains (2, 2 pouds of coffee, -28).

You can logicaly use the same type of constraints to ensure that numbers are below zero with "CHECK (price < 0)", equal to zero or positive with CHECK (price >= 0)...

Checking a column against another column

We can re-create our table and add a new column "sale_price" to ensure that the price during the sales is at least 50% of the regular product price.
CREATE TEMP TABLE products (
   id serial unique,
   name text,
   price double precision CHECK (price > 0),
   sale_price double precision CHECK (sale_price >= price * 0.50)
);

Let's try to add a few products:
-- We add a product with discount of 30% during sales, the item is valid
gab@test # INSERT INTO products (name, price, sale_price) VALUES ('1 pound of Cofee', 14.00, 9.80);                                              
INSERT 0 1

-- We add a product with discount of 50% during sales, the item is valid (because the constraint is above OR equal to 50%)
gab@test # INSERT INTO products (name, price, sale_price) VALUES ('1 pound of Cofee', 14.00, 7);
INSERT 0 1

-- We add a product with discount of 60% during sales, the item is NOT valid
gab@test # INSERT INTO products (name, price, sale_price) VALUES ('1 pound of Cofee', 14.00, 5.60);
ERROR:  new row for relation "products" violates check constraint "products_check"
DETAIL:  Failing row contains (3, 1 pound of Cofee, 14, 5.60).
Time: 0.475 ms
Last update of this page: March 27, 2017
Please do not copy any of the content of this website without asking me first.