PostgreSQL CHECK Constraints: Syntax and Examples

A PostgreSQL CHECK constraint requires each inserted or updated row to satisfy a Boolean expression. If the expression is FALSE, PostgreSQL rejects the row. If it is TRUE or NULL (unknown), the row passes.

Use CHECK for rules based on the current row, such as nonnegative prices or a valid date range. It is not a way to enforce conditions across other rows or tables. For those rules, use an appropriate constraint such as UNIQUE, FOREIGN KEY, or EXCLUDE. See PostgreSQL’s constraint documentation for the full rules.

PostgreSQL Error 23514: CHECK violation

SQLSTATE 23514 (check_violation) means an inserted or updated row made a CHECK expression evaluate to FALSE. The error message usually identifies the table and constraint; compare the failed row’s values with that constraint’s expression. PostgreSQL lists this SQLSTATE in its error-code appendix.

CHECK constraint syntax

You can add a CHECK in a column definition or as a table constraint:

[CONSTRAINT constraint_name] CHECK (expression)

A column constraint is intended for a rule about that column. Use a table constraint when the expression compares multiple columns.

Create a table with CHECK constraints

This example requires a product name, rejects empty or space-only names, prevents negative prices, and limits a discount to the product’s price:

CREATE TABLE products (
    product_id bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
    name text NOT NULL
        CONSTRAINT products_name_nonblank CHECK (btrim(name) <> ''),
    price numeric(10, 2) NOT NULL
        CONSTRAINT products_price_nonnegative CHECK (price >= 0),
    discount numeric(10, 2) NOT NULL DEFAULT 0,
    CONSTRAINT products_discount_within_price
        CHECK (discount >= 0 AND discount <= price)
);

Here, NOT NULL and CHECK handle separate cases. NOT NULL rejects SQL NULL, while the name check rejects an empty or space-only string. A CHECK by itself would allow NULL because its condition evaluates to unknown; see the PostgreSQL NOT NULL guide.

Insert a row that satisfies the constraints:

INSERT INTO products (name, price, discount)
VALUES ('Keyboard', 59.90, 10.00);

This row is rejected because the discount is greater than the price:

INSERT INTO products (name, price, discount)
VALUES ('Keyboard', 59.90, 70.00);

The name constraint also rejects an empty or space-only value:

INSERT INTO products (name, price, discount)
VALUES ('', 59.90, 10.00);

Add a CHECK to an existing table

Suppose an existing product_reviews table has a rating column that should contain values from 1 through 5. First find any current out-of-range values:

SELECT review_id, rating
FROM product_reviews
WHERE rating < 1 OR rating > 5;

For a small table with no violations, add an enforced constraint directly:

ALTER TABLE product_reviews
ADD CONSTRAINT product_reviews_rating_range
CHECK (rating BETWEEN 1 AND 5);

PostgreSQL checks existing rows before adding the constraint. For a large table, NOT VALID lets you add the rule without the initial scan. The rule still applies to new or updated rows. After correcting old violations, validate the constraint:

ALTER TABLE product_reviews
ADD CONSTRAINT product_reviews_rating_range
CHECK (rating BETWEEN 1 AND 5) NOT VALID;

-- Correct any existing out-of-range ratings before validation.

ALTER TABLE product_reviews
VALIDATE CONSTRAINT product_reviews_rating_range;

If NULL ratings are also invalid, add a NOT NULL constraint; the CHECK expression accepts NULL because it evaluates to unknown. See PostgreSQL’s ALTER TABLE documentation for constraint validation and locking details.

Remove a CHECK constraint

Use the constraint name to remove a rule:

ALTER TABLE products
DROP CONSTRAINT products_discount_within_price;

Dropping a constraint removes the rule for future inserts and updates; it does not change existing values.

Summary

Use CHECK for a Boolean rule about the row being written. A check passes when it evaluates to true or unknown, so use NOT NULL separately when a value is required. For large tables, consider adding a NOT VALID constraint and validating it after existing rows are corrected. Browse the PostgreSQL error troubleshooting index for other SQLSTATE guides.