Last active
November 3, 2021 02:21
-
-
Save hidayat365/0120299e5aa8e5f642bb0cb0ffd5618c to your computer and use it in GitHub Desktop.
Generated Column Example
This file contains bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
-- master table | |
create table transactions ( | |
id serial primary key, | |
code varchar(60) not null unique, | |
date integer not null, | |
value decimal(15,2) default 0, | |
remarks text | |
); | |
-- details table without generated column | |
create table transaction_details1 ( | |
id serial primary key, | |
transaction_id int not null, | |
item_id int not null, | |
quantity decimal(15,2) default 0, | |
unit_price decimal (15,2) default 0, | |
remarks text, | |
constraint fk_transaction_details1_transaction | |
foreign key (transaction_id) references transactions (id) | |
on update cascade on delete cascade | |
); | |
-- details table with generated column | |
create table transaction_details2 ( | |
id serial primary key, | |
transaction_id int not null, | |
item_id int not null, | |
quantity decimal(15,2) default 0, | |
unit_price decimal (15,2) default 0, | |
amount decimal(15,2) generated always as (quantity*unit_price) stored, | |
remarks text, | |
constraint fk_transaction_details2_transaction | |
foreign key (transaction_id) references transactions (id) | |
on update cascade on delete cascade | |
); | |
-- sample data for master table | |
insert into transactions(id, code, date) values (1, 'PO-01/2020', extract(epoch from timestamp '2020-05-01')); | |
insert into transactions(id, code, date) values (2, 'PO-02/2020', extract(epoch from timestamp '2020-05-05')); | |
insert into transactions(id, code, date) values (3, 'PO-03/2020', extract(epoch from timestamp '2020-05-08')); | |
-- sample data for details1 table | |
insert into transaction_details1(transaction_id, item_id, quantity, unit_price) values (1, 0, 15, 10000); | |
insert into transaction_details1(transaction_id, item_id, quantity, unit_price) values (1, 0, 25, 10000); | |
insert into transaction_details1(transaction_id, item_id, quantity, unit_price) values (2, 0, 35, 10000); | |
insert into transaction_details1(transaction_id, item_id, quantity, unit_price) values (3, 0, 45, 10000); | |
insert into transaction_details1(transaction_id, item_id, quantity, unit_price) values (3, 0, 55, 10000); | |
-- sample data for details2 table | |
insert into transaction_details2(transaction_id, item_id, quantity, unit_price) values (1, 0, 15, 10000); | |
insert into transaction_details2(transaction_id, item_id, quantity, unit_price) values (1, 0, 25, 10000); | |
insert into transaction_details2(transaction_id, item_id, quantity, unit_price) values (2, 0, 35, 10000); | |
insert into transaction_details2(transaction_id, item_id, quantity, unit_price) values (3, 0, 45, 10000); | |
insert into transaction_details2(transaction_id, item_id, quantity, unit_price) values (3, 0, 55, 10000); | |
-- no amount | |
select * | |
from transaction_details1; | |
-- calculate amount | |
select * | |
, quantity*unit_price as amount | |
from transaction_details1; | |
-- amount calculated already | |
select * | |
from transaction_details2; | |
Sign up for free
to join this conversation on GitHub.
Already have an account?
Sign in to comment