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
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.
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
Let's try to set a NOT NULL constraint for the
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
gender column of the
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
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.