In PostgreSQL, the constraints are used to apply some rules on the table’s column. In Postgres, the NOT NULL constraint prevents NULL entries from being inserted into a column. In simple terms, the table columns declared with a NOT NULL constraint take only non-null entries. In Postgres, the NOT NULL constraint can be added while creating a new or altering/modifying an existing table.

In this article, we will show you how to add or drop a NOT NULL constraint in Postgres. For this purpose, the below-listed concepts will be covered in this write-up:

- Adding NOT NULL Constraint to New Table
- Adding NOT NULL Constraint to Existing Table
- Dropping NOT NULL Constraint From a Table

So, let’s begin!

Adding NOT NULL Constraint to New Table

CREATE TABLE statement allows you to add the NOT NULL constraint to any column while table creation. To add a NOT NULL constraint to a table’s column, you need to follow the below syntax:

col_name data_type NOT NULL;

The above snippet shows that to add a not-null constraint, write the column name followed by the data type and then specify the NOT NULL constraint.

Example: How to Add a NOT NULL Constraint During Table Creation?

Let’s learn how to create a NOT NULL column during table creation:

CREATE TABLE student_information(
first_name TEXT NOT NULL,
last_name TEXT,
age INT NOT NULL
);

In the above snippet, the NOT NULL constraint is added to the "first_name" and "age" columns:

The “student_information” table has been created with three columns. Execute the “INSERT INTO” statement to insert some records into the student_information table:

INSERT INTO student_information(first_name, last_name, age)
VALUES ('Joe', 'Smith', 23),
('Tim', 'Root', 25);

Execute the “SELECT *” command to verify the newly inserted data:

SELECT * FROM student_information;

The output indicates that two records have been inserted into the student_information table successfully. Let’s insert one more record into the student_information table:

INSERT INTO student_information(first_name, last_name, age)
VALUES ('Joe', 'Ambrose', NULL);

The output snippet shows that an error occurred when we tried to insert a null value into a column that is declared with a NOT NULL constraint.

Adding NOT NULL Constraint to Existing Table

ALTER TABLE statement with ALTER COLUMN clause is used in Postgres to add NOT NULL constraints to the columns of any existing table:

ALTER TABLE tab_name
ALTER COLUMN clm_name SET NOT NULL;

Example: How Do I Add NOT NULL Constraint to an Existing Table in Postgres?

The previous example shows that the “last_name” column of the student_information table is created without a NOT NULL constraint. Hence, you can insert NULL values into that column. For instance, the “last_name” column needs to be altered, i.e., we want to add the NOT NULL constraint on that particular column. For this purpose, we will use the ALTER TABLE command as follows:

ALTER TABLE student_information
ALTER COLUMN last_name SET NOT NULL;

The output snippet verifies that the table has been altered successfully. Now the last_name column would not accept the null values:

INSERT INTO student_information(first_name, last_name, age)
VALUES ('Joe', NULL, 26);

The output proves that a null value can’t be inserted into a column declared with a NOT NULL constraint.

Dropping NOT NULL Constraint From a Table

A NOT NULL constraint can be dropped from a column using the ALTER TABLE command alongside the ALTER COLUMN clause:

ALTER TABLE tbl_name
ALTER COLUMN col_name DROP NOT NULL;

Example: How Do I Drop a NOT NULL Constraint From a Postgres Table?

We will use the ALTER TABLE command to remove the NOT NULL constraint from the "last_name" column:

ALTER TABLE student_information
ALTER COLUMN last_name DROP NOT NULL;

The student_information table has been altered successfully. Now last_name column can accept the null values, as shown in the below snippet:

INSERT INTO student_information(first_name, last_name, age)
VALUES ('Joe', NULL, 26);

Execute the “SELECT *” command to see the table’s data:

SELECT * FROM student_information;

The output shows that a NULL value has been inserted into the last_name column. It proves that the NOT NULL constraint has been dropped successfully.

Conclusion

In PostgreSQL, a table column created with a NOT NULL constraint accepts only non-null values. The NOT NULL constraint can be added when creating a new or altering/modifying an existing table. A NOT NULL constraint can be dropped from a column using the ALTER TABLE command alongside the ALTER COLUMN clause. This Postgres blog has presented a detailed overview of the NOT NULL constraint using practical examples.