PostgreSQL：如何有效地更改 psql 中的多个列？
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/4146138/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
PostgreSQL: how to efficiently alter multiple columns from psql?
提问by Ian Mackinnon
I have PostgreSQL table with several boolean columns, currently containing only true or null. I want to do the following for all of them:
我有几个布尔列的 PostgreSQL 表，目前只包含 true 或 null。我想为他们所有人做以下事情：
- Add a default value of false
- Change all null values to false
- Add a
not nullconstraint
- 添加默认值 false
- 将所有空值更改为 false
- 添加
not null约束
ie.:
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?
是否有 psql（或标准 SQL）的特性可以迭代指定的列列表并对每个列应用一系列操作？
采纳答案by Frank Heikens
This will do, needs version 8.4 or higher because of the VARIADIC.
这样做可以，因为 VARIADIC 需要 8.4 或更高版本。
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"
回答by Kissaki
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. 请参阅 PgSQL 文档：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
等等
