PostgreSQL NOT NULL Constraint

In RDBMS, the NULL represents missing value or empty value. It is not the same as an empty string. Use the NOT NULL constraint on a column to restrict the NULL value in the column.

The NOT NULL constraint can be declared with an individual column while creating a table or altering a table.

Define NOT NULL Constraint while Creating a Table

The following declares NOT NULL columns in the CREATE TABLE statement. It will create the employee table with NOT NULL constraints on the first_name and last_name columns.

Example: Define NOT NULL Columns

CREATE TABLE employee(
    emp_id INT,
    first_name VARCHAR(50) NOT NULL,
    last_name VARCHAR(50) NOT NULL,
    gender 	CHAR(1),
    birthdate DATE,
    email  VARCHAR(100),
    salary INT);

If a column has a NOT NULL constraint defined on it then any attempt to insert or update the NULL value to that column will not be allowed and will raise an error.

In psql, the NOT NULL constraints are marked by NOT NULL against the columns, as shown below.

Adding NOT NULL constraint on Existing Table

The NOT NULL constraint can be added to one or more column/s of an existing table using ALTER TABLE ALTER COLUMN statement.

Consider we have employee table as bellow

The following will set the gender column of the employee table as NOT NULL. As you can see the gender column is populated for all data in a table, so we can set that column as NOT NULL.

Example: Add NOT NULL Constraint in Existing Table

ALTER TABLE employee
ALTER COLUMN gender SET NOT NULL;

If a column already contains at least one NULL value in a column then trying to add a NOT NULL constraint will raise an error. For example, the employee table above already contains null values in the email_id column. Let's try to set a NOT NULL constraint for the email_id column.

ALTER TABLE employee
ALTER COLUMN email SET NOT NULL;

The above query will display the following result in pgAdmin.

Thus, you cannot alter a column and add a NOT NULL constraint if a column already contains null values. In this case, you need to update data in the email_id column and then set it as a NOT NULL column.

Create NOT NULL Constraint on Multiple Columns

The NOT NULL constraint can be defined on multiple columns by using ALTER COLUMN clause multiple times along with ALTER TABLE statement.

The following add NOT NULL constraints on the last_name and gender column of the employee table:

Example: Define Multiple NOT NULL Constraint

ALTER TABLE employee
ALTER COLUMN last_name SET NOT NULL
ALTER COLUMN gender SET NOT NULL;

Drop NOT NULL Constraints

To remove the NOT NULL constraint, use the DROP NOT NULL clause along with ALTER TABLE ALTER COLUMN statement.

The following removes the NOT NULL constraint on the gender column.

Example: Delete NOT NULL Constraint

ALTER TABLE employee
ALTER COLUMN gender DROP NOT NULL;

Set NOT NULL Columns using pgAdmin

You can set NOT NULL columns using pgAdmin while creating a new table or on existing tables.

Expand your databases node in the left pane and then expand Schemas -> public -> Tables nodes. Right click on the table where you want to set NOT NULL columns and select 'Properties'. In the popup, go to Columns tab where you can click on 'Yes' or 'No' switches in the NOT NULL? column against each column of a table, as shown below.