Published at Jan 31, 2025

Understanding PostgreSQL's COALESCE Function

Written by Team Timescale

In PostgreSQL and TimescaleDB, the COALESCE function provides a powerful way to handle NULL values in your queries. This is particularly valuable when working with IoT sensor data, where missing readings, disconnected devices, or transmission errors can lead to gaps in your data.

The COALESCE function evaluates arguments in order and returns the first non-NULL value. If all arguments are NULL, the function returns NULL. This makes it especially useful for providing default values, handling missing sensor readings, or cleaning up data for visualization.

COALESCE Function Syntax

COALESCE(value1, value2, ..., valueN) Copy

Examples

Let's look at some examples using a table called sensor_readings that contains temperature and humidity data from multiple IoT sensors:

CREATE TABLE sensor_readings (     time TIMESTAMPTZ NOT NULL,     sensor_id TEXT NOT NULL,     temperature FLOAT,     humidity FLOAT,     battery_level FLOAT ); Copy

Here's our sample data:

time                    | sensor_id | temperature | humidity | battery_level ------------------------+-----------+-------------+----------+-------------- 2024-01-01 00:00:00+00 | sensor_1  | 22.5       | 45.2     | 98.5 2024-01-01 00:00:00+00 | sensor_2  | NULL       | 44.8     | 87.3 2024-01-01 00:00:00+00 | sensor_3  | 23.1       | NULL     | 92.1 2024-01-01 00:00:00+00 | sensor_4  | NULL       | NULL     | 15.2 2024-01-01 00:00:00+00 | sensor_5  | 21.8       | 46.3     | NULL Copy

Handling Missing Sensor Readings

One common use case is to replace NULL values with a default or previous reading. Here's how to use COALESCE to provide a default value:

SELECT      time,     sensor_id,     COALESCE(temperature, 20.0) as temperature,     COALESCE(humidity, 50.0) as humidity,     COALESCE(battery_level, 0.0) as battery_level FROM sensor_readings; Copy

Results:

time                    | sensor_id | temperature | humidity | battery_level ------------------------+-----------+-------------+----------+-------------- 2024-01-01 00:00:00+00 | sensor_1  | 22.5       | 45.2     | 98.5 2024-01-01 00:00:00+00 | sensor_2  | 20.0       | 44.8     | 87.3 2024-01-01 00:00:00+00 | sensor_3  | 23.1       | 50.0     | 92.1 2024-01-01 00:00:00+00 | sensor_4  | 20.0       | 50.0     | 15.2 2024-01-01 00:00:00+00 | sensor_5  | 21.8       | 46.3     | 0.0 Copy

Combining COALESCE With Window Functions

COALESCE is particularly powerful when combined with window functions. Here's how to fill in missing values with the last known reading from each sensor:

SELECT      time,     sensor_id,     COALESCE(         temperature,         LAG(temperature) OVER (PARTITION BY sensor_id ORDER BY time)     ) as temperature,     COALESCE(         humidity,         LAG(humidity) OVER (PARTITION BY sensor_id ORDER BY time)     ) as humidity FROM sensor_readings; Copy

Using COALESCE for IoT Status Reporting

COALESCE can help create meaningful status messages for sensor health monitoring:

SELECT      sensor_id,     COALESCE(         CASE              WHEN battery_level < 20 THEN 'Low Battery'             WHEN temperature IS NULL THEN 'Temperature Sensor Failure'             WHEN humidity IS NULL THEN 'Humidity Sensor Failure'             ELSE 'OK'         END,         'Unknown Status'     ) as sensor_status FROM sensor_readings; Copy

Results:

sensor_id | sensor_status ----------+--------------- sensor_1  | OK sensor_2  | Temperature Sensor Failure sensor_3  | Humidity Sensor Failure sensor_4  | Low Battery sensor_5  | OK Copy

Multiple Fallback Values

COALESCE can evaluate multiple fallback options in order:

SELECT      sensor_id,     COALESCE(         temperature,                                          -- Current reading         LAG(temperature) OVER w,                             -- Previous reading         LEAD(temperature) OVER w,                            -- Next reading         AVG(temperature) OVER w,                             -- Average reading         20.0                                                 -- Default value     ) as normalized_temperature FROM sensor_readings WINDOW w AS (PARTITION BY sensor_id ORDER BY time             ROWS BETWEEN 1 PRECEDING AND 1 FOLLOWING); Copy

Performance Considerations

COALESCE evaluates its arguments in order and stops at the first non-NULL value. For optimal performance:

1. Order your arguments from most likely to least likely to be non-NULL

2. Minimize the number of arguments when possible

3. Consider using an index on columns frequently used in COALESCE operations

Common use cases in IoT

• Providing default values for missing sensor readings

• Handling device disconnections gracefully

• Creating complete time series for visualization

• Generating status reports and alerts

• Data cleaning and preprocessing

• Failover to backup sensors

Next Steps

To learn more about handling NULL values and working with time-series data in PostgreSQL, check out PostgreSQL's documentation on conditional expressions and window functions. To learn more about other PostgreSQL functions, visit our dedicated section.

For high-performance IoT data storage and analysis with automatic data partitioning and real-time aggregation capabilities, create a free Timescale account today. Self-hosting is also an option.