How to Drop Not Null Constraint in PostgreSQL

1. Importance of Using the NOT NULL Constraint
2. Apply the Not Null Constraint in PostgreSQL
3. Drop the Not Null Constraint in PostgreSQL

The NOT NULL constraint is a pretty helpful tool in PostgreSQL. However, it might be a bit tricky to use. This article will discuss how you can apply and drop it to achieve the desired results in your database.

Importance of Using the NOT NULL Constraint

The constraint can be defined as preset rules applied to a column in a table so that the data entered into it aligns with the requirements.

It will prevent invalid data entry, which tends to be a huge problem, especially if you have a lot of data in an extensive database with multiple relationships. You can apply as many constraints as you like or none at all.

The NOT NULL constraint is a particular constraint we can add to any column in a table. As the name suggests, prevent the entry in each row of that column from being NULL.

It is particularly essential when dealing with PRIMARY keys, which can never be NULL. It is also necessary when establishing relationships, as they cannot be created or are often done incorrectly when NULL values are involved.

They will also prevent a user from accidentally skipping an entry. So, let’s start learning how we can apply a NOT NULL constraint in PostgreSQL.

Apply the Not Null Constraint in PostgreSQL

Applying the NOT NULL constraint isn’t as difficult as it may seem. In most cases, it is only an additional line of code. You can use any of the following methods based on your situation.

Add the NOT NULL Constraint While Creating a Table

The best time to add the NOT NULL constraint to a column is during creation. It is because it only involves adding NOT NULL next to it like this:

CREATE TABLE name(
    column_1 int PRIMARY KEY,
    column_2 VARCHAR (100),
    column_3 VARCHAR (300) NOT NULL
);

Here, the NOT NULL constraint is added to column_3.

Add the NOT NULL Constraint to an Existing Table

One of the many things that make using PostgreSQL easy is how you can always add additional constraints even if the table has already been created. You can try this out using the following code.

First, create a table:

CREATE TABLE users(
    user_id int PRIMARY KEY,
    user_name VARCHAR (100) UNIQUE,
    "e-mail" VARCHAR (300)
);

It will create a table; however, if you realize you forgot to add a NOT NULL constraint, you can do this in two ways. The first one requires a bit more work. You first drop the existing table and create a new one, as shown below:

DROP TABLE users;

After this, create the table again with the not null constraint as shown below:

CREATE TABLE users(
    user_id int PRIMARY KEY,
    user_name VARCHAR (100) UNIQUE,
    "e-mail" VARCHAR (300) NOT NULL
);

This method will work, but it is a bit time-consuming. A much simpler alternative is using the ALTER TABLE command as shown below:

ALTER TABLE users 
ALTER COLUMN "e-mail" set not null;

This way, the NOT NULL constraint is added to the e-mail column. Moreover, you do not have to delete your table for this.

Drop the Not Null Constraint in PostgreSQL

While editing your database, you might realize that the NOT NULL constraint is not needed or placed incorrectly. At times like these, it is necessary to know how to remove or drop the NOT NULL constraint. Here are some of the ways:

Drop the NOT NULL Constraint in Individual Columns

If you would like to drop the constraint in individual columns, you can easily do so with the same ALTER TABLE command but with a slight difference since you are removing the constraint now. You can achieve this using the code below:

ALTER TABLE users
ALTER COLUMN "e-mail" drop not null;

You can quickly drop the Postgres NOT NULL constraint in the e-mail column. It is a better alternative than dropping the whole table and creating it again without adding the NOT NULL constraint to desired columns.

Drop the NOT NULL Constraint in Multiple Columns

You can use the code above multiple times if you want to drop the constraint in various columns.

ALTER TABLE users
ALTER COLUMN "e-mail" drop not null;

ALTER TABLE users
ALTER COLUMN new_column drop not null;

However, this can be a bit time-consuming. Luckily, PostgreSQL offers an alternative that we can use to drop NOT NULL as follows.

ALTER TABLE users
ALTER COLUMN "e-mail" drop not null,
ALTER COLUMN new_column drop not null;

This way, you can drop the NOT NULL constraint in multiple columns in one go.

Drop All Not Null Constraints in PostgreSQL

The method above might be helpful if you don’t have a lot of columns that need to be altered. However, if you want to drop all NOT NULL constraints from a table with many columns, it might be challenging to identify all columns needed.

For that purpose, you can use the code below:

SELECT x.attname
FROM pg_catalog.pg_attribute x
WHERE attrelid = 'users'::regclass
AND x.attnum > 0
AND NOT x.attisdropped
AND x.attnotnull;

It will filter out all columns with a NOT NULL constraint. However, this will also include PRIMARY keys, which you might want to exclude depending on your requirements.

After this, use the code above to drop constraints for all the necessary columns. That is a straightforward method that PostgreSQL offers to add and drop the NOT NULL constraints to columns in a table.