Is there a way to update all values in a PostgreSQL table that are null with 0

Question

I have a large table that doesn't follow the best data organization practices so I can't do a simple

update table set (...cols) = 0 where (...cols) is null

because the columns names are the months of the year dash a year up to 5 years. i.e Jan-20, Jan-21, Jan-22, etc.

I'm wondering if there's a command out there that would essentially do

update table set all data = 0 where data is null

I understand this goes against SQL principles but my hope is that there's something out there that's does what I would expect that command to do.

Marth · Accepted Answer · 2020-05-29 19:14:50Z

There is no pure-SQL way to do that (aside from listing all the columns manually).
You can use pl/pgsql:

DO
$$
    DECLARE
        schema_ TEXT := 'public';
        table_ TEXT := 't';
        column_list TEXT;
        coalesced_list TEXT;
    BEGIN
        -- Build up 2 strings containing
        --   (col1, col2, col3, ....)
        --   (COALESCE(col1, 0), COALESCE(col2, 0), ...)
        SELECT
            '(' || string_agg(quote_ident(column_name), ', ') || ')',
            '(' || string_agg('COALESCE(' || quote_ident(column_name) || ', 0)', ',') || ')'
        FROM information_schema.columns
        WHERE table_name = table_
          AND table_schema = schema_
        INTO column_list, coalesced_list
        ;
        -- Update the table, settings columns (c1, c2, ...) to (COALESCE(c1, 0), COALESCE(c2, 0), ...)
        -- changing all null values to zeroes
        EXECUTE format($query$
            UPDATE %I.%I
            SET %s = %s
        $query$, schema_, table_, column_list, coalesced_list);
    END
$$;

Collectives™ on Stack Overflow

Is there a way to update all values in a PostgreSQL table that are null with 0

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related