How to Set a NOT NULL Column into NULL in PostgreSQL?
Last Updated :
12 Mar, 2024
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 - geeksforgeeksExplanation: 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 NULLExplanation: 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.
Similar Reads
How to Alter a Column from Null to Not Null in SQL Server
In SQL Server, columns are defined with specific constraints, one of which is the nullability of the column whether or not it can hold NULL values. As a database evolves, this setting may need to be changed particularly to ensure that certain data fields are always populated. Altering a column from
4 min read
How to Set a Column Value to Null in PL/SQL?
In PL/SQL, setting a column value to NULL is a common requirement when working with databases. Understanding how to set column values to NULL is essential for database developers and administrators. In this article, we will look into the concept of setting a column value to NULL in PL/SQL, covering
4 min read
How to Set a Column Value to Null in SQL?
You can set a column value to NULL using the SQL UPDATE statement. Through the UPDATE statement, existing records in a table can be changed. The fundamental syntax to set a column value to NULL is as follows. Syntax: UPDATE table_name set column_name=NULL WHERE Conditions; table_name: The name of th
2 min read
How to Filter Rows Without Null in a Column in SQL?
Here we will see, how to filter rows without null in a column of an MS SQL Server's database table with the help of a SQL query using IS NOT NULL operator. For the purpose of demonstration, we will be creating a demo_orders table in a database called âgeeksâ. Creating the Database: Use the below SQL
2 min read
How to Set a Column Value to NULL in SQL Server
In the world of database management, SQL Server is a leading and extensively utilized system. A fundamental task within SQL Server is manipulating data within tables, and setting a column value to NULL is a common operation. Whether it's for maintaining data integrity, performing updates, or meeting
4 min read
How to Set a Column Value to NULL in SQLite?
SQLite is a lightweight and self-contained relational database management system in short RDBMS. Its has a server-less architecture which makes it a better option for small desktop and mobile applications. It also requires very low configuration which eventually helps the developer to integrate it i
4 min read
How to Add an Identity to an Existing Column in PostgreSQL?
PostgreSQL, a robust open-source relational database management system, offers a variety of tools for managing and organizing data. One such feature is the ability to add an identity to an existing column, which is particularly useful in situations when each row requires a unique identifier. In this
4 min read
How to Check Column Types in PostgreSQL?
In PostgreSQL, checking column types is an important aspect of understanding the structure and data stored in a database table. It helps database developers and administrators work effectively by providing a clear picture of the database schema. In this article, we will explore various methods to ch
4 min read
PostgreSQL - NOT NULL Constraint
In PostgreSQL, the NOT NULL constraint is a fundamental feature to ensure that a column cannot contain NULL values. NULL represents unknown or missing information in databases, distinct from an empty string or the number zero. For example, if you ask someone for their email address and they donât kn
3 min read
How to Check a Column is Empty or Null in SQL Server
In SQL Server table columns, there can be times when there is NULL data or Column Value is Empty (''). When data is displayed or used in data manipulations, there could be a need to remove these records with NULL values or Empty column values or replace the NULL value with another value like EMPTY v
5 min read