POSTGRESQL

A Guide to the Postgres Not Null Constraint

intro

Take a look at the PostgreSQL NOT NULL constraint and learn how to use it effectively to maintain the wholeness of your database.

Tools used in the tutorial
Tool Description Link
Dbvisualizer DBVISUALIZER
TOP RATED DATABASE MANAGEMENT TOOL AND SQL CLIENT

Maintaining the wholeness of a database is an important concern for any organization that relies on a database to power its applications and services. Without proper safeguards in place, critical information can become incomplete, inconsistent, or entirely absent—leading to flawed decision-making, frustrated users, and a tarnished reputation. Fortunately, PostgreSQL, the powerful open-source database management system, provides a solution. This is the Postgres NOT NULL constraint.

In this blog, we’re walking you through the NOT NULL constraint in Postgres, its powering syntax and its use case. Join us!

Introduction to the Postgres Not Null Constraint

The Postgres NOT NULL constraint is a fundamental tool that ensures data fields are never left empty. This means that every row in the table must have a value for the specified column. The end goal is to prevent the storage of missing information and to maintain the reliability of your database.

In contrast to the NULL value, which signifies a lack of information, the NOT NULL constraint mandates that a column must have a non-NULL value. This requirement helps maintain PostgreSQL data integrity, ensuring that information is always present and accessible.

Postgres Not Null: Operator Syntax

A not-null constraint simply specifies that a column must not assume the null value. To create a NOT NULL PostgreSQL constraint, you have a few options:

#1 Defining a new table:

Copy
        
1 CREATE TABLE table_name ( 2 column_name data_type NOT NULL, 3 -- other column definitions 4 );

NOT NULL constraints are always expressed as column constraints. Functionally, a NOT NULL constraint is the same as writing a check constraint CHECK (column_name IS NOT NULL). However, with Postgres, it is more effective to write an explicit NOT NULL constraint. The disadvantage with this approach is that NOT NULL constraints defined in this manner cannot have explicit names.

Note that if a column has a NOT NULL PostgreSQL constraint, any attempt to insert or update NULL in the column will result in an error.

#2 Modifying an already existing table:

Copy
        
1 ALTER TABLE table_name 2 ALTER COLUMN column_name SET NOT NULL;

#3 Implementing more than one constraint:

Copy
        
1 CREATE TABLE table_name ( 2 column_name data_type constraint1 constraint2 constraint3, 3 -- other column definitions 4 );

In Postgres, the order of the constraints doesn't matter: it does not necessarily determine in which order the constraints are checked.

How to Use the Not Null Constraint in Postgres

For illustration purpose, create an employees table with some columns and their respective constraints as shown in the query below:

Copy
        
1 CREATE TABLE employees ( 2 employee_id SERIAL PRIMARY KEY, 3 first_name VARCHAR(50) NOT NULL, 4 last_name VARCHAR(50) NOT NULL, 5 email VARCHAR(100) NOT NULL UNIQUE, 6 hire_date DATE NOT NULL CHECK (hire_date <= CURRENT_DATE) 7 );

Executing in DbVisualizer, a full-feature database client that supports PostgreSQL and many other databases:

Creating demo ‘employees’ table in DbVisualizer
Creating demo employees table in DbVisualizer

Now, to test the validity of the implemented NOT NULL constraints, try to insert a valid data set by running the query below in DbVisualizer:

Testing validity of our NOT NULL constraints
Testing validity of our NOT NULL constraints

Next, to validate whether we’re going to get the expected error, let’s test our Postgres NOT NULL constraints by inserting an invalid data set that violates the constraints:

Error from violating our NULL constraints
Error from violating our NULL constraints

Note that if you use NULL instead of NOT NULL, the particular column will accept both NULL and non-NULL values. If you don’t explicitly specify NULL or NOT NULL, the particular column will accept NULL by default.

Postgres NOT NULL Constraint with CHECK Constraint

In PostgresSQL, the NOT NULL constraint ensures that a column cannot have NULL values. However, in some cases, you may want more complex logic where either one column or another must not be NULL or empty. This can be done using a CHECK constraint that adds this logic.

It can be expressed using a CHECK constraint as follows:

Copy
        
1 CHECK (column IS NOT NULL)

For example, in an developers table, you might want to ensure that either the email or hire_date column is either not NULL or empty, but not necessarily both. This can be done by implementing a CHECK constraint as shown:

Copy
        
1 CREATE TABLE developers ( 2 id serial PRIMARY KEY, 3 first_name VARCHAR (50), 4 last_name VARCHAR (50), 5 password VARCHAR (50), 6 email VARCHAR (50), 7 CONSTRAINT first_name_last_name_notnull CHECK ( 8 NOT ( 9 ( 10 first_name IS NULL 11 OR first_name = '' 12 ) 13 AND ( 14 last_name IS NULL 15 OR last_name = '' 16 ) 17 ) 18 ) 19 );

In this query:

  • first_name IS NULL OR first_name = '' checks if first_name is either NULL or an empty string.
  • last_name IS NULL OR last_name = '' checks if last_name is either NULL or an empty string.
  • NOT ( ... AND ... ) ensures that both conditions are not true simultaneously, meaning at least one of the columns (first_name or last_name) must have a value.

To validate whether or not we’re going to get the expected error, test the Postgres NOT NULL constraints by inserting an invalid data set that violates the constraints:

Copy
        
1 INSERT INTO developers (first_name, last_name) 2 VALUES 3 (NULL, NULL), 4 (NULL, ''), 5 ('', NULL), 6 ('', '');
Error from violating our NULL constraints
Error from violating our NULL constraints

Best Practices and Tips for Using the NOT NULL Constraint in Postgres

Time to look at some of the best practices that should be taken into consideration when working with NOT NULL in Postgres:

  • Only use the NOT NULL constraint when you are certain that the column should never have a null value.
  • Use NOT NULL in combination with other constraints, such as CHECK or to further maintain the wholeness of your data.
  • Validate the data before attempting to insert it into the database, to ensure that it meets the NOT NULL and other constraints.
  • Regularly review your database schema to ensure that NOT NULL constraints are still appropriate and necessary

It’s a wrap!

Conclusion

The Postgres NOT NULL constraint ensures data fields are never left empty to prevent the storage of missing information and to maintain the reliability of your database. To better appreciate its capabilities, you need a tool that helps you manage databases and visually explore query results. This is where a full-featured database client like DbVisualizer comes in. In addition to being able to connect to several DBMSs, it offers advanced query optimization functionality, and full support for all database features, including NOT NULL operations. Download DbVisualizer for free now!

FAQs

What is the NOT NULL constraint in PostgreSQL?

In Postgres, the NOT NULL constraint ensures that a column cannot have a NULL value. This is important for maintaining the wholeness of your data as it ensures that critical fields are always populated with valid data. It prevents incomplete records from being inserted into the database.

How do I define a NOT NULL constraint in PostgreSQL?

You can define a NOT NULL constraint in PostgreSQL by including the NOT NULL clause in the column definition when creating or altering a table. For example: column_name VARCHAR(50) NOT NULL.

How do I add a NOT NULL constraint to an existing column in PostgreSQL?

To add a NOT NULL constraint to an existing column, you can use the ALTER TABLE statement. Here's an example:

Copy
        
1 ALTER TABLE table_name 2 ALTER COLUMN column_name SET NOT NULL;

Can I combine the NOT NULL constraint with other constraints in PostgreSQL?

Yes, you can combine the NOT NULL constraint with other constraints, such as UNIQUE, CHECK, and DEFAULT, either during table creation or by altering the table. For example:

Copy
        
1 CREATE TABLE developers ( 2 developer_id SERIAL PRIMARY KEY, 3 username VARCHAR(50) NOT NULL UNIQUE, 4 email VARCHAR(100) NOT NULL, 5 created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP 6 );

About the author
Leslie S. Gyamfi.
Leslie S. Gyamfi
Leslie Gyamfi is a mobile/web app developer with a passion for creating innovative solutions. He is dedicated to delivering high-quality products and technical articles. You can connect with him on LinkedIn
The Table Icon
Sign up to receive The Table's roundup
More from the table
Title Author Tags Length Published
title

PostgreSQL Subquery - A Guide

author Leslie S. Gyamfi tags POSTGRESQL SUBQUERY 3 min 2024-10-02
title

SQL OFFSET: Skipping Rows in a Query

author Antonello Zanini tags MySQL ORACLE POSTGRESQL SQL SQL SERVER 7 min 2024-09-30
title

SQL FETCH: Retrieving Data In Database Cursors

author Antonello Zanini tags MySQL ORACLE POSTGRESQL SQL SQL SERVER 8 min 2024-09-24
title

PostgreSQL NULLIF: Conditional Logic Made Easier

author Antonello Zanini tags NULLIF POSTGRESQL 6 min 2024-09-11
title

The Postgres UPDATE Statement: A Deep Dive

author Leslie S. Gyamfi tags POSTGRESQL 4 min 2024-09-05
title

PostgreSQL Sequence: A Beginner’s Guide

author Ochuko Onojakpor tags POSTGRESQL 11 min 2024-09-04
title

Cursors in PostgreSQL - A Guide

author Leslie S. Gyamfi tags Cursors POSTGRESQL 7 min 2024-09-03
title

Everything You Need to Know About the Postgres JSONB Data Type

author Antonello Zanini tags JSON POSTGRESQL 5 min 2024-09-02
title

PostgreSQL ADD COLUMN: Add One or More Columns to a Table

author Leslie S. Gyamfi tags ALTER POSTGRESQL 6 min 2024-08-22
title

Index Creation in SQL

author Lukas Vileikis tags MySQL POSTGRESQL SQL SERVER 7 min 2024-08-15

The content provided on dbvis.com/thetable, including but not limited to code and examples, is intended for educational and informational purposes only. We do not make any warranties or representations of any kind. Read more here.

Cookie policy

We use cookies to ensure that we give you the best experience on our website. However you can change your cookie settings at any time in your browser settings. Please find our cookie policy here ↗