Postgres 9.6.5 - Can I use a query to calculate a value from two columns and insert the calculated value in a new column in the same table?

Question

I am running the following version of Postgres on CentOS 7:

PostgreSQL 9.6.5 on x86_64-pc-linux-gnu, compiled by gcc (GCC) 4.8.5 20150623 (Red Hat 4.8.5-11), 64-bit

I am new to database operations and need some help understanding if/how I should do the following:

I have a single table "Table1" that has three columns of data:

| timestamp | Value1 | Value2 |

The Value1 and Value2 columns contain decimal values like the below:

| 1.6732 | 2.78093 |

I have around a million rows of data in this table.

I would like to be able to deduct Value1 from Value2 and insert the result of this calculation in a new column in the same table, so that the table permanently stores the computed value and ends up looking like the below.

timestamp | Value1 | Value2 | Value3

I am not sure if I can or should do this, and if it is possible, how I would create a query to make this happen.

Please can anyone give any advice, examples or pointers on how I should go about doing this or point me in the right direction of how I can learn to do this myself?

Thank you...

See here for ways of working around this missing feature.

Vérace
– Vérace

2017-10-16 21:33:58 +00:00
Commented Oct 16, 2017 at 21:33 — Vérace
– Vérace, Commented Oct 16, 2017 at 21:33
See also here: dba.stackexchange.com/a/188296/1822

user1822
– user1822

2017-10-17 07:27:38 +00:00
Commented Oct 17, 2017 at 7:27 — user1822
– user1822, Commented Oct 17, 2017 at 7:27

Technology Smiths · Accepted Answer · 2017-10-17 16:06:01Z

4

I found the below which seems to have done the trick...

Firstly, I added the Value3 column to Table1 using the below SQL:

ALTER TABLE "Table1" ADD COLUMN Value3 NUMERIC;

Then I ran the below update and set statement:

UPDATE "Table1" SET Value3 = Value2 - Value1;

This updates the Value3 column with the result of Value2 - Value1.

edited Oct 17, 2017 at 16:06

answered Oct 16, 2017 at 23:29

Technology Smiths

711 silver badge6 bronze badges

Add a comment |

Stack Exchange Network

Postgres 9.6.5 - Can I use a query to calculate a value from two columns and insert the calculated value in a new column in the same table?

1 Answer 1

Your Answer

Linked

Hot Network Questions

Postgres 9.6.5 - Can I use a query to calculate a value from two columns and insert the calculated value in a new column in the same table?

1 Answer 1

Your Answer

Sign up or log in

Post as a guest

Linked

Related

Hot Network Questions