Hi! In this article we will learn basics and advanced stuff about PostgreSQL NOT NULL Constraints. We will take a look at NULL, what NULL means in computing, how do they behave as constraints in PostgreSQL.

What is a Constraint?

Constraints are the way to limit data stored in a column. You can limit length, NULL data etc.

In PostgreSQL documentation, PostgreSQL defines a constraint as:

Data types are a way to limit the kind of data that can be stored in a table. For many applications, however, the constraint they provide is too coarse. For example, a column containing a product price should probably only accept positive values. But there is no standard data type that accepts only positive numbers. Another issue is that you might want to constrain column data with respect to other columns or rows. For example, in a table containing product information, there should be only one row for each product number.

To that end, SQL allows you to define constraints on columns and tables. Constraints give you as much control over the data in your tables as you wish. If a user attempts to store data in a column that would violate a constraint, an error is raised. This applies even if the value came from the default value definition.

https://www.postgresql.org/docs/current/ddl-constraints.html

Null Data in PostgreSQL?

Take data in PostgreSQL is a kind of spreadsheet.

As you can see cell D2 is empty. No data is being stored in this cell, that is called NULL in PostgreSQL.

PostgreSQL stores data in files. (Like a text file) NULL cells in PostgreSQL will be empty on the disk as well.

PostgreSQL NOT NULL Constraints

When inserting data into a PostgreSQL table, the NOT NULL constraint prevents null values from being entered into certain columns.

Take a user data table as an example. User data tables contain e-mail address, and in most of the cases, saving e-mail address is a must.

Frontend developers, check e-mail adress data not to be empty on each registration. But as well said “Do not trust users.” They can find a way to post data directly to your backend endpoint and hack the system in that way.

Of course, backend developers are checking submitted data as well but who knows? There might be a leak or a bug which lets users to submit an empty string.

Implementing an additional data integrity check can significantly improve security, especially for systems exposed to external access.

PostgreSQL NOT NULL Constraints Samples

Let’s take a look at a few examples for PostgreSQL NOT NULL constraints. I mentioned a users table above, let’s start with it.

Users Table Example

Let’s write down the DDL.

CREATE TABLE users (
  user_id SERIAL PRIMARY KEY,
  username VARCHAR(255) NOT NULL UNIQUE,
  email VARCHAR(255) NOT NULL UNIQUE CHECK (email LIKE '%@%'),
  password_hash VARCHAR(255) NOT NULL,
  first_name VARCHAR(255),
  last_name VARCHAR(255),
  created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

Wait?! Can you use multiple constraints at once? Of course!

In this example you can see NOT NULL is being used with UNIQUE and also with CHECK for e-mail address. Which checks the e-mail adress to be in format like something@somewhere.

Orders Table Not Null Example

When we design an online shopping system, we need to define some constraints as well to check data integrity.

CREATE TABLE orders (
  order_id SERIAL PRIMARY KEY,  -- Auto-incrementing primary key
  customer_id INTEGER NOT NULL,  -- References a customer table (assuming it exists)
  order_date DATE NOT NULL DEFAULT CURRENT_DATE,  -- Capture order date automatically
  total_amount DECIMAL(10,2) NOT NULL,  -- Store total order value
  status VARCHAR(20) NOT NULL DEFAULT 'pending',  -- Track order status
  shipping_address VARCHAR(255) NOT NULL,  -- Required for delivery
  billing_address VARCHAR(255) NOT NULL,  -- Required for payment
  created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,  -- Record creation time
  updated_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP  -- Track updates
);

In this example, most of the columns are defined with NOT NULL constraint. Why?

This is a typical design we use in financial systems. We don’t want our data source to contain empty data fields. Financial systems are designed to be bullet proof. Everyone, even the financial operations departments can make a mistake. But as a data owner, our analyst teams and us as technics, we are responsible for the data integrity as well.

Production Table Not Null Example

Have another example from a production company.

Imagine you have a movie database and want to store information about each film. This CREATE TABLE script helps you do that in PostgreSQL:

CREATE TABLE productions (
  id SERIAL PRIMARY KEY,  -- Auto-generated unique number for each movie
  title TEXT NOT NULL,    -- Movie title (can't be blank) and must be unique
  release_date DATE,     -- When the movie came out (can't be blank)
  budget MONEY,          -- How much it cost to make (can't be blank)
  studio TEXT,           -- Who made the movie (can't be blank)
  genre TEXT,           -- What type of movie it is (can't be blank)
  director TEXT,        -- Who directed the movie (can't be blank)
  synopsis TEXT         -- Short description of the movie (optional)
);

Here’s the answer simplified for beginners:

Building a “Productions” table in PostgreSQL:

Imagine you have a movie database and want to store information about each film. This CREATE TABLE script helps you do that in PostgreSQL:

SQL

CREATE TABLE productions (
  id SERIAL PRIMARY KEY,  -- Auto-generated unique number for each movie
  title TEXT NOT NULL,    -- Movie title (can't be blank) and must be unique
  release_date DATE,     -- When the movie came out (can't be blank)
  budget MONEY,          -- How much it cost to make (can't be blank)
  studio TEXT,           -- Who made the movie (can't be blank)
  genre TEXT,           -- What type of movie it is (can't be blank)
  director TEXT,        -- Who directed the movie (can't be blank)
  synopsis TEXT         -- Short description of the movie (optional)
);

Kodu dikkatli kullanın. Daha fazla bilgicontent_copy

Why are these rules important?

• NOT NULL: Makes sure important information like title, release date, etc. is always there, preventing incomplete entries.
• UNIQUE (title): Ensures no two movies have the same name, avoiding confusion.
• PRIMARY KEY (id): Gives each movie a unique ID for easy reference.

Remember:

• This is a basic example. You can add or remove columns based on your needs.
• There are other rules (like “budget can’t be negative”) you can set to ensure correct data.
• Talk to someone familiar with databases if you need help customizing this script!

I hope this explanation is easier to understand!

Alternative Methods

While there isn’t a true “alternative” to the NOT NULL constraint in PostgreSQL, there are a few different ways to achieve similar results:

Check Constraints

You can create a CHECK constraint that explicitly checks if a value is not null. This is functionally equivalent to a NOT NULL constraint, but offers more flexibility. For example:

CREATE TABLE my_table (
  id INTEGER PRIMARY KEY,
  name VARCHAR(255) CHECK (name IS NOT NULL)
);

Default Values

Instead of enforcing non-null, you can set a default value for the column. This ensures a value is always present, but allows nulls if explicitly inserted. For example:

CREATE TABLE my_table (
  id INTEGER PRIMARY KEY,
  name VARCHAR(255) DEFAULT 'Unknown'
);

Triggers

You can create a trigger that fires on insert or update events and throws an error if the value is null. This offers more control but can be complex to manage.

Application Logic

You can enforce data integrity within your application by checking for null values before inserting or updating data. This approach gives you maximum control but requires code implementation.

Choosing the best method:

• Simple data integrity: NOT NULL constraint is the easiest and most efficient option.
• More complex validation: CHECK constraint offers flexibility for custom validation rules.
• Default values: Useful when a default value is acceptable even if data might be missing sometimes.
• Triggers and application logic: For advanced scenarios requiring specific controls or flexibility.

Remember, each method has its trade-offs in terms of performance, complexity, and flexibility. Choose the approach that best balances your data integrity needs with your development needs.

Performance

When building efficient and robust databases, PostgreSQL NOT NULL constraints become essential tools. They enforce crucial data integrity by ensuring specific columns never accept empty values. But do these constraints come at a performance cost? Let’s dive in!

Benefits of NOT NULL Constraints:

• Data Integrity: Prevent missing values, upholding data accuracy and consistency.
• Improved Queries: Optimized indexing eliminates null checks, potentially speeding up queries.
• Reduced Storage: Null values often take up extra space, making tables more compact.
• Stronger Validation: Catch data entry errors early, enhancing data quality.

Performance Considerations:

• Insert Performance: Initial data insertion might be slightly slower due to constraint checks.
• Null Value Handling: Checking and rejecting null values adds small overhead.
• Indexing: While beneficial, indexing NOT NULL columns adds its own performance trade-offs.

Optimizing for Performance:

• Targeted Usage: Apply NOT NULL only to crucial columns, not all.
• Selective Indexing: Consider indexing frequently used NOT NULL columns for efficient queries.
• Application Validation: Handle potential null values in your application before database insertion.
• Monitoring and Profiling: Regularly monitor performance and profile queries to identify bottlenecks.

Summary

In this comprehensive article, we delved into the nuances of PostgreSQL NOT NULL constraints, exploring their significance, practical applications, alternative methods, and performance considerations. We learned that these constraints play a pivotal role in maintaining data integrity, enhancing query optimization, and ensuring robust validation.

From understanding the basics of constraints to exploring real-world examples like users and orders tables, as well as alternative methods such as check constraints and default values, we covered a wide spectrum of essential information. Additionally, we examined the performance implications of employing NOT NULL constraints and provided insights into optimizing their usage.

Join our community to stay updated with the latest insights on database management and development best practices. Subscribe to our newsletter and share your thoughts in the comments section below. We look forward to hearing about your experiences and questions related to PostgreSQL NOT NULL constraints.

Hi! I’m an IT Specialist

I want to hear from you! I am Working with enterprises for 10+ years to improve their infrastructure and efficiency.

Get in touch with me.

Get in touch