Postgres NOT NULL Constraint
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.
Before diving into this tutorial, be sure to install and configure the following for your operating system:
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:
CREATE TABLE <table_name> (
column_name data_type NOT NULL
- First, we call the
CREATE TABLEclause, followed by the name of the table.
- We then specify the column name, the data type and the
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:
CREATE TABLE sales(
id SERIAL PRIMARY KEY,
item_id INTEGER NOT NULL,
item_quantity INTEGER NOT NULL CHECK(item_quantity > 0),
total_price INTEGER NOT NULL CHECK(total_price > 0);
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
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:
ALTER TABLE name_of_table
ALTER COLUMN name_of_column SET NOT NULL;
Let’s discuss what’s happening in this statement:
- First, we call the
ALTER TABLEstatement, followed by the name of the table.
- We then alter the target column using the
ALTER COLUMNclause, then we specify the name of the column.
- Finally, we set a column constraint using the
SET NOT NULLclause.
Next, we’ll look at the following table structure:
CREATE TABLE person(
ID serial PRIMARY KEY,
We’ll use an
ALTER TABLE statement to add a NOT NULL constraint on both the
ALTER TABLE person
ALTER COLUMN name SET NOT NULL,
ALTER COLUMN last_name NOT NULL;
Notice that we can set a NOT NULL constraint on multiple columns in a table.
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.
Pilot the ObjectRocket Platform Free!
Try Fully-Managed CockroachDB, Elasticsearch, MongoDB, PostgreSQL (Beta) or Redis.Get Started