PostgreSQL 9.6.5 – Calculate and Insert Value from Two Columns

computed-columnpostgresql

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…

Best Answer

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.

Related Solutions

Postgresql – Postgres Index scan forward vs backward = speed difference of 357X slower

Since I like replacing aggregate functions by old-fashioned self-joins and NOT EXISTS clauses, here is my attempt:

SET search_path='tmp';

DROP TABLE tmp.changes CASCADE;
CREATE TABLE tmp.changes
        ( id integer NOT NULL PRIMARY KEY
        , fullname varchar
        , issuer varchar
        , rsymbol varchar
        , industry varchar
        , activity INTEGER NOT NULL
        , shareschange FLOAT
        , sharespchange FLOAT
        , mfiled FLOAT
        );

        -- lacking information from the OP
        -- I can only presume a flat distribution.
INSERT INTO tmp.changes(id, activity, shareschange,sharespchange,mfiled )
SELECT nm.*
        , (random() *20)::integer -- mfiled
        , random() *10000
        , random() *100
        , random() *100000
FROM generate_series(1,1000000) nm
        ;

ALTER TABLE tmp.changes
        ALTER shareschange
        SET STATISTICS 1000
        ;
ALTER TABLE tmp.changes
        ALTER mfiled
        SET STATISTICS 1000
        ;

VACUUM ANALYZE tmp.changes
        ;


CREATE INDEX changes_mfiled_shareschange
    ON tmp.changes(mfiled,shareschange)
        ;

EXPLAIN ANALYZE
SELECT initcap(ch.fullname) AS some_name1
     , initcap(ch.issuer) AS some_name2
     , upper(ch.rsymbol) AS some_name3
     , initcap(ch.industry) AS some_name4
     , ch.activity
     , to_char(ch.shareschange,'FM9,999,999,999,999,999') AS some_name5
     , ch.sharespchange || '%' AS some_name6
FROM   changes ch
WHERE  ch.activity IN (4,5)
        -- NOTE: the subquery is *not* correlated.
        -- [I had expected a subselect of nx.activity IN (4,5)
        -- like in the main query. ]
AND    NOT EXISTS (SELECT * FROM changes nx
        WHERE nx.mfiled > ch.mfiled
        )
ORDER  BY ch.shareschange ASC
LIMIT  15
        ;

PostgreSQL – How to Install pgcrypto Extension on CentOS 7

From the docs on CREATE EXTENSION

PostgreSQL will create the extension using details from the file SHAREDIR/extension/extension_name.control.

You need to check what SHAREDIR is set to with pg_config --sharedir. If that's not set to /usr/share/pgsql/extension then likely your postgresql-contrib was packaged by someone different than your copy of postgresql. These are compile time variables and they should be the same. Moving them in likely won't work either because if these are packaged by two separate entities they're likely built against different libraries.

CentOS

In your case I assume

You're installing the PostgreSQL from the PostgreSQL repos.
You're installing the -contrib from the CentOS repos.

What you probably want to do is install from the CentOS repos. Uninstall all things related to PostgreSQL, and delete those files you copied or linked and run this

# Adds the PostgreSQL repo
rpm -Uvh https://yum.postgresql.org/10/redhat/rhel-7-x86_64/pgdg-centos10-10-2.noarch.rpm

# I think this should install them explicitly from the PostgreSQL repo
rpm -Uvh https://yum.postgresql.org/10/redhat/rhel-7-x86_64/postgresql10-10.1-1PGDG.rhel7.x86_64.rpm
rpm -Uvh https://yum.postgresql.org/10/redhat/rhel-7-x86_64/postgresql10-contrib-10.1-1PGDG.rhel7.x86_64.rpm

Best Answer

Related Solutions

Postgresql – Postgres Index scan forward vs backward = speed difference of 357X slower

PostgreSQL – How to Install pgcrypto Extension on CentOS 7

CentOS

Related Question