how to embed a function to column in PostgreSQL

Question

I'm new to SQL and PostgreSQL and I'm trying to find a way to create a new table in database where a column will automatically calculate a pre-defined function with data from other columns.

Specifically I want a LOG table with TIME_IN | TIME_OUT | DURATION columns where the DURATION column shows the result of (=TIME_OUT - TIME_IN)

Of course this can be done with running a query but I'm trying to figure out if it's possible

to have it pre-defined so whenever running SELECT * FROM log the table will load up with duration value already calculated according to updated data in IN / OUT columns.

user330315 · Accepted Answer · 2021-08-01 14:55:58Z

2

This can be done using a generated column, e.g.:

create table log
( 
  id integer primary key generated always as identity,
  time_in timestamp,
  time_out timestamp,
  duration interval generated always as (time_out - time_in) stored
);

Another option would be to create a view:

create view log_with_duration
as
select id, 
       time_in, 
       time_out, 
       time_out - time_in as duration
from log;

answered Aug 1, 2021 at 14:55

user330315

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

how to embed a function to column in PostgreSQL

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related