在PostgreSQL 9.6中更改某种数据类型的表中的所有列 [英] Change all columns in table of a certain data type in PostgreSQL 9.6

问题描述

似乎几个月前，我遇到了一个有关此问题的问题，但现在似乎找不到.

It seems like several months ago I came across a SO question covering this but I can't seem to find it now.

基本上，我想做两件事.

Basically, I want to do two things.

首先，用几列 numeric(20,2)制作了许多表，我只想将它们全部更改为 numeric .该语句对于一列很简单:

First, a number of tables were made with several columns numeric(20,2) and I want to just change them all to numeric. The statement is simple enough for one column:

ALTER TABLE table_name
ALTER COLUMN code
TYPE numeric;

照顾好这一点.

第二，在这些列上，我要删除任何结尾的零:

Second, on these columns I want to remove any trailing zeros:

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

很难弄清楚如何使其自动化，因此我只需要指定表即可清理表.十分确定这是可能的.

Having difficulty figuring out how to automate it so I only have to specify the table and it will clean up the table. Pretty sure this is possible.

推荐答案

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

You can find all of the columns with the data type that you want to change with a statement like:

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

您可以使用自定义函数或DO命令(例如:

You can iterate over the result with a custom function or with a DO command such as:

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$$;

此外，要删除结尾的零，一种更通用的解决方案是将值转换为浮点或双精度，然后再返回数字，例如:

Also, to remove trailing zeroes, a more general solution is to cast the value to float or double precision and then back to numeric, e.g:

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

这篇关于在PostgreSQL 9.6中更改某种数据类型的表中的所有列的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！