#Postgresql coalesce empty string how to#

Let’s see the SQL query:ĬOALESCE(subcategory, category, family, 'no product description ') And if both subcategory and category are NULL, we want to replace them with the family of the product. If the subcategory is NULL, we want to replace the subcategory with the category. Suppose we want a report that lists products and their subcategories.

You can use the COALESCE() function with more than two arguments. Notice the result: product_brandĮxample 3: Use COALESCE() with Multiple Arguments

When a brand is NULL, we will put a '-' instead of a NULL. Product || ', brand: ' || COALESCE(brand, '-') AS product_brand For example, suppose we want a list of the product names with the brand name. To avoid that, we can use the COALESCE() function to return an empty string (or a space) instead of a NULL. The result is NULL because every text string concatenation involving a NULL value returns a NULL value. SELECT 'Hello, how are you ' || null || '?' AS example SELECT 'Hello, how are you ' || 'Peter ' || '?' AS example If we want to concatenate two strings and one of them is NULL, the concatenation result will be NULL. Many operations involving NULL values return a NULL as a result. You can see the result below: productĮxample 2: Use COALESCE() When Concatenating NULL and StringsĪ frequent SQL issue related with NULL values is string concatenation. We’re using the COALESCE() function to replace NULL values with a text. Here’s the query we’d use:ĬOALESCE(subcategory,'No Subcategory') AS subcategory,ĬOALESCE(category,'No Category') AS category, For these products, we want to display a text: ‘ No Category’ or ‘ No Subcategory’. However, there are some products with a NULL in their category or subcategory. We want to show all the products with their subcategory, category, and family. Example 1: Use COALESCE() to Replace NULL with a Label

#Postgresql coalesce empty string how to#

Now, let’s see how to use the COALESCE() function in some realistic examples. Example SQL Queries Using COALESCE() Function The product “Tomatoes” belongs to the category “vegetables” and the family “food” it does not have a subcategory, so there is a NULL in this field. For example, the product “pork ribs” belongs to the subcategory “pork meat” in the category “meat” and the family “food”. You’ll note that some of the products have a subcategory, but others do not. This table stores product data records for a marketplace and includes the columns product, brand, subcategory, category, family, units, quantity_available (the current stock of this product), and minimum_to_have (the threshold when the market needs to order this product from their suppliers). Get to Know the Example Dataįor the rest of the article, we will demonstrate the COALESCE() function using the table stock, which is shown below. In other words, COALESCE() returns the first non-NULL argument.

When marital_status is not NULL, COALESCE() returns the value of the column marital_status. In the above query, the COALESCE() function is used to return the value ‘ Unknown’ only when marital_status is NULL. We can use the SQL COALESCE() function to replace the NULL value with a simple text: (Obviously, some columns will be mandatory (non-nullable), but this is set by the database designer, not the data type itself.) Let’s show a simple example using the table persons.

In SQL databases, any data type admits NULL as a valid value that is, any column can have a NULL value, regardless of what data type it is. It contains a comprehensive review of NULL values, NULL-related functions, and other common functions in SQL. Before starting on the technical details of NULL and COALESCE(), I suggest checking out our interactive course on Standard SQL Functions.