CODEWITHSUNDEEP
sqlpostgresql
khajlk
khajlk

Reputation: 861

PostgreSQL: How do I multiply every entry in multiple columns by a fixed number?

Okay, my question is similar to this but my case is different. In my PostgreSQL 9.5 database, I have a table my_table having a layout like follows:

ID   a0   a1 ..   a23   b0   b1   ..   b23   c0   c1 ..   c23
1    23   22 ..   11    12   0.12 ..   65   0.17  12 ..   19
2    42   52 ..   12    1.2  14   ..   42   0.35  12 ..   12
3    11   25 ..   13    2.5  0.14 ..   15   1.1   8  ..   14

First column ID (integer) is unique for each record while there are 24 columns (numeric) for each variable a, b and c thus summing up to 72 columns. I want to multiply each entry in these 72 columns to a fixed number, let say 0.20. I am aware of PostgreSQL UPDATE command like this:

UPDATE my_table set a0 = a0 * 0.20

In this case, I would need to repeat this command a large number of times (undesired). Is there an alternate quick approach (single SELECT or iteration) to multiply a fixed number to multiple columns?

Upvotes: 2

Views: 2188

Answers (1)

klin
klin

Reputation: 121754

Example table:

drop table if exists my_table;
create table my_table(id serial primary key, a1 dec, a2 dec, a3 dec);
insert into my_table values
(default, 1, 2, 3);

Use execute in an anonymous code block:

do $$
begin
    execute concat('update my_table set ', string_agg(format('%1$I = %1$I * 0.2', attname), ','))
    from pg_attribute a
    where attrelid = 'my_table'::regclass
    and attnum > 0
    and attname ~ '^[abc]+';
end
$$;

select * from my_table;

 id | a1  | a2  | a3  
----+-----+-----+-----
  1 | 0.2 | 0.4 | 0.6
(1 row)

Upvotes: 1

Related Questions