---
title: "Understanding PostgreSQL's COALESCE Function"
description: "Read what the PostgreSQL’s COALESCE function does, how to use it, and practical usage examples. A practical guide for PostgreSQL and TimescaleDB developers."
section: "Postgres basics"
---

> **TimescaleDB is now Tiger Data.**

*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](https://www.timescale.com/blog/storing-iot-data-8-reasons-why-you-should-use-postgresql), 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)
`

### 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
);
`

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
`

## 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;
`

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
`

## Combining COALESCE With Window Functions

`COALESCE` is particularly powerful when combined with [window functions](https://www.timescale.com/learn/postgresql-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;
`

## 
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;
`

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
`



## 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);
`

## 
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](https://www.tigerdata.com/blog/time-series-introduction) 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](https://www.postgresql.org/docs/current/functions-conditional.html) and window functions. To learn more about other [PostgreSQL functions, visit our dedicated section](https://www.timescale.com/learn/postgres-basics#:~:text=Postgres%20functions,-Understanding%20PostgreSQL%20Functions).

For high-performance IoT data storage and analysis with automatic data partitioning and real-time aggregation capabilities, [create a free Timescale account today](https://console.cloud.timescale.com/signup). [Self-hosting is also an option](https://docs.timescale.com/self-hosted/latest/install/).