intro
Take a look at the PostgreSQL NOT NULL constraint and learn how to use it effectively to maintain the wholeness of your database.
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:
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:
1
ALTER TABLE table_name
2
ALTER COLUMN column_name SET NOT NULL;
#3 Implementing more than one constraint:
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:
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:
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:
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:
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:
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:
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:
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:
1
INSERT INTO developers (first_name, last_name)
2
VALUES
3
(NULL, NULL),
4
(NULL, ''),
5
('', NULL),
6
('', '');
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:
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:
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:
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
);
