Postgresql Not Null Constraint

Introduction

When you’re creating a table in PostgreSQL, you have the ability to add certain constraints on the columns. In SQL, constraints are special rules that limit the sort of data that can be stored in a given column. One type of constraint is the NOT NULL constraint, which dictates that a given column cannot contain any null values. In this article, we’ll take a closer look at the PostgreSQL NOT NULL constraint and review some examples of how the constraint is used.

Prerequisites

Before attempting to follow along with this tutorial, you’ll need to make sure these important prerequisites are in place:

• Make sure that PostgreSQL is installed and working on your device. The command psql -V will display the version of PostgreSQL that’s installed on your machine.

• You’ll need a basic knowledge of database management systems and SQL commands in PostgreSQL.

Open psql Command-line Interface

In order to execute commands, we’ll need to open the psql command-line interface.

There are two steps required to connect to psql command-line console.

The first command will grant you login privileges as the postgres superuser after you enter the password:

After we enter the superuser password, we can start the interactive terminal for Postgres:

Once you are connected to the psql interface, you can start executing queries and commands in PostgreSQL.

PostgreSQL not null

By default, a data record can contain null values in PostgreSQL. Using the NOT NULL constraint enforces that the field needs to contain a value other than a null value. With this constraint in place, you can’t insert or update a record without adding a value for the field in question.

Create a Database in PostgreSQL

Let’s create a database on which we can test out the NOT NULL constraint. Make sure the new database name you choose is unique; otherwise, PostgreSQL will return an error.

To create a new database, we’ll use the following syntax:

We’ll use the name db_name for our database, but you can supply any name you’d like.

To connect to the database, enter \c followed by your database name. You’ll be notified that you’re now connected to database “db_name” as user “postgres”.

Create a PostgreSQL Table

To create a table, we use this basic syntax:

Notice that the data type is placed after the column name, and it’s followed by the constraint, if one is being used.

Use the Data Type to Add the NOT NULL Constraint

Constraints are used to define the rules about what type of data is stored in a table. They help limit the data that can be added to a field in a table. If any data is entered that violates the constraint, you’ll get an error prompt from PostgreSQL.

Using the PostgreSQL where not null

In Postgres, the NOT NULL constraint will never accept any null values. There is no specific name for null data– it’s not equivalent to a zero value or even an empty string. In theoretical database terms, it represents unknown information.

Selecting using the where is not null in PostgreSQL

Let’s look at an example where we can apply the concept of NOT NULL in a SELECT statement:

This query will return all records where the value of employee_id is not null. Since the employee_id has a not null constraint, the results will look like the following:

If we switch our SELECT statement to use the NULL condition, our results would look like this:

As we mentioned earlier, the employee_id field has a NOT NULL constraint, so no records match the NULL condition specified in this query.

Using the update where not null

In PostgreSQL, we can also use the NOT NULL condition in an UPDATE statement.

Here’s an example:

In the example above, we see that the PostgreSQL NOT NULL condition will update the table named payroll. It will modify the name column to have a value of ‘Justine’ for any records where the employee_id does not contain a null value.

Using the delete where not null

We can also apply a NOT NULL condition to a DELETE statement using the following syntax:

This syntax will DELETE all the records from the payroll table where the name doesn’t have a null value.

Let’s check to see if the records were successfully deleted by executing a SELECT * statement:

Conclusion

When you want to enforce certain rules about the data stored in your PostgreSQL tables, constraints are the best way to get the job done. In this article, we focused our attention on the PostgreSQL NOT NULL constraint and looked at several examples of how to use the NOT NULL condition in a variety of database operations. With these examples to guide you, you’ll be able to make use of the NOT NULL constraint in your own PostgreSQL database.