In PostgreSQL, the NOT NULL constraint makes sure that the column should accept only non-null values. For instance, if a column is created with a NOT NULL constraint, then attempting to insert a NULL value to that column will throw an error. Moreover, the NOT NULL constraint prevents users from updating NULL values in columns.

Using practical examples, this post will describe the NOT NULL constraint in detail. So let’s start.

How to Create Table’s Columns With NOT NULL Constraint?

To create a column with NOT NULL Constraint, use the following syntax:

CREATE TABLE tab_name(
col_name DATA TYPE NOT NULL
);

The above syntax states that you must specify the NOT NULL constraint after the data type to create a column that doesn't accept null values.

Note: NULL, zero, and empty string are three different things in PostgreSQL. The term NULL refers to unknown/missing information.

Example: How to Declare a Column Using NOT NULL?

Let’s create a sample table named emp_record, that consists of four columns: emp_id, emp_name, emp_age, and emp_email. Suppose we want the users to insert the Non-null value in the emp_email column. To achieve this purpose, we will utilize the NOT NULL Constraint as follows:

CREATE TABLE emp_records(
emp_id INT PRIMARY KEY,
emp_name TEXT,
emp_age INT,
emp_email VARCHAR NOT NULL
);

The above statement creates a new table emp_record with the following details:

• A column named emp_id is created that will accept unique integer values.
• Next, we created two more columns: emp_name to accept string data and emp_age column, which will accept integer values.
• Finally, we declare an emp_email column with VARCHAR data type and NOT NULL constraint, ensuring that the user must insert the NON-NULL values:

The first step is done, i.e., the emp_record table has been created. Let’s insert some data into the emp_record table to understand the working of the NOT NULL constraint:

INSERT INTO emp_record(emp_id, emp_name, emp_age, emp_email)
VALUES (1, 'JOHN', 24, NULL);

The output snippet makes this concept crystal clear, i.e., you can’t insert NULL values to the column created with a NOT NULL constraint.

Let’s insert a couple of non-null values into the selected table:

INSERT INTO emp_record(emp_id, emp_name, emp_age, emp_email)
VALUES(1, 'JOHN', 24, 'john@xyz.com'),
(2, 'JOE', 27, 'joe@abc.com'),
(3, 'MIKE', 28, 'mike@xyz.com')
;

This is how the NOT NULL constraint works in PostgreSQL.

How Do I Add a NOT NULL Constraint to an Existing Table's Column?

Use the below syntax to set a NOT NULL constraint in an existing table’s column:

ALTER TABLE tab_name
ALTER COLUMN col_1_name SET NOT NULL;

This way, a NOT NULL constraint can be set to an existing column using the ALTER COLUMN command and the SET clause. Use the comma-separated syntax to set the NOT NULL constraint to several columns:

ALTER TABLE tab_name
ALTER COLUMN col_1_name SET NOT NULL,
ALTER COLUMN col_2_name SET NOT NULL,
…,
ALTER COLUMN col_N_name SET NOT NULL;

Example: How to Update an Existing Column With NOT NULL Constraint?

Let’s add the NOT NULL constraint to the emp_name column using the below query:

ALTER TABLE emp_record
ALTER COLUMN emp_name SET NOT NULL;

Now, the emp_name column will accept only the non-null values:

INSERT INTO emp_record(emp_id, emp_name, emp_age, emp_email)
VALUES (4, NULL, 23, 'abx@xyz.com');

The output snippet authenticates the working of the NOT NULL constraint.

CHECK Constraint: An Alternate Approach For the NOT NULL Constraint

The CHECK constraint can be used as an alternative to the NOT NULL constraint. You need to follow the below syntax to avail the functionality of the NOT NULL Constraint using the CHECK constraint:

CHECK(col_name IS NOT NULL);

Example: How to Use CHECK Constraint as an Alternative to NOT NULL Constraint?

Let’s create a new table named std_record with three columns: std_id, std_name, std_email. Suppose we want the student to enter a non-null value into the std_email column. We will achieve this purpose using the CHECK Constraint as follows:

CREATE TABLE std_record (
std_id INT PRIMARY KEY,
std_name TEXT,
std_email VARCHAR(30),
CONSTRAINT std_email CHECK (
NOT (std_email IS NULL OR std_email = '' )
)
);

Now, attempting to insert a null value to the std_email column will throw an error:

INSERT INTO std_record(std_id, std_name, std_email)
VALUES (1, 'Joe', NULL);

In this way, the CHECK constraint can be used as an alternative to the NOT NULL constraint.

Conclusion

In PostgreSQL, the NOT NULL constraint makes sure that the column should accept only non-null values. Occasionally, the CHECK constraint can be used as an alternative to the NOT NULL constraint. This write-up demonstrates various use cases of the NOT NULL constraint using practical examples.