PostgreSQL CHECK Constraints

Any application requires the correctness of the data. For example, the user’s age must be greater than zero, the user’s login name must not contain spaces, the user’s password must meet a certain complexity, and so on.

For these requirements, although we can validate the data entered by the user in the application interface, but this cannot replace the data validation at the database level. This can increase the security of the application.

PostgreSQL provides CHECK constraints to ensure that the data written to the table meets your requirements. Data that does not meet the CHECK constraints will be rejected by PostgreSQL.

PostgreSQL CHECK syntax

This is the syntax of PostgreSQL CHECK constraints:

[CONSTRAINT constraint_name]
CHECK(expr)

Explanation:

• The constraint_name is the name of the constraint. CONSTRAINT constraint_name is optional, use this clause only if you need to specify a constraint name.
• The expr is a boolean expression. If the expression evaluates to true, PostgreSQL allows the input to be written to the table, otherwise PostgreSQL denies writing the data.

You can use CHECK constraints for columns or tables. If you use a CHECK constraint for a column, the CHECK expression can only use this column. If you use CHECK constraints for a table, the CHECK expression can use all columns of the table.

Here’s an example, the value of the age column need to be greater than 0, there are three different methods to add this constraint as follows:

• Add a CHECK constraint for the column

  CREATE TABLE users (
      id INTEGER PRIMARY KEY,
      age INTEGER NOT NULL CHECK(age > 0)
  );
  

• Add a CHECK constraint for the table

  CREATE TABLE users (
      id INTEGER PRIMARY KEY,
      age INTEGER NOT NULL,
      CONSTRAINT users_age_check CHECK(age > 0)
  );
  

• Add CHECK Constraint by ALTER TABLE

  ALTER TABLE users
  ADD CONSTRAINT users_age_check CHECK(age > 0);
  

PostgreSQL CHECK Constraint Example

Through the following examples, you will easily understand the usages of PostgreSQL CHECK constraints.

Suppose, you need a users table store the user’s name, login name, password, and need to meet the following requirements:

1. The user name cannot be empty.
2. The login name must contain at least 4 characters.
3. The length of the password should be no less than 8 characters.
4. The password cannot be the same as the login name.

Note that in real applications you should not store the clear text of the password in the database, it is not secure.

Create a table named users using the following CREATE TABLE statement:

CREATE TABLE users (
    id SERIAL PRIMARY KEY,
    name VARCHAR(45) NOT NULL,
    login_name VARCHAR(45) NOT NULL CHECK(length(login_name) >= 4),
    password VARCHAR(45) NOT NULL CHECK(length(password) >= 8),
    CHECK(login_name <> password)
);

Here, there are 3 CHECK constraints in the CREATE TABLE statement:

1. In the login_name column definition, the CHECK(length(login_name) >= 4) ensures that the length of the login name is not less than 4.
2. In the password column definition, the CHECK(length(password) >= 8) ensures that the length of the login name is not less than 8.
3. The constraint CHECK(login_name <> password) on the table ensure that password cannot be the same as the login name.

View constraints on the users table with the following \d command :

\d users

                                    Table "public.users"
  Column   |         Type          | Collation | Nullable |              Default
------------+-----------------------+-----------+----------+-----------------------------------
id         | integer               |           | not null | nextval('users_id_seq'::regclass)
name       | character varying(45) |           | not null |
login_name | character varying(45) |           | not null |
password   | character varying(45) |           | not null |
Indexes:
   "users_pkey" PRIMARY KEY, btree (id)
Check constraints:
   "users_check" CHECK (login_name::text <> password::text)
   "users_login_name_check" CHECK (length(login_name::text) >= 4)
   "users_password_check" CHECK (length(password::text) >= 8)

As you can see in the output above, there are 3 CHECK constraints in the users table. Constraint names are all generated by PostgreSQL.

Note that NOT NULL is also a constraint.

To verify that the CHECK constraint for login column is in effect, try inserting a row using the following INSERT statement:

INSERT INTO users (name, login_name, password)
VALUES ('Tim', 'tim', 'timisok');

ERROR:  new row for relation "users" violates check constraint "users_login_name_check"
DETAIL:  Failing row contains (1, Tim, tim, timisok).

Since the length of tim is less than 4, PostgreSQL gives the above error.

To verify that the CHECK constraint for password column is in effect, try inserting a row using the following INSERT statement:

INSERT INTO users (name, login_name, password)
VALUES ('Tim', 'tim1', 'timisok');

ERROR:  new row for relation "users" violates check constraint "users_password_check"
DETAIL:  Failing row contains (2, Tim, tim1, timisok).

Since the length of timisok is less than 8, PostgreSQL gives the above error.

To verify that the CHECK constraint CHECK(login_name <> password) is in effect, try inserting a row using the following INSERT statement:

INSERT INTO users (name, login_name, password)
VALUES ('Tim', 'timisgood', 'timisgood');

ERROR:  new row for relation "users" violates check constraint "users_check"
DETAIL:  Failing row contains (3, Tim, timisgood, timisgood).

Since the login name and password given in the above statement are both timisgood, PostgreSQL gives the above error.

Use the following statement to insert a row that meets the CHECK constraints.

INSERT INTO users (name, login_name, password)
VALUES ('Tim', 'hitim', 'timisgood');

The row was successfully inserted into the users table.

The CHECK constraints also apply to UPDATE statements, such as:

UPDATE users
SET login_name = 'tim'
WHERE name = 'Tim';

ERROR:  new row for relation "users" violates check constraint "users_login_name_check"
DETAIL:  Failing row contains (4, Tim, tim, timisgood).

Conclusion

PostgreSQL provides CHECK constraints to ensure that the rows stored in the table meets your requirements. The rows that does not meet the CHECK constraints is rejected.

You can specify CHECK constraints for a column or a table.