CODEWITHSUNDEEP
sqlpostgresqlgenerated-columns
danb2000
danb2000

Reputation: 19

how to embed a function to column in PostgreSQL

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.

Upvotes: 0

Views: 227

Answers (1)

user330315
user330315

Reputation:

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;

Upvotes: 2

Related Questions