Mass-Coalescing of Null Values

Question

I have a table in a Postgres database with monthly columns from 2012 to the end of 2018:

create table sales_data (
  part_number text not null,
  customer text not null,
  qty_2012_01 numeric,
  qty_2012_02 numeric,
  qty_2012_03 numeric,
  ...
  qty_2018_10 numeric,
  qty_2018_11 numeric,
  qty_2018_12 numeric,
  constraint sales_data_pk primary key (part_number, customer)
);

The data is populated from a large function that pulls data from an extremely wide variety of sources. It involves many left joins -- for example, in combining history with future data, where a single item may have history but not future demand or vice versa. Or, certain customers may not have data as far back or forward as we want.

The problem I'm coming up with is due to the left joins (and the nature of the data I'm pulling), a significant number of the values I am pulling are null. I would like any null to simply be zero to simplify any queries against this table, specifically aggregate functions that say 1 + null + 2 = null.

I could modify the function and add hundreds of coalesce statements. However, I was hoping there was another way around this, even if it means modifying the values after the fact. That said, this would mean adding 84 update statements at the end of the function:

update sales_data set qty_2012_01 = 0 where qty_2012_01 is null;
update sales_data set qty_2012_02 = 0 where qty_2012_02 is null;
update sales_data set qty_2012_03 = 0 where qty_2012_03 is null;
... 78 more like this...
update sales_data set qty_2018_10 = 0 where qty_2018_10 is null;
update sales_data set qty_2018_11 = 0 where qty_2018_11 is null;
update sales_data set qty_2018_12 = 0 where qty_2018_12 is null;

I'm missing something, right? Is there an easier way?

I was hoping the default setting on the column would force a zero, but it doesn't work when the function is explicitly telling it to insert a null. Likewise, if I make the column non-nullable, it just pukes on my insert -- I was hoping that might force the invocation of the default.

By the way, the insert-then-update strategy is one I chastise others for, so I understand this is less than ideal. This function is a bit of a beast, and it does require some occasional maintenance (long story). My primary goal is to keep the function as readable and maintainable as possible -- NOT to make the function uber-efficient. The table itself is not huge -- less than a million records after all is said and done -- and we run the function to populate it once or twice a month.

Answer 1

While INSERT statement itself you can COALESCE (col_name, 0) will fix the issue. You can add NOT NULL also to maintain data integrity .

Assuming Inserting data from Temp Table

INSERT INTO sales_data (qty_2012_01, qty_2012_02)
SELECT COALESCE(qty_2012_01, 0), COALESCE(qty_2012_01, 0)
FROM temp_sales_data;

Single Update

UPDATE sales_date SET
qty_2012_01 = COALESCE(qty_2012_01, 0),
qty_2012_02 = COALESCE(qty_2012_02, 0)
..
..
WHERE qty_2012_01 IS NULL 
OR qty_2012_02 IS NULL 
...
....

The above query will update all the columns in single update.

Answer 2

There is no built-in feature (I would know of). (Still true for Postgres 15.) Short of spelling out COALESCE(col, 0) everywhere you can write PL/pgSQL code generating the query string to replace all NULL values with 0 in all numeric columns of a table:

DO
$do$
DECLARE
   _sql text;
BEGIN
   SELECT INTO _sql
          'UPDATE public.sales_data'
    || E'\nSET   ('      || string_agg(col, ', ')                          || ')'
    || E'\n    = ('      || string_agg('COALESCE(' || col || ', 0)', ', ') || ')'
    || E'\nWHERE  NOT (' || string_agg(col, ', ')                          || ') IS NOT NULL'
   FROM  (
      SELECT quote_ident(attname) AS col
      FROM   pg_attribute
      WHERE  attrelid = 'public.sales_data'::regclass
      AND    attnum >= 1                     -- exclude system columns
      AND    NOT attisdropped                -- exclude dropped columns
      AND    NOT attnotnull                  -- exclude columns defined NOT NULL
      AND    atttypid = 'numeric'::regtype   -- only numeric columns (?)
      ORDER  BY attnum
      ) sub;

   IF _sql IS NULL THEN
      RAISE WARNING 'No numeric column found in table "sales_data"!';
   ELSE
      -- RAISE NOTICE '%',  _sql;            -- test output for debugging
      EXECUTE _sql;                          -- payload
   END IF;
END
$do$;

fiddle
_{Old sqlfiddle}

Concatenates and executes a query of the form:

UPDATE sales_data
SET   (qty_2012_01, qty_2012_02, qty_2012_03)
    = (COALESCE(qty_2012_01, 0), COALESCE(qty_2012_02, 0), COALESCE(qty_2012_03, 0))
WHERE  NOT (qty_2012_01, qty_2012_02, qty_2012_03) IS NOT NULL

The added WHERE clause prevents updates that would not change anything (at normal cost). The odd syntax NOT ((x,y) IS NOT NULL) identifies rows with at least one null value. Why this form? See:

• Why is IS NOT NULL false when checking a row type?
• IS NOT NULL test for a record does not return TRUE when variable is set

I attached a demo to the fiddle.

Works for any table with any column names. All numeric columns are updated. Only rows that actually change are touched.

Since the function is invasive, I added a child-safety device. Quote the RAISE NOTICE line and unquote EXECUTE to prime the bomb.

Call:

SELECT f_convert_numeric_null('sales_data');

Write your query results to a temporary table, run the function on the temp table and then INSERT into the actual table.

Related:

• Replace empty strings with null values
• Table name as a PostgreSQL function parameter
• Generate DEFAULT values in a CTE UPSERT using PostgreSQL 9.3