sql - 在 PostgreSQL 9.6 中更改表中某种数据类型的所有列

Question

好像几个月前我遇到了一个关于这个的 SO 问题，但我现在似乎找不到它。

基本上，我想做两件事。

首先，用几列 numeric(20,2) 制作了一些表格，我只想将它们全部更改为 numeric。该语句对于一列来说足够简单:

ALTER TABLE table_name
ALTER COLUMN code
TYPE numeric;

负责处理。

其次，在这些列上，我想删除所有尾随零:

UPDATE table_name
SET code = replace(replace(code::text, '.50', '.5'), '.00', '')::numeric;

很难弄清楚如何使其自动化，所以我只需要指定表格，它就会清理表格。很确定这是可能的。

Answer 1

您可以使用如下语句找到所有具有您想要更改的数据类型的列:

select column_name, table_name
from information_schema.columns
where data_type='numeric'
    and numeric_precision = 20
    and numeric_scale = 2;

您可以使用自定义函数或 DO 命令迭代结果，例如:

do $$
declare
t record;
begin
    for t IN select column_name, table_name
            from information_schema.columns
            where data_type='numeric'
                and numeric_precision = 20
                and numeric_scale = 2;
    loop
        execute 'alter table ' || t.table_name || ' alter column ' || t.column_name || ' type numeric';
    end loop;
end$$;

此外，要删除尾随零，更通用的解决方案是将值转换为 float 或 double ，然后再转换回数字，例如:

set code = cast(cast(code as double precision) as numeric);