I have written the following query in postgresql:

select log10(x) as x
   , log10(y) as y
   , log10(z) as z
from table;

This changes the values in my table into log10's of their original values.

In the data output panel, everything looks fine, so I just have to add it to my table.

Is there a way to do this? I can't find anything that does what I want to accomplish?

I have tried ALTER TABLE just under the first code lines like this:

ALTER TABLE table
   add x numeric
   , add y numeric
   , add z numeric

I get this error:

ERROR: syntax error at or near "table"

Any other way to add the log10 columns to the original table?

CodePudding user response：

It seems like you already have columns x, y, and z. In that case, you will not be able to add additional columns with the same names. Instead, you must do one of three things:

1. UPDATE the table in place. No new column additions: just update the values of the columns you have. In this case, the original values are lost, and nothing is done to support new data as it arrives.
2. ALTER the table to add the new generated columns, but use different names. In this case, the new columns are redundant, because the existing columns already have everything you need to generate the values whenever you want them. And if the table is really named table, make sure to properly account for the fact "table" is a reserved word.
3. Create a View. The original table is unchanged, but now you have a View in the database you can SELECT from when you want to use the logarithmic values instead.

Of these, I find it likely option 3 is most appropriate, but without more details it's hard to know

CodePudding user response：

The following example works in sqlfiddle MySQL5.6:

create table yourtable (x numeric, y numeric);
insert into yourtable values (100,2000);
alter table yourtable add xx numeric, add yy numeric;
update yourtable set xx=log10(x), yy=log10(y);
select * from yourtable

returns:

You can also update the existing columns:

update yourtable set x=log10(x), y=log10(y);