Postrges Alter Column Not Null

This tutorial explores on changing a table column in PostgreSQL to include the NOT NULL constraint. This ensures that a specific column within the database table always contains a value and no NULL values are available.

Before proceeding, this tutorial explores on how to modify an existing table column and not the fundamentals of the NOT NULL constraint in PostgreSQL. If you are looking for the basics of that, check this tutorial https://linuxhint.com/postgres-not-null.

Setting Up the Sample Table

Let us start by configuring a sample table. For demonstration purposes, we create a table that stores the network information as shown in the following query:

CREATE TABLE network_info (
id SERIAL PRIMARY KEY,
ip_address VARCHAR(15) NOT NULL,
subnet_mask VARCHAR(15),
gateway VARCHAR(15),
dns_server VARCHAR(15),
description TEXT
);

We can then insert the sample records into the table as shown in the following:

The resulting table is as follows:

Notice that the table contains NULL values in various columns. Let us explore how to add the NOT NULL constraint to one of these columns.

Step 1: Check If there are NULL Values in the Column

Before adding a NOT NULL constraint to an existing column, ensuring that no NULL values columns is essential. This is because adding a NOT NULL constraint to an existing column does not affect the already existing data.

We can check for NULL values with a SELECT statement. For example, to check the null columns in the dns_server column of the “network_info” table, we can run the following query:

SELECT * FROM network_info WHERE dns_server IS NULL;

This should return all the rows where the value of the dns_server column is NULL.

Step 2: Fix the NULL Values (If There Are Any)

If you get any rows containing NULL values, as shown in the previous example, you need to handle these values before setting the NOT NULL column in PostgreSQL.

One common way to handle these NULL values is to update them to some default value. For example, in the previous sample table, we can set the default DNS server for NULL values to 1.1.1.1 as shown in the following example:

UPDATE network_info SET dns_server = '1.1.1.1' WHERE dns_server IS NULL;

This should update the NULL values to include the 1.1.1.1 as shown in the following output:

As you might notice, no NULL values are in the dns_server column anymore. Therefore, we can add the NOT NULL constraint for the column.

Using the Alter Column to Add the Not Null Constraint in PostgreSQL

Once we verified that there are NO NULL values in the column on which we wish to add a NOT NULL constraint, we can use the ALTER COLUMN clause to add the NOT NULL constraint as shown in the following:

Example:

ALTER TABLE network_info ALTER COLUMN dns_server SET NOT NULL;

Once we run the given query, any attempt to insert a NULL value into the dns_server column results to an error.

Example Demonstration:

INSERT INTO network_info (ip_address, subnet_mask, gateway, dns_server, description)
VALUES ('192.168.1.101', '255.255.255.0', '192.168.1.254', NULL, 'Router');

You will notice that the value of the dns_server column is NULL. If we run the previous query, we should get an error as shown in the following:

ERROR: null value in column "dns_server" of relation "network_info" violates not-null constraint
SQL state: 23502

There you have it!

Conclusion

You learned how to check a table column for NULL values, update the NULL values with default values, and add a NOT NULL constraint to an existing table column.