August 1, 2023
Summary: in this tutorial, you will learn about the PostgreSQL
CHECK constraints and how to use them to constrain values in columns of a table based on a boolean expression.
CHECK constraint is a kind of constraint that allows you to specify if values in a column must meet a specific requirement.
CHECK constraint uses a Boolean expression to evaluate the values before they are inserted or updated to the column.
Define PostgreSQL CHECK constraint for new tables
Typically, you use the
CHECK constraint at the time of creating the table using the
CREATE TABLE statement.
The following statement defines an
DROP TABLE IF EXISTS employees; CREATE TABLE employees ( id SERIAL PRIMARY KEY, first_name VARCHAR (50), last_name VARCHAR (50), birth_date DATE CHECK (birth_date > '1900-01-01'), joined_date DATE CHECK (joined_date > birth_date), salary numeric CHECK(salary > 0) );
employees table has three
- First, the birth date (
birth_date) of the employee must be greater than
01/01/1900. If you try to insert a birth date before
01/01/1900, you will receive an error message.
- Second, the joined date (
joined_date) must be greater than the birth date (
birth_date). This check will prevent from updating invalid dates in terms of their semantic meanings.
- Third, the salary must be greater than zero, which is obvious.
Let’s try to insert a new row into the
INSERT INTO employees (first_name, last_name, birth_date, joined_date, salary) VALUES ('John', 'Doe', '1972-01-01', '2015-07-01', - 100000);
The statement attempted to insert a negative salary into the
salary column. However, PostgreSQL returned the following error message:
ERROR: new row for relation "employees" violates check constraint "employees_salary_check" DETAIL: Failing row contains (1, John, Doe, 1972-01-01, 2015-07-01, -100000).
The insert failed because of the
CHECK constraint on the
salary column that accepts only positive values.
By default, PostgreSQL gives the
CHECK constraint a name using the following pattern:
For example, the constraint on the salary column has the following constraint name:
However, if you want to assign a
CHECK constraint a specific name, you can specify it after the
CONSTRAINT expression as follows:
column_name data_type CONSTRAINT constraint_name CHECK(...)
See the following example:
... salary numeric CONSTRAINT positive_salary CHECK(salary > 0) ...
Define PostgreSQL CHECK constraints for existing tables
CHECK constraints to existing tables, you use the
ALTER TABLE statement. Suppose, you have an existing table in the database named prices_list
CREATE TABLE prices_list ( id serial PRIMARY KEY, product_id INT NOT NULL, price NUMERIC NOT NULL, discount NUMERIC NOT NULL, valid_from DATE NOT NULL, valid_to DATE NOT NULL );
Now, you can use
ALTER TABLE statement to add the
CHECK constraints to the
prices_list table. The price and discount must be greater than zero and the discount is less than the price. Notice that we use a Boolean expression that contains the
ALTER TABLE prices_list ADD CONSTRAINT price_discount_check CHECK ( price > 0 AND discount >= 0 AND price > discount );
The valid to date (
valid_to) must be greater than or equal to valid from date (
ALTER TABLE prices_list ADD CONSTRAINT valid_range_check CHECK (valid_to >= valid_from);
CHECK constraints are very useful to place additional logic to restrict values that the columns can accept at the database layer. By using the
CHECK constraint, you can make sure that data is updated to the database correctly.
In this tutorial, you have learned how to use PostgreSQL
CHECK constraint to check the values of columns based on a Boolean expression.