How to Set a NOT NULL Column into NULL in PostgreSQL?

PostgreSQL is an open-source relational database management system in short RDBMS. It is commonly known for its reliability and vast feature set. We can clearly state that it is one of the most powerful RDBMS available. We often create some columns with NOT NULL constraints but later on, there will be some possibility that we have to add some NULL values to it. There may be some possible reasons like data cleanup or temporary data manipulation-related tasks. In such cases, we need to remove the NOT NULL constraints from our column.

In this article, we are going to explore a possible way through which we can easily remove and add NOT NULL constraints to our column. We will explore various examples with proper explanations.

How to Set a NOT NULL Column into NULL in PostgreSQL

Setting up a NOT NULL column into NULL is a simple task. We can easily achieve this task with ALTER and DROP statements. ALTER statement is usually used to change the structure of the table and DROP statement is used to remove existing database objects. Together we can use them to achieve our end goal i.e. “Setting up a NOT NULL column into NULL“.

Syntax:

ALTER TABLE table_name
ALTER COLUMN column_name DROP NOT NULL;

Creating a Table in Our Database

In this, we will create a table with certain columns. We will also create a column with a NOT NULL constraint.

Table Name: ‘geeksforgeeks’

Query:

CREATE TABLE geeksforgeeks(
    user_id INT PRIMARY KEY,
    username VARCHAR(255),
    contest_rank INT NOT NULL,
    contest_score INT
);

After executing the above query, a table will be created with the above said columns in our database. Now let’s add some data to our table and display them.

Query:

--adding data to our table
INSERT INTO geeksforgeeks (user_id, username, contest_rank, contest_score)
VALUES (108, 'Vishu', 01, 100);
INSERT INTO geeksforgeeks (user_id, username, contest_rank, contest_score)
VALUES (109, 'Ayush', 02, 90);
INSERT INTO geeksforgeeks (user_id, username, contest_rank, contest_score)
VALUES (110, 'Neeraj', 03, 85);
INSERT INTO geeksforgeeks (user_id, username, contest_rank, contest_score)
VALUES (111, 'Sumit', 04, 80);

--displaying the data
SELECT * from geeksforgeeks;

Output:

table – geeksforgeeks

Explanation: In the above table, we have created our column ‘contest_rank’ with let’s null constraint. Therefore it will not accept any Null data.

Now, let’s try to insert some NULL values to our column with NOT NULL constraint.

Query:

INSERT INTO geeksforgeeks (user_id, username, contest_rank, contest_score)
VALUES (112, 'Vivek', NULL, 70);

Output:

Adding NULL value to a NOT NULL constraint.

Explanation: As we have previously discussed we cannot add any NULL values to a column with NOT NULL constraint. In the above image, we can , see that we have attempted to add a NULL value in our ‘contest_rank’ column. Therefore it is throwing us an error.

UPDATING NOT NULL Column into NULL

In this, let’s we are going to remove NOT NULL constraint from our table and update that column value to NULL.

Query:

ALTER TABLE geeksforgeeks 
ALTER COLUMN contest_rank DROP NOT NULL;

After executing this query, NOT NULL constraint is removed from our column contest_rank. Now let’s update the column value to NULL.

Query:

UPDATE geeksforgeeks
SET contest_rank = NULL;

Output:

Setting contest_rank column to NULL

Explanation: In the above image, we can see that we have updated all the values of ‘contest_rank’ column to NULL. As we have seen previously, contest_rank column is not accepting any NULL values but after removing the NOT NULL constraint, we can ,see that it started to accept NULL values.

Adding NOT NULL Constraint to a Column in PostgreSQL

In this, we will add back our NOT NULL constraint to our column ‘contest_rank’. Previously we have removed it from our column but in this topic, we will see how we can add it back to our column.

Query:

ALTER TABLE geeksforgeeks 
ALTER COLUMN contest_rank SET NOT NULL;

The query is nearly same as the previous one but the difference is that instead of DROP command we have used SET.

NOTE: Before setting up a column with NOT NULL constraint, make sure your column do not contains any NULL values. If it does, it will throw you an error.

Conclusion

Overall, to set up a NOT NULL column into NULL, we need to remove the NOT NULL constraint from the column. We can achieve this task easily with the help of ALTER and DROP statement. We have covered how we can remove and add back a NOT NULL constraint. We have covered each topic with clear explanation. Now you have a good understanding of removing a NOT NULL constraint. Now you can write queries related to it and get the desired result.