Postgres NOT NULL Constraint

Introduction

By default, columns in PostgreSQL tables can contain NULL values. However, there may be certain cases where you don’t want a column to have any NULL values in it. The Postgres NOT NULL constraint can give you the control you need, ensuring that no NULL values can be inserted into the specified column. In this article, we’ll take a closer look at the NOT NULL constraint and review a few examples of its use.

Prerequisites

Before diving into this tutorial, be sure to install and configure the following for your operating system:

• PostgreSQL

You’ll also need some basic PostgreSQL knowledge in order to follow along with the examples in the article.

What Is NULL and NOT NULL?

In PostgreSQL, the term NULL is used to represent a missing value for a column. Because NULL values appear to be blank, they’re sometimes confused with an “empty” value, but they are not the same at all.

A field containing a NULL value is viewed as a field having no value at all. The value of NULL does not equate to zero or a space.

A NOT NULL constraint strictly enforces that a target column must not be assigned with a NULL value. The constraint is enforced during INSERT and UPDATE operations.

The basic syntax for specifying a not-null constraint during the creation of a table looks like the following:

• First, we call the CREATE TABLE clause, followed by the name of the table.
• We then specify the column name, the data type and the NOT NULL constraint.

Creating NOT NULL Constraint for New Table

In this section, we’ll show you how to create a table with a NOT NULL constraint. Here’s an example to get us started:

Let’s discuss the above statement in a bit more detail:

The statement creates a table named sales, and we applied the NOT NULL constraint against the columns item_id, item_quantity and total_price. Notice that we used the CHECK constraint along with the Postgres NOT NULL constraint, Postgres allows us to use multiple constraints on a target column, and the order in which we use these constraints is not important.

Adding NOT NULL Constraint to an Existing Table

We can also add a NOT NULL constraint to an existing table using the ALTER TABLE statement. The basic syntax is shown below:

Let’s discuss what’s happening in this statement:

• First, we call the ALTER TABLE statement, followed by the name of the table.
• We then alter the target column using the ALTER COLUMN clause, then we specify the name of the column.
• Finally, we set a column constraint using the SET NOT NULL clause.

Next, we’ll look at the following table structure:

We’ll use an ALTER TABLE statement to add a NOT NULL constraint on both the name and last_name columns.

Notice that we can set a NOT NULL constraint on multiple columns in a table.

Conclusion

Although PostgreSQL allows NULL values in tables by default, there may be times when you want to make sure a column has no NULL values in it. Fortunately, the NOT NULL constraint makes it easy to control the data that’s added to columns in a PostgreSQL table, ensuring that no values are NULL. In this article, we showed you how to create a table with the Postgres NOT NULL constraint and how to modify an existing table to add the constraint. With our examples to get you started, you’ll be able to utilize this constraint in your own PostgreSQL database.