PostgreSQL Alter Column NOT NULL

This will ensure that the specific column within the database table always contains a value and no NULL values are available.

Before proceeding, this tutorial will explore 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 the tutorial https://cloudenv.io/postgres-not-null

Setting Up Sample Table

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

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 sample records into the table as shown:

INSERT INTO network_info (ip_address, subnet_mask, gateway, dns_server, description)
VALUES 
('192.168.1.1', '255.255.255.0', '192.168.1.254', '8.8.8.8', 'Router'),
('192.168.1.2', '255.255.255.0', NULL, NULL, 'Disconnected device'),
('192.168.1.3', NULL, NULL, NULL, NULL),
('192.168.1.4', '255.255.255.0', '192.168.1.254', '8.8.4.4', 'Workstation'),
('192.168.1.5', NULL, '192.168.1.254', NULL, 'Workstation with manual IP'),
('192.168.1.6', '255.255.255.0', '192.168.1.254', '8.8.8.8', 'NAS Device'),
('192.168.1.7', '255.255.255.0', '192.168.1.254', '8.8.8.8', 'Server'),
('192.168.1.8', '255.255.255.0', NULL, NULL, 'Device without internet access'),
('192.168.1.9', NULL, NULL, NULL, NULL),
('192.168.1.10', '255.255.255.0', '192.168.1.254', '8.8.4.4', 'Workstation');

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 no NULL values columns is essential. This is because adding a NOT NULL constraint to an existing does not affect 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 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 NULL values (if any)

If you get any rows containing NULL values, as shown in the example above, 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 sample table above, we can set the default DNS server for null values to 1.1.1.1, as shown in the example below:

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 you will notice, no NULL values are in the dns_server column anymore. Therefore, we can add the NOT NULL` constraint for the column.

PostgreSQL Alter Column Not Null

Once we have 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:

Example:

ALTER TABLE network_info ALTER COLUMN dns_server SET NOT NULL;

Once we run the above query, any attempt to insert a NULL value into the dns_server column will result in 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 query above, we should get an error as shown:

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

And there you have it.

Conclusion

In this tutorial, you learned how to check a table column for NULL values, update NULL values with defualt values, and finally, add a NOT NULL constraint to an existing table column.