Skip to content
Related Articles

Related Articles

PostgreSQL – NULLIF() Function
  • Last Updated : 28 Aug, 2020
GeeksforGeeks - Summer Carnival Banner

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

Attention reader! Don’t stop learning now. Get hold of all the important DSA concepts with the DSA Self Paced Course at a student-friendly price and become industry ready.

My Personal Notes arrow_drop_up
Recommended Articles
Page :