PostgreSQL – NULLIF() Function
PostgreSQL has a NULLIF function to handle null values. The NULLIF function is one of the most common conditional expressions provided by PostgreSQL.
The NULLIF function returns a null value if argument_1 equals to argument_2, otherwise it returns argument_1.
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:
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;