PostgreSQL - NULLIF() Function

Last Updated : 08 Nov, 2024

Effectively handling NULL values is important in database management, especially for ensuring data integrity and avoiding errors. PostgreSQL offers several powerful functions, such as NULLIF and COALESCE, to help manage NULL and empty values efficiently.

In this article, we will guide us through the PostgreSQL NULLIF() function its syntax, usage, and practical examples to help us implement it effectively.

PostgreSQL NULLIF() Function

The PostgreSQL NULLIF function compares two expressions and returns NULL if they are equal; otherwise, it returns the first expression. This function is particularly useful for handling conditional NULLs and avoiding divide-by-zero errors in SQL queries.

Syntax

NULLIF(argument_1,argument_2);

key terms

expression_1: The first value or expression to compare.
expression_2: The second value or expression to compare against expression_1
Return Value: The NULLIF function returns a null value if 'argument_1' equals 'argument_2', otherwise it returns 'argument_1'.

Why Use NULLIF in PostgreSQL?

The NULLIF function is essential when working with data that might have placeholder values, such as an empty string or a specific number like 0, that should be treated as NULL in certain situations. For example, NULLIF is commonly used to replace empty strings ('') or other default values with NULL in SELECT statements.

Examples of PostgreSQL NULLIF() Function

Let us take a look at an example of the NULLIF() Function in PostgreSQL to better understand the concept. First, we create a table named 'posts' as follows:

Query:

CREATE TABLE posts (
  id serial primary key,
    title VARCHAR (255) NOT NULL,
    excerpt VARCHAR (150),
    body TEXT,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP
);

INSERT INTO posts (title, excerpt, body)
VALUES
      ('test post 1','test post excerpt 1','test post body 1'),
      ('test post 2','','test post body 2'),
      ('test post 3', null ,'test post body 3');

Example 1: Displaying Posts Overview with NULLIF

Our aim is to display the posts overview page that shows the title and excerpt of each post. In case the excerpt is not provided, we use the first 40 characters of the post body. We can simply use the following query to get all rows in the posts table.

Query:

SELECT
    ID,
    title,
    excerpt
FROM
    posts;

Output:

1. Query with COALESCE Only

The following query retrieves the id, title, and excerpt for each post. If the excerpt is NULL, it uses the first 40 characters of the body. To substitute the null value in the excerpt column, we can use the COALESCE function as follows.

Query:

SELECT
    id,
    title,
    COALESCE (excerpt, LEFT(body, 40))
FROM
    posts;

Output

Explanation:

Here, the excerpt column in Post 2 shows content from the body since COALESCE treats both NULL and empty strings as missing values. However, empty strings can still appear as blanks.

2. Using NULLIF with COALESCE for Improved Handling

Unfortunately, there is a mix between null value and ” (empty) in the excerpt column. This is why we need to use the NULLIF function. By combining NULLIF with COALESCE, we can treat empty strings as NULL, ensuring a more consistent result.

Query:

SELECT
    id,
    title,
    COALESCE (
        NULLIF (excerpt, ''),
        LEFT (body, 40)
    )
FROM
    posts;

Output

Explanation:

Now, NULLIF converts empty strings to NULL, and COALESCE provides the body content for posts with no excerpt. This approach is more robust for NULL handling in PostgreSQL.

Important Points About PostgreSQL NULLIF() Function

Short-Circuit Evaluation: NULLIF returns the first argument if it doesn't equal the second, avoiding further evaluation.
NULLIF vs. COALESCE: While COALESCE handles NULLs by providing fallback values, NULLIF specifically converts matching expressions to NULL.
Simplifying CASE Statements: Instead of complex CASE logic, NULLIF can be a simpler alternative (e.g., NULLIF(column, 0)).

Conclusion

Mastering the NULLIF function in PostgreSQL helps you efficiently handle NULL and empty values in your database. By combining NULLIF with COALESCE and applying it in SELECT and INSERT statements, we can improve data handling and simplify SQL queries.

These functions not only enhance data accuracy but also streamline query performance. With NULLIF and COALESCE, managing conditional values becomes straightforward, reducing the need for complex conditional logic in our PostgreSQL database.

PostgreSQL - CAST

RajuKumar19

Improve

Article Tags :

PostgreSQL - NULLIF() Function

PostgreSQL NULLIF() Function

Why Use NULLIF in PostgreSQL?

Examples of PostgreSQL NULLIF() Function

Example 1: Displaying Posts Overview with NULLIF

1. Query with COALESCE Only

2. Using NULLIF with COALESCE for Improved Handling

Important Points About PostgreSQL NULLIF() Function

Conclusion

Similar Reads

Basics

Database Operations

Data Types

Querying Tables

Table Operations

Modifying Data

Conditionals

Control Flow

Transactions & Constraints

JOINS & Schemas