PostgreSQL – CHECK Constraint

In PostgreSQL, the CHECK constraint is primarily used to specify if a value in a column necessarily meets a specific requirement. The CHECK constraint utilizes a Boolean expression to assess the values before performing an insert or update operation to the column. If the values pass the check, PostgreSQL allows the insertion or update of those values into the column. It is primarily used while creating a table.

Syntax: variable_name Data-type CHECK(condition);

Now let’s jump into an example for better understanding.

Example:
We will first create an employee table with specific checks as follows:

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)
);

Now we will first insert data that satisfies the above constraints using the below command:

INSERT INTO employees (
    first_name,
    last_name,
    birth_date,
    joined_date,
    salary
)
VALUES
    (
        'Raju',
        'Kumar',
        '1994-01-01',
        '2015-07-01',
         100000
    );

Now if we check if the data insertion was successful using the below command:

SELECT * FROM employee;

We can see the data inserted as below:

Now we will try to insert data that fails the check as below:

INSERT INTO employees (
    first_name,
    last_name,
    birth_date,
    joined_date,
    salary
)
VALUES
    (
        'Nikhil',
        'Aggarwal',
        '1972-01-01',
        '2015-07-01',
        - 100000
    );

Output:

ERROR:  new row for relation "employees" violates check constraint "employees_salary_check"
DETAIL:  Failing row contains (2, Nikhil, Aggarwal, 1972-01-01, 2015-07-01, -100000)

As we can see the behaviour is exactly what we expected.