Introduction to PostgreSQL CHECK constraints

In PostgreSQL, aCHECK constraint ensures that values in a column or a group of columns meet a specific condition.

A check constraint allows you to enforce data integrity rules at the database level. A check constraint uses a boolean expression to evaluate the values, ensuring that only valid data isinserted orupdated in a table.

Creating CHECK constraints

Typically, you create a check constraint when creating a table using theCREATE TABLE statement:

CREATE TABLE table_name(   column1 datatype,   ...,   CONSTRAINT constraint_name CHECK(condition));

In this syntax:

• First, specify the constraint name after theCONSTRAINT keyword. This is optional. If you omit it, PostgreSQL will automatically generate a name for theCHECK constraint.
• Second, define a condition that must be satisfied for the constraint to be valid.

If theCHECK constraint involves only one column, you can define it as a column constraint like this:

CREATE TABLE table_name(   column1 datatype,   column1 datatype CHECK(condition),   ...,);

By default, PostgreSQL assigns a name to aCHECK constraint using the following format:

{table}_{column}_check

Adding CHECK constraints to tables

To add aCHECK constraint to an existing table, you use theALTER TABLE ... ADD CONSTRAINT statement:

ALTER TABLE table_nameADD CONSTRAINT constraint_nameCHECK (condition);

Removing CHECK constraints

To drop aCHECK constraint, you use theALTER TABLE ... DROP CONSTRAINT statement:

ALTER TABLE table_nameDROP CONSTRAINT constraint_name;

PostgreSQL CHECK constraint examples

Let’s explore some examples of using theCHECK constraints.

1) Defining PostgreSQL CHECK constraint for a new table

First, create a new table calledemployees with someCHECK constraints:

CREATE TABLE employees (  id SERIAL PRIMARY KEY,  first_name VARCHAR (50) NOT NULL,  last_name VARCHAR (50) NOT NULL,  birth_date DATE NOT NULL,  joined_date DATE NOT NULL,  salary numeric CHECK(salary > 0));

In this statement, the employees table has oneCHECK constraint that enforces the values in the salary column greater than zero.

Second, attempt toinsert a new row with a negative salary into theemployees table:

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

Error:

ERROR:  newrow for relation"employees" violatescheck constraint "employees_salary_check"DETAIL:  Failingrow contains (1, John, Doe,1972-01-01,2015-07-01,-100000).

The insert fails because theCHECK constraint on thesalary column accepts only positive values.

2) Adding PostgreSQL CHECK constraints for existing tables

First, use theALTER TABLE ... ADD CONSTRAINT statement to add aCHECK constraint to theemployees table:

ALTER TABLE employeesADD CONSTRAINT joined_date_checkCHECK (joined_date >  birth_date );

TheCHECK constraint ensures that the joined date is later than the birthdate.

Second, attempt to insert a new row into theemployees table with the joined date is earlier than the birth date:

INSERT INTO employees (first_name,last_name, birth_date, joined_date, salary)VALUES ('John','Doe', '1990-01-01', '1989-01-01', 100000);

Output:

ERROR:  newrow for relation"employees" violatescheck constraint "joined_date_check"DETAIL:  Failingrow contains (2, John, Doe,1990-01-01,1989-01-01,100000).

The output indicates that the data violates the check constraint “joined_date_check”.

3) Using functions in CHECK constraints

The following example adds aCHECK constraint to ensure that the first name has at least 3 characters:

ALTER TABLE employeesADD CONSTRAINT first_name_checkCHECK (LENGTH(TRIM(first_name))>= 3);

In this example, we define a condition using theTRIM() andLENGTH() functions:

• First, theTRIM() function removes leading and trailing whitespaces from the first_name.
• Second, theLENGTH() function returns the character length of the result of theTRIM() function.

The whole expressionLENGTH(TRIM(first_name)) >= 3 ensures the first name contains three or more characters.

The following statement will fail because it attempts to insert a row into theemployees table with the first name that has 2 characters:

INSERT INTO employees (first_name,last_name, birth_date, joined_date, salary)VALUES ('Ab','Doe', '1990-01-01', '2008-01-01', 100000);

Error:

ERROR:  newrow for relation"employees" violatescheck constraint "first_name_check"DETAIL:  Failingrow contains (4, Ab, Doe,1990-01-01,2008-01-01,100000).

4) Removing a CHECK constraint example

The following statement removes theCHECK constraintjoined_date_check from theemployees table:

ALTER TABLE employeesDROP CONSTRAINT joined_date_check;

Summary

• Use PostgreSQLCHECK constraint to check the values of columns based on a boolean expression.

Last updated onJanuary 25, 2024

On this page

• Introduction to PostgreSQL CHECK constraints
• PostgreSQL CHECK constraint examples
• Summary