# How to Add or Drop NOT NULL Constraints in PostgreSQL

> In PostgreSQL, a table column created with a NOT NULL constraint accepts only non-null values. It can be added when creating a new or altering an existing tabl…

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.

---
[View this page online](https://www.commandprompt.com/education/how-to-add-or-drop-not-null-constraints-in-postgresql/)