PostgreSQL – Insert Sum of Row Data into Separate Column

postgresql

I have a table

   id   name     class  social  science math  Total
    2   Max      Three  85        56     85
    3   Arnold   Three  55        40     75
    4   Krish    Four   60        50     70
    5   John     Four   60        80     90

I have to find total of each student's marks and insert to column Total.

While running this query –

INSERT INTO student(Total)    
SELECT (social + math + science ) as Total from student;

I am getting in this format –

   id   name     class  social  science math  Total
    2   Max      Three  85        56     85
    3   Arnold   Three  55        40     75
    4   Krish    Four   60        50     70
    5   John     Four   60        80     90
    6                                          226
    7                                          170
    8                                          180
    9                                          230

How to correct this I am not getting.

Best Answer

Use an UPDATE statement.

update student 
   set total = social + math + science;

But that column is totally useless. In general you should not store data in a database that can easily be derived from existing values.

In your, adding the values of the three columns adds no (noticeable) overhead to a select statement, so there is no point in storing the total.

You should rather remove the column completely and then create a view doing this for you. Otherwise you need to update that column each time one of the other three columns are changed.

create view total_score
as
select id, name, class, social, science, math, 
       social + math + science as total
from student;

Plus: your name column should be defined as NOT NULL.

Related Solutions

PostgreSQL – How to Insert File Data into a Bytea Column

as superuser:

create or replace function bytea_import(p_path text, p_result out bytea) 
                   language plpgsql as $$
declare
  l_oid oid;
begin
  select lo_import(p_path) into l_oid;
  select lo_get(l_oid) INTO p_result;
  perform lo_unlink(l_oid);
end;$$;

lo_get was introduced in 9.4 so for older versions you would need:

create or replace function bytea_import(p_path text, p_result out bytea) 
                   language plpgsql as $$
declare
  l_oid oid;
  r record;
begin
  p_result := '';
  select lo_import(p_path) into l_oid;
  for r in ( select data 
             from pg_largeobject 
             where loid = l_oid 
             order by pageno ) loop
    p_result = p_result || r.data;
  end loop;
  perform lo_unlink(l_oid);
end;$$;

then:

insert into my_table(bytea_data) select bytea_import('/my/file.name');

Postgresql after ‘for each row’ triggers don’t actually execute after each row

Since the your INSERT and UPDATE are atomic (either all rows inserted/updated or none), you cannot catch a state when the count will not be 5 or 0. First the INSERT happens, then the trigger is fired for all the rows.

Best Answer

Related Solutions

PostgreSQL – How to Insert File Data into a Bytea Column

Postgresql after ‘for each row’ triggers don’t actually execute after each row

Related Question