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...

  • One of the lacunae of PostgreSQL is the lack of calculated/virtual/persistent fields/columns - it's a real shame and one of the major advantages that Oracle (and even MariaDB, not to mention Firebird) have over PostgreSQL. You can implement a trigger to do the "dirty work" behind the scenes - not too complex. They really are a "nice to have" - using them for shadow (searching) columns is a great use for them. See here for ways of working around this missing feature! For you, SELECT (v1 + v2) AS v3, .... FROM tab; is OK! – Vérace Oct 16 '17 at 21:33
  • See also here: dba.stackexchange.com/a/188296/1822 – a_horse_with_no_name Oct 17 '17 at 7:27
  • seems a bit over kill to discuss virtual columns here. A simple new column with update and a trigger for future inserts would be more than enough. – VynlJunkie Oct 18 '17 at 10:55

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

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


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.


Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.