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:

Add a default value of false
Change all null values to false
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?

Kissaki · Accepted Answer · 2010-11-10 15:51:39Z

33

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

answered Nov 10, 2010 at 15:51

Kissaki

9,3665 gold badges44 silver badges46 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

Ian Mackinnon Over a year ago

Thanks. I know how to do it manually, but was just wondering if there was an iteration tool. I'll edit to make that clearer :) Also it's necessary to update null values to false before setting not null else you receive an error; setting the default does not cause automatic conversion on adding the not null.

Ian Mackinnon Over a year ago

Props for the multiple, comma-separated ALTER COLUMN commands though! - I never noticed that in the syntax before.

Frank Heikens · Accepted Answer · 2010-11-10 16:25:46Z

6

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"

answered Nov 10, 2010 at 16:25

Frank Heikens

130k26 gold badges158 silver badges153 bronze badges

1 Comment

Ian Mackinnon Over a year ago

Brilliant, thank you! This one piece of code is packed with useful stuff I didn't know about.

Collectives™ on Stack Overflow

PostgreSQL: how to efficiently alter multiple columns from psql?

2 Answers 2

2 Comments

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

2 Comments

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related