Add a Not Null Constraint in Postgres

In SQL-based databases, a NOT NULL constraint allows us to ensure that the data that is stored in a specific column does not contain any NULL values. For example, if we add a NOT NULL constraint to a table column, we enforce a rule that a value MUST be provided for that column. This can, in turn, help prevent the errors that occur from NULL values and preserve the data integrity.

In this tutorial, we will walk you through adding a NOT NULL constraint to a table column in PostgreSQL. We will cover the basic syntax, examples, and points to note when working with a NOT NULL constraint.

Requirements:

To follow along with this post, we assume that you have the following:

Installed and running PostgreSQL database
Basic knowledge of SQL and PostgreSQL syntax

Let’s proceed.

Connecting to PostgreSQL Server

Ensure that you are connected to the PostgreSQL server to perform the queries in this post. For basic administration, you can use the psql command-line tool.

Create a Table and Sample Data

Let us set up a basic table and add a sample data for demonstration purposes. Feel free to skip this section if you already have an existing table that you wish to use.

CREATE TABLE user_sessions (

session_id SERIAL PRIMARY KEY,

username VARCHAR(50),

ip_address INET,

browser VARCHAR(50),

start_time TIMESTAMPTZ,

end_time TIMESTAMPTZ

);

-- Insert sample data

INSERT INTO user_sessions (username, ip_address, browser, start_time, end_time)

VALUES

('user1', '192.168.0.1', 'Chrome', '2023-05-15 10:00:00', '2023-05-15 11:30:00'),

('user2', '192.168.0.2', 'Firefox', '2023-05-15 12:00:00', '2023-05-15 13:15:00'),

('user1', '192.168.0.3', 'Safari', '2023-05-15 14:30:00', NULL);

The previous query creates a user_sessions table that stores the user session information such as the username, IP address, browser, start, and end time.

The resulting time is as follows:

Identify the Target Column

Before you can create a NOT NULL constraint to a given column, it is good to ensure that the target column exists.

Let us take the “user_sessions” table that we created. Our goal is to implement the NOT NULL constraint to the username column.

Add the NOT NULL Constraint

We can use the ALTER TABLE statement with the ALTER COLUMN clause to add a NOT NULL constraint to a column

The syntax is as demonstrated in the following:

ALTER TABLE table_name ALTER COLUMN column_name SET NOT NULL;

Example:

ALTER TABLE user_sessions ALTER COLUMN username SET NOT NULL;

The given query should add a NOT NULL constraint to the username column in the user_sessions table.

Verify the Added Constraint

Once we add the constraint, we can check the table information to verify using the \d+ command in psql.

\d+ table_name

Example:

\d+ user_sessions;

This should return the detailed information for the user_sessions table. Locate the NULLABLE column. The username column should show “NOT NULL”.

Test Constraint

To test the newly added NOT NULL constraint, insert a row into the table without specifying a value for the target column.

For example, let us attempt to a null value to the username column in the “user_sessions” table.

insert into user_sessions (ip_address, browser, start_time, end_time)

values ('192.168.0.1', 'Chrome', '2023-05-15 10:00:00', '2023-05-15 11:30:00');

This should return an error which indicates that the insert violates the NOT NULL constraint.

SQL Error [23502]: ERROR: null value in column “username” of relation “user_sessions” violates not-null constraint.

Handling NULL Values

It is good to keep in mind that the NOT NULL constraint only applies to the values that are inserted after the constraint is added. Hence, it does not affect any NULL values that exist before it.

For that, you can use the features such as the DROP statement to remove the NULL values as shown in the following:

DELETE FROM table_name WHERE column_name IS NULL;

This should drop all the rows that contain a NULL value in the specified column.

You can also update the table to add a default value for any row that contains a NULL value in the specified column.

For example, you can run the following:

UPDATE table_name SET column_name = 'NIL' WHERE column_name IS NULL;

The given query should insert the NIL value to all NULL rows.

Conclusion

You now learned how to add a NOT NULL constraint to a column in a PostgreSQL database.