Postgresql Not Null Constraint
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.
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 -Vwill 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:
sudo su - postgres
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:
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:
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.
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
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:
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
If we switch our
SELECT statement to use the
NULL condition, our results would look like this:
db_name=# SELECT * FROM payroll WHERE wage_rate IS NULL;
employee_id | name | wage_rate | salary
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
Here’s an example:
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:
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:
SELECT * FROM TABLE_NAME;
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