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:
1 | sudo su - postgres |
After we enter the superuser password, we can start the interactive terminal for Postgres:
1 | psql |
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:
1 | postgres=# CREATE DATABASE db_name; |
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:
1 2 | CREATE TABLE TABLE_NAME( COLUMN NAME + DATA TYPE + COLUMN_CONSTRAINT); |
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.
1 2 3 | db_name=# CREATE TABLE payroll(employee_id INT PRIMARY KEY NOT NULL, name VARCHAR(30), wage_rate INTEGER, salary REAL); |
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:
1 | db_name=# SELECT * FROM payroll WHERE employee_id IS NOT NULL; |
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:
1 2 3 4 5 6 7 8 9 | employee_id | name | wage_rate | salary -------------+--------+-----------+-------- 1 | hector | 400 | 10000 2 | justine| 400 | 10000 3 | sofia | 450 | 15000 4 | ivy | 400 | 10000 5 | henry | 500 | 25000 6 | myler | 500 | 25000 (6 ROWS) |
If we switch our SELECT
statement to use the NULL
condition, our results would look like this:
1 2 3 4 | db_name=# SELECT * FROM payroll WHERE wage_rate IS NULL; employee_id | name | wage_rate | salary -------------+------+-----------+-------- (0 ROWS) |
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:
1 2 | db_name=# UPDATE payroll SET name ='Justine' WHERE employee_id IS NOT NULL; |
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:
1 | db_name=# DELETE FROM payroll WHERE name IS NOT NULL; |
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:
1 | SELECT * FROM TABLE_NAME; |
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.
Pilot the ObjectRocket Platform Free!
Try Fully-Managed CockroachDB, Elasticsearch, MongoDB, PostgreSQL (Beta) or Redis.
Get Started