PostgreSQL CHECK Constraint

Summary: in this tutorial, you’ll learn how to use PostgreSQL CHECK constraint to ensure values in table columns meet a condition.

Introduction to PostgreSQL CHECK constraint

PostgreSQL CHECK constraints maintain data integrity by ensuring that the value in a column must satisfy a Boolean expression.

When a column has a CHECK constraint, and you attempt to insert or update a value that causes the Boolean expression to be false, PostgreSQL issues a constraint violation and rejects the changes.

CHECK constraints can be helpful to enforce data integrity rules at the database level. They prevent invalid data from being inserted or update in table columns.

For example, you can use the CHECK constraint to prevent negative values from being inserted into the price column of the products table.

Here’s the syntax of the CHECK constraint:

CREATE TABLE table_name (
    column1 data_type CONSTRAINT constraint_name CHECK(boolean_expression),
    ...

);Code language: PostgreSQL SQL dialect and PL/pgSQL (pgsql)

In this syntax:

First, add a CHECK constraint to the table column.
Second, provide a constraint name after the CONSTRAINT keyword. This constraint name will show up in the error if a constraint violation error occurs. If you don’t provide the constraint name, PostgreSQL will implicitly generate one for the constraint.
Third, place a Boolean expression inside parentheses () after the CHECK keyword to check values being inserted or updated in the column. The Boolean expression can reference columns of the same table, not other tables.

Here’s the more concise syntax that does not use the constraint name:

CREATE TABLE table_name (
    column1 data_type CHECK(boolean_expression),
    ...
);Code language: PostgreSQL SQL dialect and PL/pgSQL (pgsql)

This CHECK constraint is a column constraint because it attaches to a particular column (column1).

It’s worth noting that you can apply multiple constraints to the same column. For example, you can use both NOT NULL and CHECK constraints for the same column:

CREATE TABLE table_name (
    column1 data_type NOT NULL CHECK(boolean_expression),
    ...
);Code language: PostgreSQL SQL dialect and PL/pgSQL (pgsql)

Alternatively, you can define the CHECK constraint as a table constraint:

CREATE TABLE table_name (
    column1 data_type NOT NULL,
    ...,
   CHECK(boolean_expression)
);Code language: PostgreSQL SQL dialect and PL/pgSQL (pgsql)

In this syntax, you define the CHECK constraint after the column list. This syntax is handy when referencing multiple table columns in the Boolean expression.

Basic PostgreSQL CHECK constraint example

First, create a table called items with a CHECK constraint to ensure that the price is greater than or equal to 0:

CREATE TABLE products (
  product_id INT GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
  name VARCHAR(255) NOT NULL,
  price DEC(11, 2) NOT NULL CONSTRAINT positive_price CHECK (price >= 0),
  discounted_price DEC(11, 2) NOT NULL DEFAULT 0
);Code language: PostgreSQL SQL dialect and PL/pgSQL (pgsql)

Next, attempt to insert a row with a negative price into the products table:

INSERT INTO
  products (name, price)
VALUES
  ('iPhone Pro 15', -1299.99);Code language: PostgreSQL SQL dialect and PL/pgSQL (pgsql)

PostgreSQL issued the following error:

new row for relation "products" violates check constraint "positive_price"Code language: plaintext (plaintext)

Then, insert a new row with a valid price:

INSERT INTO
  products (name, price)
VALUES
  ('iPhone Pro 15', 1299.99) RETURNING *;Code language: PostgreSQL SQL dialect and PL/pgSQL (pgsql)

Output:

 product_id |     name      |  price  | discounted_price
------------+---------------+---------+------------------
          2 | iPhone Pro 15 | 1299.99 |             0.00

After that, update the price to an invalid one:

UPDATE products
SET
  price = -899.99
WHERE
  product_id = 2;Code language: PostgreSQL SQL dialect and PL/pgSQL (pgsql)

PostgreSQL issued the same check constraint violation:

new row for relation "products" violates check constraint "positive_price"Code language: plaintext (plaintext)

Finally, update the price to a valid one:

UPDATE products
SET
  price = 999.99
WHERE
  product_id = 2 
RETURNING *;Code language: PostgreSQL SQL dialect and PL/pgSQL (pgsql)

Output:

 product_id |     name      | price  | discounted_price
------------+---------------+--------+------------------
          2 | iPhone Pro 15 | 999.99 |             0.00

The output indicates that the statement updated the product price successfully.

Adding CHECK constraints to tables

You use ALTER TABLE ... ADD CONSTRAINT statement to add a CHECK constraint to an existing table. Here’s the syntax of the statement:

ALTER TABLE table_name 
ADD CONSTRAINT constraint_name 
CHECK (boolean_expression)Code language: PostgreSQL SQL dialect and PL/pgSQL (pgsql)

For example, the following statement adds a CHECK constraint to check if the discounted price is less than price:

ALTER TABLE products
ADD CONSTRAINT discounted_price_check
CHECK (discounted_price < price AND discounted_price > 0);Code language: PostgreSQL SQL dialect and PL/pgSQL (pgsql)

PostgreSQL issues an error because some row in the table violates the new CHECK constraint:

check constraint "discounted_price_check" of relation "products" is violated by some rowCode language: plaintext (plaintext)

The reason is that row id 2 has a discounted price of 0, which violates the new CHECK constraint.

To fix this, you can update the discounted price of the existing rows to make it valid for the new CHECK constraint:

UPDATE products
SET discounted_price = price * 0.9
RETURNING *;Code language: PostgreSQL SQL dialect and PL/pgSQL (pgsql)

Output:

 product_id |     name      | price  | discounted_price
------------+---------------+--------+------------------
          2 | iPhone Pro 15 | 999.99 |           899.99

And then add the CHECK constraint to the products table again:

ALTER TABLE products
ADD CONSTRAINT discounted_price_check
CHECK (discounted_price < price AND discounted_price > 0);Code language: PostgreSQL SQL dialect and PL/pgSQL (pgsql)

If you attempt to insert a row with the discounted price higher or equal to the price, PostgreSQL will issue a check violation. For example:

INSERT INTO products(name, price, discounted_price)
VALUES('iPhone Pro 15', 1299.99, 1399.99);Code language: PostgreSQL SQL dialect and PL/pgSQL (pgsql)

Error:

ERROR: new row for relation "products" violates check constraint "discounted_price_check"Code language: plaintext (plaintext)

Removing CHECK constraints from tables

To remove a CHECK constraint from a table, you use the ALTER TABLE ... DROP CONSTRAINT statement:

ALTER TABLE table_name
DROP CONSTRAINT IF EXISTS constraint_name;Code language: PostgreSQL SQL dialect and PL/pgSQL (pgsql)

In this syntax:

First, specify the table name from which you want to remove the CHECK constraint.
Second, provide the constraint name in the DROP CONSTRAINT clause to remove it.
Third, use the optional IF EXISTS option to prevent an error from attempting to remove a non-existing CHECK constraint.

For example, the following statement drops the discounted_price CHECK constraint from the products table:

ALTER TABLE products
DROP CONSTRAINT IF EXISTS discounted_price_check;Code language: PostgreSQL SQL dialect and PL/pgSQL (pgsql)

Summary

A CHECK constraint adds a validation logic to one or more columns in a table to ensure data integrity.
Use the CONSTRAINT ... CHECK to define a CHECK constraint for a table.
Use the ALTER TABLE ... ADD CONSTRAINT statement to add a CHECK constraint to a table.
Use the ALTER TABLE ... DROP CONSTRAINT statement to remove a CHECK constraint from a table.