This article covers the Postgres CHECK constraint and its usage. In this article, we are going to learn:

1. What are Postgres check constraints?
2. How to create a CHECK constraint in CREATE TABLE statement
3. How to add a CHECK constraint on a column of an existing table
4. How to create a CHECK constraint using the pgAdmin4 tool

Introduction to the Postgres check constraint

Before the query inserts a record in a table, the CHECK constraint evaluates the condition specified in the definition of the CHECK constraint. If the condition evaluates to TRUE, then the values specified in a query will be inserted or updated in the table. If the condition evaluates to FALSE, then the query returns a check condition violation error and terminates the query.

Create check constraint in a new table

We can create a CHECK constraint while creating a new table. For demonstration, I have created a table named tblPurchaseOrder.

The table has one CHECK constraint.

1. The CHECK constraint checks that the value of the order_quantity column must be greater than zero. If a query inserts the record with the order_quantity =0, then it should throw an error.

Now, let us try to add a record with the order_quantity > 0. Run the following query:

insert into tblPurchaseOrder
(orderID, Product_code, Order_Qty, Product_Cost, UserID, Orderd_Date)
values
(1,’PROD0001′,5,500,’nisarg.upadhyay’, ‘2021-05-30’)

As you can see, the record has been added successfully. Let us try to insert a record with order_quantity =0.

insert into tblPurchaseOrder
(orderID, Product_code, Order_Qty, Product_Cost, UserID, Orderd_Date)
values
(2,’PROD0001′,0,10,’nisarg.upadhyay’, ‘2021-05-30’)
The query has returned an error.

• Note:
  1. While creating a new table, if we define a CHECK constraint without specifying the name, the PostgreSQL assigns a name that follows [table_name]_[column_name]_check naming convention
  2. We can specify the name of the constraint in the CREATE TABLE statement. We can specify the constraint name after specifying the CONSTRAINT expression. Following is the pattern:
     Create table tbl_name(col_1 datatype, Col_2 data_type CONSTRAINT constraint_name (Check_condition))

Add constraint using ALTER TABLE statement

We can add a constraint by using ALTER TABLE statement. The syntax to add the constraint is as the following.

ALTER TABLE tbl_name ADD CONSTRAINT constraint_name CHECK (condition)

In the syntax,

1. tbl_name: Specify the table name which contains the column on which you want to add the CHECK CONSTRAINT
2. constraint_name: Specify the desired constraint name. The constraint name must be specified after ADD CONSTRAINT expression
3. Condition: Specify the CHECK condition. The CHECK condition must be specified between the parentheses

We want to add a CHECK constraint on the Product_Cost column of the tblPurchaseOrder table. When we add a record with the Product_Cost <0, the query must return an error. Run the following query to create the constraint.

ALTER TABLE tblPurchaseOrder ADD CONSTRAINT “Check_Product_Cost” CHECK (product_cost > 0)

Now, let us insert a record with Product_Cost < 0.

insert into tblPurchaseOrder
(orderID, Product_code, Order_Qty, Product_Cost, UserID, Orderd_Date)
values
(2,’PROD0001′,50,0,’nisarg.upadhyay’, ‘2021-05-30’)

As you can see in the above image, the query has returned an error.

Create CHECK constraint using pgAdmin4

Now, let us understand how we can create a CHECK constraint using the pgAdmin4 tool. The pgAdmin4 is a PostgreSQL database management tool. When we install PostgreSQL, pgAdmin4 installs automatically.

We want to add a CHECK constraint on the Ordered_date column of the tblPurchaseOrder table. When we insert a record with the ordered_date> 10 days, the query must return an error.

To create a CHECK constraint, launch pgAdmin4. Specify the master password to connect to the PostgreSQL.

You can view the list of the installed servers and objects in the Browser pan. To view the objects, expand Servers 🡪 Expand PostgreSQL13 🡪 Expand Databases.

Right-click on DemoDatabase 🡪 Expand Schemas 🡪 Expand public 🡪 Expand Tables 🡪 Right-click on tables 🡪 Select Properties.

A dialog box to configure the table properties opens. On the dialog box, click on Constraints, and in the sub-menu, click on Check. Click on the (+) sign and then click on the edit row button.

Another submenu opens. You can specify the desired name of the constraint in the name textbox.

Now, we want to create a CHECK constraint on the tblPurchaseOrder table. The CHECK constraint is a combination of two conditions:

1. When the user executes the INSERT statement, the value of Orderd_Date should not be older than ten days. For example, if today is 2021-05-31 and the user tries to insert the value of Orderd_Date as 2021-05-20, the query must return an error
2. The value of the Orderd_Date column must not be greater than the current date and time

To fulfill the above condition, the definition of the CHECK condition must be the following.

CHECK (orderd_date between Current_date-INTEGER ’10’ AND Current_date)

We can specify the definition of the CHECK constraint under the definition sub-menu.

Click on the Save button to create the constraint and close the dialog box. Let us test the constraint by inserting records. First, let us insert the record with the value of Orderd_Date=’2021-05-15.’

insert into tblPurchaseOrder
(orderID, Product_code, Order_Qty, Product_Cost, UserID, Orderd_Date)
values
(4,’PROD0002′,5,500,’nisarg.upadhyay’, ‘2021-05-15’)

Let us insert the record with the value of the Orderd_Date higher than the Current_date.

insert into tblPurchaseOrder
(orderID, Product_code, Order_Qty, Product_Cost, UserID, Orderd_Date)
values
(5,’PROD0002′,1,50,’nisarg.upadhyay’, ‘2021-05-31’)

View the CHECK CONSTRAINTS

To view the list of CHECK constraints created on a table, we can use the following query

select cons.conname as constraint_name,
columnusage.table_schema as table_schema,
columnusage.table_name,
columnusage.column_name
from pg_constraint cons
join pg_namespace namespace on namespace.oid = cons.connamespace
join pg_class class on cons.conrelid = class.oid
left join information_schema.constraint_column_usage columnusage
on cons.conname = columnusage.constraint_name
and namespace.nspname = columnusage.constraint_schema
where contype =’c’
order by cons.conname;

Output:

Alternatively, you can use the pgAdmin4 tool to view the constraints. To do that, expand DemoDatabase 🡪 Expand Schemas 🡪 Expand public 🡪 Expand Tables 🡪 Expand Constraints.

As you can see, three constraints have been created on the tblPurchaseOrder table.

Summary

In this article, we learned about the Postgres check constraint. I have explained how we can create CHECK constraints using CREATE TABLE statement. Also, we learned how we can add a check constraint to an existing table using ALTER TABLE statement.

• Author
• Recent Posts

Nisarg Upadhyay

Nisarg Upadhyay is a SQL Server Database Administrator and Microsoft certified professional who has more than 8 years of experience with SQL Server administration and 2 years with Oracle 10g database administration.

He has expertise in database design, performance tuning, backup and recovery, HA and DR setup, database migrations and upgrades. He has completed the B.Tech from Ganpat University. He can be reached on nisargupadhyay87@outlook.com

Latest posts by Nisarg Upadhyay (see all)

• Different ways to identify and change compatibility levels in SQL Server - July 22, 2024
• Copy SQL Databases between Windows 10 and CentOS using SQL Server data tools - October 19, 2022
• Changing the location of FILESTREAM data files in SQL Database - October 14, 2022