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:
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:
1 2 3 | CREATE TABLE <table_name> ( column_name data_type NOT NULL ); |
- 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:
1 2 3 4 5 6 | 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 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:
1 2 | 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 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:
1 2 3 4 5 | CREATE TABLE person( ID serial PRIMARY KEY, name VARCHAR(50), last_name VARCHAR(50) ); |
We’ll use an ALTER TABLE
statement to add a NOT NULL constraint on both the name
and last_name
columns.
1 2 3 | 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.
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.
Pilot the ObjectRocket Platform Free!
Try Fully-Managed CockroachDB, Elasticsearch, MongoDB, PostgreSQL (Beta) or Redis.
Get Started