PostgreSQL Warning: Null Value Eliminated in Set Function (SQLSTATE 01003)

When PostgreSQL silently discards NULL values during an aggregate operation, it emits a warning with SQLSTATE 01003 and condition name null_value_eliminated_in_set_function:

WARNING:  null value eliminated from aggregate

This warning appears in server logs and is surfaced to clients when they call set (aggregate) functions — such as SUM, AVG, MIN, MAX, or COUNT(col) — on a column or expression that contains one or more NULL values.

What This Error Means

SQLSTATE 01003 belongs to class 01 (Warning). Unlike class 02 (No Data) or class 22 (Data Exception), class 01 warnings do not abort the current statement or transaction. The query completes successfully and returns a result; PostgreSQL is informing you that the result may not be what you intended because NULLs were silently ignored.

According to the SQL standard, aggregate functions other than COUNT(*) skip NULL values in their input set. PostgreSQL follows this standard, but it has the option to emit a warning when this happens — particularly when the aggregate is applied to a set that includes NULLs. In practice, most PostgreSQL client drivers receive this warning and either log it, surface it as a notice-level message, or suppress it entirely depending on their configuration.

The connection and transaction remain fully intact after this warning. Any result returned is valid SQL-standard behavior; the warning is purely advisory.

Common Causes

1. Aggregating a nullable column directly. Running SELECT AVG(response_time) FROM requests on a column that allows NULLs will trigger this warning whenever any row has a NULL response_time. The NULLs are excluded from the average calculation without any error.

2. Using COUNT(column) instead of COUNT(*). COUNT(col) counts non-NULL values only; NULL rows are silently dropped. If you expect COUNT(col) to equal COUNT(*), this warning signals a mismatch.

3. NULLs introduced by a LEFT JOIN or OUTER JOIN. Joining tables and then aggregating the outer side often produces NULLs for unmatched rows. Aggregating these NULL-bearing columns triggers the warning.

4. Computed expressions that return NULL. Expressions like col1 / col2 return NULL when col2 is zero (if you've used NULLIF or similar), and aggregating such a column will produce this warning.

5. Partial NULL columns from optional data. Tables where certain fields are only populated under specific conditions (e.g., discount_amount only set on discounted orders) commonly trigger this warning when aggregated across all rows.

How to Fix null_value_eliminated_in_set_function

1. Filter out NULLs explicitly before aggregating. Add a WHERE col IS NOT NULL clause to restrict the input set:

   SELECT AVG(response_time)
   FROM requests
   WHERE response_time IS NOT NULL;
   

2. Use COALESCE to substitute a default value for NULLs. If treating NULL as zero (or another sentinel) is semantically correct for your use case, replace the NULL inline:

   SELECT SUM(COALESCE(discount_amount, 0))
   FROM orders;
   

3. Use COUNT(*) when you want to count all rows regardless of column values. If NULLs should count as data points (i.e., "the row exists"), switch from COUNT(col) to COUNT(*):

   -- Counts only rows where status IS NOT NULL
   SELECT COUNT(status) FROM events;
   
   -- Counts every row
   SELECT COUNT(*) FROM events;
   

4. Handle NULLs from outer joins before aggregating. When joining and aggregating, be explicit about which side can be NULL:

   SELECT u.id, COALESCE(SUM(o.total), 0) AS lifetime_value
   FROM users u
   LEFT JOIN orders o ON o.user_id = u.id
   GROUP BY u.id;
   

5. Suppress the warning if the behavior is intentional. If you understand the SQL standard behavior and the warning is noise in your logs, you can suppress it at the session level:

   SET client_min_messages = 'error';
   

   This prevents warnings from being sent to the client. Use with care — it also suppresses other warnings.

Additional Information

• SQLSTATE class 01 (Warning) is defined in the SQL standard; 01003 specifically maps to the condition null_value_eliminated_in_set_function. PostgreSQL has supported this warning since at least version 7.4.
• Related SQLSTATE codes in the same class include 01000 (Warning, generic), 01007 (privilege not granted), and 01006 (privilege not revoked).
• Many PostgreSQL client drivers (psycopg2, JDBC, node-postgres) expose this as a notice or warning object rather than raising an exception. Check your driver's warning-handling API if you need to programmatically detect it.
• ORMs like SQLAlchemy, Django ORM, and ActiveRecord typically do not raise Python/Ruby exceptions for SQLSTATE 01 warnings; they may log them at the DEBUG level or discard them. Inspect your ORM's underlying connection event listeners if you want to capture these.
• There is no performance impact from this warning itself. The aggregate function executes normally; the warning is purely a diagnostic message.

Frequently Asked Questions

Why does my query return a result instead of an error if something was eliminated? NULL elimination in aggregates is standard SQL behavior. The result is still a valid aggregate over the non-NULL values in the input. PostgreSQL raises a warning — not an error — because the query completed successfully. Whether the result is semantically correct depends on your application's intent.

Will SQLSTATE 01003 roll back my transaction? No. Class 01 is a warning class, not an error class. The statement completes, the transaction remains open, and no rollback occurs. You can continue issuing statements on the same connection.

COUNT(col) and COUNT(*) returned different numbers — is that the 01003 warning? Yes, exactly. COUNT(col) skips NULLs; COUNT(*) does not. If those numbers differ, your column contains NULLs. The warning is PostgreSQL telling you the two counts will disagree. Use COUNT(*) for row counts and COUNT(col) only when you specifically want to count non-NULL values.

My application never sees this warning even though the logs show it. Why? PostgreSQL sends warnings to the client at the WARNING message severity level. Many drivers filter by client_min_messages, and some connection pool configurations suppress all notices and warnings before they reach application code. Check your driver's notice handler or connection parameters (e.g., options=-c client_min_messages=warning in the connection string).