Open In App

PostgreSQL – NULLIF() Function

Improve
Improve
Like Article
Like
Save
Share
Report

PostgreSQL has a NULLIF function to handle null values. The NULLIF function is one of the most common conditional expressions provided by PostgreSQL.

Syntax:NULLIF(argument_1,argument_2);

The NULLIF function returns a null value if argument_1 equals to argument_2, otherwise it returns argument_1.

Example :

First, we create a table named posts as follows:

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
);

Now we insert some sample data to the table as follows:

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');

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.

SELECT
    ID,
    title,
    excerpt
FROM
    posts;

This leads to the following:

nullif

The null value in the excerpt column. To substitute this null value, we can use the COALESCE function as follows:

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

This will result in the following:

Unfortunately, there is a mix between null value and ” (empty) in the excerpt column. This is why we need to use the NULLIF function:

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

Output:

nullif


Last Updated : 28 Aug, 2020
Like Article
Save Article
Previous
Next
Share your thoughts in the comments
Similar Reads