PostgreSQL Coalesce: Removing Null Values

Most databases have the ability to store the “null” value. The null is not “nothing” in terms of programming, but the null value indicates that no real value is stored in the database table field. However, nulls can cause issues if you’re trying to add values or find real values in your tables. The SQL language includes some functions that help you remove null values from your data sets. The coalesce function is one function that gets a data set and finds the first record that doesn’t contain a null in the column you indicate.

Working with PostgreSQL in your applications. Learn how now.

Finding Null Values

PostgreSQL lets you store default values in your database tables. Most programmers allow nulls if no value is indicated when you insert a record. Allowing nulls makes it easier for you to insert only the necessary data when you write your “INSERT” queries. However, some programmers prefer to store a blank string instead of null. The issue with this design is that you can’t use the null functions to find blank entries.

You can run a simple query to find out if you have any nulls in your tables. For instance, the following query tells you if you have any customers in your customers table with a first name set to null:

select * from customers where first_name is null;

Notice that the “is null” parameter is used and not “equals null.” If you use equals null, none of your columns will display. A value won’t equal null, so the SQL language uses the “is null” syntax. In the above statement, any records with a null in the first_name is returned, but what if you want to return a data set that only has columns where the first name is filled out? You can perform the opposite type of query. The following query finds all records where the first name has a value:

select * from customers where first_name is not null;

Using Coalesce in Your PostgreSQL Queries

Let’s say you want to use calculations within your queries, so you can’t run separate queries to perform your calculations. If you try to calculate records with nulls, it will throw an error. The following query is a basic statement that gets values and replaces any null values with zero:

select coalesce(amount, 0) from orders order by customer;

The above statement replaces null with the number zero. The purpose is to eliminate nulls. In some database tables, you might store nulls in values such as “amount.” While this isn’t usually preferred in most database designs, you can’t always control what other programmers will design on your database. The coalesce statement ensures that when you return the “amount” to the software, the data set contains zeros.

Learn good table design at Udemy.com

You can also use the coalesce statement with calculations in the database. For instance, you might want to calculate commission for sales against the “amount” column in your database tables. The following code allows you to perform the calculation without worrying about nulls throwing an error from your application:

select coalesce(amount, 0)* .06 from orders order by customer;

The statement above multiplies .06 against the “amount” column. If you didn’t have the coalesce statement, you could run into issues with the null values. With the above statement, all nulls are replaced by zeros, so your calculation will simply return zero.

You can also use coalesce to return the first non-null value in a list of values. For instance, if you have 6 values and you want to find the first value that isn’t null, you can use the following statement:

SELECT COALESCE(null, null, 5, 3, 1, 9);

The above statement returns 5 when you run it in your queries. You can also use this with columns in a database. For instance, the following code would return the first non-null value in a list of columns in a pricing table:

SELECT COALESCE(amount, price, cost) from PRODUCTPRICING;

Using CASE Statements Instead of Coalesce

Although not as efficient, you can also use the PostgreSQL “CASE” statement to eliminate null values. The following code is an example of a PostgreSQL query statement that performs a similar process as the coalesce statement:

SELECT CASE WHEN amount IS NULL THEN 0 ELSE amount END AS amount_alias;

The above statement says “when the amount column is null, return zero. If the amount column is not null, return the actual amount.” For instance, if you have two records and one record has an amount of “6” and the other has the null value, your record set will return 6 and 0.

Learn how to work with the SQL language for all databases

You can also use this type of statement for calculation. Just like the previous example, you can use this CASE statement to make calculations. For instance, if you want to multiply a 6% commission against the amount column, the following is an example of the query you could write:

SELECT CASE WHEN amount IS NULL THEN 0 ELSE amount*.06 END AS amount_alias;

In both of the last two statements, the results are shown in the column “amount_alias” in your data set. Incidentally, other SQL languages have an “isnull” function that performs the same action as coalesce. The PostgreSQL language does not have an ‘isnull’ function.

The coalesce statement is one of the most powerful functions in PostgreSQL, because it ensures that your calculations and results are not filled with null values, which can cause errors in your applications. You can insert this function into any of your PostgreSQL functions.

Learn SQL in six days and code with the experts