Handling NULL values is one of those fundamental PostgreSQL skills that shows up constantly in data warehouse pipelines, reporting queries, and ETL transformations. We'll walk through the core functions and patterns, explain when to use each, and touch on how this fits into a real warehouse context.

Understanding What NULL Actually Means

Before filling NULLs, let's be precise about what they represent. NULL is not zero, not an empty string, and not false. It means unknown or absent. That distinction matters because filling a NULL with a default value is a semantic decision, not just a technical one. In a data warehouse, blindly replacing NULLs can corrupt aggregations, so always ask: why is this value missing?

Using COALESCE: The Workhorse

The most common function is COALESCE, which returns the first non-NULL value from a list of arguments. It short-circuits, meaning it stops evaluating once it finds a non-NULL value.

SELECT
  order_id,
  customer_id,
  COALESCE(discount_amount, 0)          AS discount_amount,
  COALESCE(promo_code, 'NO_PROMO')      AS promo_code,
  COALESCE(delivery_notes, notes, 'N/A') AS notes_filled
FROM orders;

Notice the third example chains multiple columns. This is useful when you have a priority hierarchy: try delivery_notes first, fall back to notes, and finally default to a string literal.

Using NULLIF: The Reverse Operation

Sometimes the problem is the opposite: you have a placeholder value like 0 or 'unknown' that should actually be treated as NULL. NULLIF(value, compare) returns NULL when the two arguments match.

SELECT
  product_id,
  NULLIF(stock_count, -1) AS stock_count,   -- -1 was used as a sentinel
  COALESCE(NULLIF(region, 'UNKNOWN'), 'EMEA') AS region
FROM inventory;

Combining NULLIF with COALESCE like the second column above is a very common pattern in dbt models when cleaning raw source data.

Using CASE WHEN for Conditional Logic

When the fill logic depends on other columns, CASE WHEN gives you full control.

SELECT
  user_id,
  signup_channel,
  CASE
    WHEN revenue IS NULL AND trial_expired = true  THEN 0
    WHEN revenue IS NULL AND trial_expired = false THEN -1   -- still in trial
    ELSE revenue
  END AS revenue_filled
FROM user_metrics;

This is cleaner than nesting multiple COALESCE calls when the business logic has meaningful branches.

Forward-Fill and Backward-Fill with Window Functions

In time-series tables (think daily metrics, sensor readings, or slowly changing dimensions), a NULL often means "same as the previous row." This is called a forward-fill or LOCF (Last Observation Carried Forward). PostgreSQL does not have a built-in forward-fill function, but we can construct one.

-- Step 1: Assign a group ID that increments only on non-NULL values
WITH grouped AS (
  SELECT
    metric_date,
    value,
    COUNT(value) OVER (ORDER BY metric_date) AS fill_group
  FROM daily_metrics
)
-- Step 2: Take the MAX (which is the single non-NULL value) within each group
SELECT
  metric_date,
  MAX(value) OVER (PARTITION BY fill_group ORDER BY metric_date) AS value_filled
FROM grouped
ORDER BY metric_date;

The trick here is that COUNT(value) ignores NULLs, so the fill_group number only advances when a real value appears. Everything between two real values shares the same group number, and MAX then broadcasts that real value across the group.

Permanently Updating NULL Values in the Table

All the examples above work at query time. To permanently replace NULLs in the table itself, use UPDATE.

-- Simple constant fill
UPDATE customers
SET country = 'Unknown'
WHERE country IS NULL;

-- Fill from another column in the same row
UPDATE orders
SET billing_address = shipping_address
WHERE billing_address IS NULL
  AND shipping_address IS NOT NULL;

In a warehouse context, be careful here. Mutating raw or staging tables is generally discouraged. The pattern in tools like dbt is to keep raw data immutable and apply fills in a transformation layer, typically a staging model. That way the lineage is auditable.

Setting Column Defaults to Prevent NULLs at Ingestion

If the goal is to stop NULLs from entering the table in the first place, a column default is the right tool.

ALTER TABLE products
ALTER COLUMN is_active SET DEFAULT true;

-- Or at table creation time
CREATE TABLE events (
  event_id      BIGSERIAL PRIMARY KEY,
  event_type    TEXT      NOT NULL,
  payload       JSONB     DEFAULT '{}'::JSONB,
  processed_at  TIMESTAMPTZ DEFAULT NOW()
);

Note that DEFAULT only applies to new inserts. Existing NULL rows are unaffected unless you run an UPDATE afterward.

Warehouse Context: Where This Shows Up in Practice

Let's connect this to real warehouse tooling:

dbt staging models: Use COALESCE and NULLIF in your stg_ models to normalize raw source data before it hits your dimensional model.
Fact tables: Numeric measure columns (revenue, quantity, duration) should almost always have NULLs replaced with 0 via COALESCE so that SUM aggregations behave predictably.
Dimension tables with SCDs: In a Type 2 slowly changing dimension, NULL in valid_to is intentional and means "current row." Do not fill those.
BigQuery and Snowflake: Both support COALESCE and CASE WHEN identically to PostgreSQL. Snowflake also offers NVL and NVL2 as Oracle-compatible aliases for COALESCE.

A Quick Note on Performance

The COALESCE function is extremely cheap in PostgreSQL and adds no meaningful overhead. Window-based forward-fills on large tables are more expensive because they require a sort. If you are running forward-fills frequently in a production warehouse, materializing the result into a new table or a materialized view is worth considering rather than recomputing the window function on every query.

2725. Filling Missing Values PostgreSQL

Similar Questions