CHECK constraints in PostgreSQL allow us to specify boolean conditions for inserting or updating values in one or more columns. While updating or inserting, the values that don’t satisfy the specified criteria/condition will be rejected. In Postgres, the CHECK constraints are beneficial for adding additional logic or restrictions at the database layer.

The following concepts of CHECK constraint will be covered in this post:

• How to Define/Add CHECK Constraints While Creating a Table in Postgres?
• Adding CHECK Constraint to an Existing Table

So let’s learn all these concepts one-by-one through practical examples.

How to Define/Add CHECK Constraints While Creating a Table in Postgres?

The CHECK constraint is normally defined at the time of table creation. The syntax of defining a CHECK constraint will be as follows:

CREATE TABLE tab_name(
col_name DATA TYPE CHECK(CONDITION)
);

Example #1: How Does CHECK Constraint Work in Postgres?

The below-given query will create a book_info table with four columns: book_name, book_category, published_date, and book_price:

CREATE TABLE book_info (
book_name VARCHAR (50),
book_category TEXT, 
published_date DATE CHECK(published_date > '2000-01-01'),
book_price INT
);

For the published_date column, a condition is specified using the CHECK constraint. The specified condition states that the book published_date must be greater than ‘2000-01-01’:

Let’s insert a record into the book_info table using the following command:

INSERT INTO book_info(book_name, book_category, published_date)
VALUES('Introduction to Postgres', 'Database', '1999-06-01', 500);

The output verified that an error occurred on violating the CHECK constraint.

Adding CHECK Constraint to an Existing Table

ALTER TABLE can be used to add CHECK constraints to an existing table:

ALTER TABLE tab_name
ADD CONSTRAINT check_constraint_name 
CHECK (Condition);

Example: How Does the CHECK Constraint Work on an Already Existing Table?

Suppose we want to add the CHECK constraint on the book_price column. For that purpose, we will use the CHECK constraint as follows:

ALTER TABLE book_info
ADD CONSTRAINT book_price CHECK(book_price <= 1000);

Now, the book_price column will not accept those value that exceeds the book_price 1000:

INSERT INTO book_info(book_name, book_category, published_date, book_price)
VALUES('Introduction to Postgres', 'Database', '2008-06-01', 1500);

The output shows that the value entered for the book_price column violates the condition specified in the CHECK constraint. Let’s insert a record that satisfies the CHECK constraint for both publised_date and book_price columns:

INSERT INTO book_info(book_name, book_category, published_date, book_price)
VALUES('Introduction to Postgres', 'Database', '2018-06-01', 500);

The output shows that if the values to be inserted satisfy the CHECK constraint, the data will be inserted into the targeted table.

Conclusion

CHECK constraints in PostgreSQL allow us to specify boolean conditions for inserting or updating values in one or more columns. Values that meet the specified criteria/condition will be inserted/updated into PostgreSQL, while those that do not will be rejected. This post explained the usage of the CHECK constraint through examples.