PostgreSQL: how to efficiently alter multiple columns from psql?

Question

I have PostgreSQL table with several boolean columns, currently containing only true or null. I want to do the following for all of them:

1. Add a default value of false
2. Change all null values to false
3. Add a not null constraint

ie.:

-- for each column specified:
update my_table set my_column = 'f' where my_column is null;
alter table my_table alter column my_column set default 'f';
alter table my_table alter column my_column set not null; 

Is there a feature of psql (or standard SQL) that will iterate over a specified list of columns and apply a sequence of operations to each one?

Answer 1

You can not iterate over all columns, but to be safe you probably don’t want to do that anyway but specify which ones to alter yourself. Another way would be to do a script querying for the column names and then altering them.

To alter them you use ALTER TABLE. See the PgSQL doc: http://www.postgresql.org/docs/8.4/static/sql-altertable.html

ALTER TABLE xy ALTER COLUMN a SET DEFAULT FALSE, ALTER COLUMN b SET NOT NULL

etc

Answer 2

This will do, needs version 8.4 or higher because of the VARIADIC.

CREATE OR REPLACE FUNCTION setdefaults(
    IN _tname TEXT,     -- tablename to alter
    VARIADIC _cname TEXT[]  -- all columnnames to alter
) 
RETURNS boolean 
LANGUAGE plpgsql 
AS
$$
DECLARE
    row record;
BEGIN   
    FOR row IN SELECT unnest(_cname) AS colname LOOP
        EXECUTE 'ALTER TABLE ' || quote_ident(_tname) || ' ALTER COLUMN ' || quote_ident(row.colname) || ' SET DEFAULT false;';
        EXECUTE 'UPDATE ' || quote_ident(_tname) || ' SET ' || quote_ident(row.colname) || ' = DEFAULT WHERE ' || quote_ident(row.colname) || ' IS NULL;';
        EXECUTE 'ALTER TABLE ' || quote_ident(_tname) || ' ALTER COLUMN ' || quote_ident(row.colname) || ' SET NOT NULL;';
    END LOOP;

    RETURN TRUE;
END;
$$;

SELECT setdefaults('foo', 'x','y','z'); -- alter table "foo"