-- Compilateur PostgreSQL :
\echo ''
\echo '-------------------'
\echo '-- create tables --'
\echo '-------------------'
\echo ''
\echo '-- dim_period --'
create table if not exists dim_period (
id integer,
year smallint not null,
quarter varchar(2) not null,
month varchar(2) not null,
period varchar(7) not null,
lock boolean default '0',
created_by varchar(30),
creation_date timestamp,
modified_by varchar(30),
modification_date timestamp,
constraint pk_period primary key(id)
);
\echo '-- dim_currency --'
create table if not exists dim_currency (
id smallserial,
currency_name varchar(4) not null,
currency_descr varchar(30) not null,
created_by varchar(30),
active boolean default '1',
creation_date timestamp,
modified_by varchar(30),
modification_date timestamp,
constraint pk_currency primary key(id),
constraint un_currency_name unique (currency_name)
);
\echo '-- dim_category --'
create table if not exists dim_category (
id smallserial,
category_name varchar(30) not null,
active boolean default '1',
created_by varchar(30),
creation_date timestamp,
modified_by varchar(30),
modification_date timestamp,
constraint pk_category primary key(id),
constraint un_category_name unique (category_name)
);
\echo '-- dim_unit_of_measure --'
create table if not exists dim_unit_of_measure (
id serial,
unit_name varchar(30) not null,
active boolean default '1',
created_by varchar(30),
creation_date timestamp,
modified_by varchar(30),
modification_date timestamp,
constraint pk_unit_of_measure primary key(id),
constraint un_unit_of_measure_name unique (unit_name)
);
\echo '-- dim_commodity --'
create table if not exists dim_commodity (
id serial,
commodity_name varchar not null,
commodity_type varchar(30) not null,
category_id integer not null,
unit_of_measure_id integer not null,
active boolean default '1',
created_by varchar(30),
creation_date timestamp,
modified_by varchar(30),
modification_date timestamp,
constraint pk_commodity primary key(id),
constraint un_commodity_name_type unique (commodity_name, commodity_type)
);
\echo '-- dim_zone --'
create table if not exists dim_zone (
id serial,
zone_name varchar not null,
active boolean default '1',
created_by varchar(30),
creation_date timestamp,
modified_by varchar(30),
modification_date timestamp,
constraint pk_zone primary key(id),
constraint un_zone_name unique (zone_name)
);
\echo '-- dim_hedgebook --'
create table if not exists dim_hedgebook (
id serial,
hedgebook_name varchar not null,
active boolean default '1',
created_by varchar(30),
creation_date timestamp,
modified_by varchar(30),
modification_date timestamp,
constraint pk_hedgebook primary key(id),
constraint un_hedgebook_name unique (hedgebook_name)
);
\echo '-- fct_policy_proposal --'
create table if not exists fct_policy_proposal (
amount_forecast real,
amount_b2b real,
cover real,
summary_amount real,
number_of_weeks_cover integer,
date_covered_to timestamp,
period_id integer not null,
commodity_id integer not null,
active boolean default '1',
created_by varchar(30),
creation_date timestamp,
modified_by varchar(30),
modification_date timestamp,
constraint un_policy_proposal_period_id_commodity_id unique (period_id, commodity_id)
);
\echo '-- fct_ownership_price --'
create table if not exists fct_ownership_price (
price real,
coverage real,
period_id integer not null,
commodity_id integer not null,
zone_id integer not null,
hedgebook_id integer not null,
active boolean default '1',
created_by varchar(30),
creation_date timestamp,
modified_by varchar(30),
modification_date timestamp,
constraint un_ownership_price_period_id_commodity_id_zone_id_hedgebook_id unique (period_id, commodity_id, zone_id, hedgebook_id)
);
\echo '-- fct_full_year_price --'
create table if not exists fct_full_year_price (
price real,
type varchar(30),
period_id integer not null,
commodity_id integer not null,
active boolean default '1',
created_by varchar(30),
creation_date timestamp,
modified_by varchar(30),
modification_date timestamp,
constraint un_full_year_price_period_id_commodity_id unique (period_id, commodity_id)
);
\echo '-- fct_unit_of_measure --'
create table if not exists fct_unit_of_measure (
rate real,
period_id integer not null,
unit_of_measure_id integer not null,
active boolean default '1',
created_by varchar(30),
creation_date timestamp,
modified_by varchar(30),
modification_date timestamp,
constraint un_unit_of_measure_period_id_unit_of_measure_id unique (period_id, unit_of_measure_id)
);
\echo '-- fct_fx_rate --'
create table if not exists fct_fx_rate (
rate real,
type varchar(30),
period_id integer not null,
currency_from_id integer not null,
currency_to_id integer not null,
active boolean default '1',
created_by varchar(30),
creation_date timestamp,
modified_by varchar(30),
modification_date timestamp,
constraint un_fx_rate_period_id_currency_from_id_currency_to_id unique (period_id, currency_from_id, currency_to_id)
);
\echo '-- fct_volume --'
create table if not exists fct_volume (
volume real,
period_id integer not null,
zone_id integer not null,
active boolean default '1',
created_by varchar(30),
creation_date timestamp,
modified_by varchar(30),
modification_date timestamp,
constraint un_volume_period_id_zone_id unique (period_id, zone_id)
);
\echo
\echo '---------------------'
\echo '-- insert + update --'
\echo '---------------------'
\echo
\echo '-- shtMasterData - Period > dim_period --'
prepare insert_data_ps(integer[], integer[], text[], text[], text[], text) as
insert into dim_period (id, year, quarter, month, period, created_by, creation_date)
select unnest($1), unnest($2), unnest($3), unnest($4), unnest($5), unnest(array_fill($6::text, array[array_length($1,1)])), unnest(array_fill(now()::timestamp, array[array_length($1,1)]))
on conflict on constraint pk_period do nothing;
execute insert_data_ps(array[202101, 202102], array[2021, 2021], array['Q1', 'Q2'], array['01', '02'], array['2021.01', '2021.02'], 'lwatin');
execute insert_data_ps(array[202101, 202102], array[2021, 2021], array['Q1', 'Q1'], array['01', '02'], array['2021.01', '2021.02'], 'lwatin');
deallocate insert_data_ps;
\echo '-- ? - ? > dim_period --'
update dim_period
set lock = '1', modified_by = 'lwatin', modification_date = now()
where id = 202101;
\echo '-- shtMasterData - Currency > dim_currency --'
prepare insert_data_ps(text[], text[], text) as
insert into dim_currency (currency_name, currency_descr, created_by, creation_date)
select unnest($1), unnest($2), unnest(array_fill($3::text, array[array_length($1,1)])), unnest(array_fill(now()::timestamp, array[array_length($1,1)]))
on conflict on constraint un_currency_name do update set currency_descr = excluded.currency_descr, modified_by = $3, modification_date = now() where dim_currency.currency_descr <> excluded.currency_descr;
execute insert_data_ps(array['CHF', 'EUR'], array['Franc', 'Euro'], 'lwatin');
execute insert_data_ps(array['CHF'], array['Swiss Franc'], 'lwatin');
deallocate insert_data_ps;
/*
create or replace procedure insert_data_sp (a text array, b text array, user_name text)
LANGUAGE plpgsql
AS $$
declare
p1 text;
p2 text;
begin
p1 := concat('{', array_to_string(a, ','), '}');
p1 := concat('{', array_to_string(b, ','), '}');
end;
$$;
call insert_data_sp(array['CHF', 'EUR'], array['Franc', 'Euro'], 'lwatin');
call insert_data_sp(array['CHF', 'EUR'], array['Swiss Franc', 'Euro'], 'lwatin');
*/
\echo '-- ? - ? > dim_category --'
prepare insert_data_ps(text[], text) as
insert into dim_category (category_name, created_by, creation_date)
select unnest($1), unnest(array_fill($2::text, array[array_length($1,1)])), unnest(array_fill(now()::timestamp, array[array_length($1,1)]))
on conflict on constraint un_category_name do nothing;
execute insert_data_ps(array['Coffee', 'Cocoa'],'lwatin');
deallocate insert_data_ps;
\echo '-- ? - ? > dim_unit_of_measure --'
prepare insert_data_ps(text[], text) as
insert into dim_unit_of_measure (unit_name, created_by, creation_date)
select unnest($1), unnest(array_fill($2::text, array[array_length($1,1)])), unnest(array_fill(now()::timestamp, array[array_length($1,1)]))
on conflict on constraint un_unit_of_measure_name do nothing;
execute insert_data_ps(array['cts/lb', 'USD/MT'],'lwatin');
deallocate insert_data_ps;
\echo '-- shtMDCommodity - Commodity > dim_commodity --'
prepare insert_data_ps(text[], text[], integer[], integer[], text) as
insert into dim_commodity (commodity_name, commodity_type, category_id, unit_of_measure_id, created_by, creation_date)
select unnest($1), unnest($2), unnest($3), unnest($4), unnest(array_fill($5::text, array[array_length($1,1)])), unnest(array_fill(now()::timestamp, array[array_length($1,1)]))
on conflict on constraint un_commodity_name_type do update set category_id = excluded.category_id, unit_of_measure_id = excluded.unit_of_measure_id, modified_by = $5, modification_date = now() where dim_commodity.category_id <> excluded.category_id or dim_commodity.unit_of_measure_id <> excluded.unit_of_measure_id;
execute insert_data_ps( array['Coffee Arabicas', 'Coffee Robustas', 'Coffee Robustas'],
array['Futures', 'Futures', 'Differential'],
array[1, 1, 1],
array[1, 2, 2],
'lwatin');
execute insert_data_ps(array['Coffee Robustas', 'Coffee Robustas'],
array['Futures', 'Differential'],
array[1, 1],
array[2, 1],
'lwatin');
deallocate insert_data_ps;
\echo '-- ? - ? > dim_zone --'
prepare insert_data_ps(text[], text) as
insert into dim_zone (zone_name, created_by, creation_date)
select unnest($1), unnest(array_fill($2::text, array[array_length($1,1)])), unnest(array_fill(now()::timestamp, array[array_length($1,1)]))
on conflict on constraint un_zone_name do nothing;
execute insert_data_ps(array['EUROPE', 'LATAM'],'lwatin');
deallocate insert_data_ps;
\echo '-- ? - ? > dim_hedgebook --'
prepare insert_data_ps(text[], text) as
insert into dim_hedgebook (hedgebook_name, created_by, creation_date)
select unnest($1), unnest(array_fill($2::text, array[array_length($1,1)])), unnest(array_fill(now()::timestamp, array[array_length($1,1)]))
on conflict on constraint un_hedgebook_name do nothing;
execute insert_data_ps(array['MT01A-BF50 / US16', 'EN01A-NN / CA10'],'lwatin');
deallocate insert_data_ps;
\echo '-- shtOwnershipPrice - Ownership Price Input > fct_ownership_price --'
prepare insert_data_ps(real[], real[], integer[], integer[], integer[], integer[], text) as
insert into fct_ownership_price (price, coverage, period_id, commodity_id, zone_id, hedgebook_id, created_by, creation_date)
select unnest($1), unnest($2), unnest($3), unnest($4), unnest($5), unnest($6), unnest(array_fill($7::text, array[array_length($1,1)])), unnest(array_fill(now()::timestamp, array[array_length($1,1)]))
on conflict on constraint un_ownership_price_period_id_commodity_id_zone_id_hedgebook_id do update set price = excluded.price, coverage = excluded.coverage, modified_by = $7, modification_date = now() where fct_ownership_price.price <> excluded.price or fct_ownership_price.coverage <> excluded.coverage;
execute insert_data_ps( array[24216, 29148],
array[100, 100],
array[202101, 202102],
array[1, 2],
array[1, 2],
array[1, 2],
'lwatin');
deallocate insert_data_ps;
\echo '-- shtPolicyProposal - Policy Proposal > fct_policy_proposal --'
prepare insert_data_ps(real[], real[], real[], real[], integer[], timestamp[], integer[], integer[], text) as
insert into fct_policy_proposal (amount_forecast, amount_b2b, cover, summary_amount, number_of_weeks_cover, date_covered_to, period_id, commodity_id, created_by, creation_date)
select unnest($1), unnest($2), unnest($3), unnest($4), unnest($5), unnest($6), unnest($7), unnest($8), unnest(array_fill($9::text, array[array_length($1,1)])), unnest(array_fill(now()::timestamp, array[array_length($1,1)]))
on conflict on constraint un_policy_proposal_period_id_commodity_id do update set amount_forecast = excluded.amount_forecast, amount_b2b = excluded.amount_b2b, cover = excluded.cover, summary_amount = excluded.summary_amount, number_of_weeks_cover = coalesce(excluded.number_of_weeks_cover, fct_policy_proposal.number_of_weeks_cover), date_covered_to = excluded.date_covered_to, modified_by = $9, modification_date = now()
where fct_policy_proposal.amount_forecast <> excluded.amount_forecast or fct_policy_proposal.amount_b2b <> excluded.amount_b2b or fct_policy_proposal.cover <> excluded.cover or fct_policy_proposal.summary_amount <> excluded.summary_amount or coalesce(fct_policy_proposal.number_of_weeks_cover, 0) <> excluded.number_of_weeks_cover or fct_policy_proposal.date_covered_to <> excluded.date_covered_to;
execute insert_data_ps( array[150, 150, 150, 150],
array[200, 200, 200, 200],
array[100, 100, 100, 100],
array[197, 197, 197, 197],
array[12, null, 12, null],
array[timestamp '2021-03-01', null, timestamp '2021-03-01', null],
array[202101, 202102, 202101, 202102],
array[1, 1, 2, 2],
'lwatin');
execute insert_data_ps( array[150, 150, 150, 150],
array[200, 200, 200, 200],
array[100, 100, 100, 100],
array[197, 197, 197, 197],
array[null, 11, null, 11],
array[timestamp '2021-03-01', null, timestamp '2021-03-01', null],
array[202101, 202102, 202101, 202102],
array[1, 1, 2, 2],
'lwatin');
deallocate insert_data_ps;
\echo ''
\echo '------------'
\echo '-- select --'
\echo '------------'
\echo ''
\echo '-- shtMasterData - Period --'
select p.year as Year
,p.month as Month
,p.quarter as Quarter
,p.period as Period
,p.created_by as "Created By"
,p.creation_date as "Creation Date"
,p.modified_by as "Modified By"
,p.modification_date as "Modification Date"
from dim_period p;
\echo '-- shtMasterData - Currency --'
select c.currency_name as Name
,c.currency_descr as "Currency Name"
,c.created_by as "Created By"
,c.creation_date as "Creation Date"
,c.modified_by as "Modified By"
,c.modification_date as "Modification Date"
from dim_currency c
where c.active = '1';
\echo '-- shtMDCommodity - Commodity --'
select co.commodity_name as Name
,ca.category_name as "Category"
,co.commodity_type as "Commodity Type"
,co.created_by as "Created By"
,co.creation_date as "Creation Date"
,co.modified_by as "Modified By"
,co.modification_date as "Modification Date"
from dim_commodity co
inner join dim_category ca on ca.id = co.category_id
inner join dim_unit_of_measure um on um.id = co.unit_of_measure_id
where co.active = '1' and ca.active = '1' and um.active = '1';
\echo '-- shtOwnershipPrice - Ownership Price Input --'
select co.commodity_name as "Commodity Name"
,um.unit_name as "Unit Of Measure"
,op.price as Price
,op.coverage as Coverage
,p.period as Period
,op.created_by as "Created By"
,op.creation_date as "Creation Date"
,op.modified_by as "Modified By"
,op.modification_date as "Modification Date"
from fct_ownership_price op
inner join dim_commodity co on co.id = op.commodity_id
inner join dim_category ca on ca.id = co.category_id
inner join dim_unit_of_measure um on um.id = co.unit_of_measure_id
inner join dim_period p ON p.id = op.period_id
where p.year between 2021 and 2024 and op.active = '1' and co.active = '1' and ca.active = '1' and um.active = '1';
\echo '-- shtPolicyProposal - Policy Proposal --'
select co.commodity_name as "Commodity Name"
,ca.category_name as Category
,co.commodity_type as "Commodity Type"
,um.unit_name as "Unit Of Measure"
,p.period as Period
,pp.amount_forecast as Amount_FORECAST
,pp.amount_b2b as Amount_B2B
,pp.created_by as "Created By"
,pp.creation_date as "Creation Date"
,pp.modified_by as "Modified By"
,pp.modification_date as "Modification Date"
from fct_policy_proposal pp
inner join dim_commodity co on co.id = pp.commodity_id
inner join dim_category ca on ca.id = co.category_id
inner join dim_unit_of_measure um on um.id = co.unit_of_measure_id
inner join dim_period p ON p.id = pp.period_id
where p.year between 2021 and 2024 and pp.active = '1' and co.active = '1' and ca.active = '1' and um.active = '1';
\echo '-- shtCoverSummary - Cover Summary --'
select co.commodity_name as "Commodity Name"
,ca.category_name as Category
,co.commodity_type as "Commodity Type"
,p.period as Period
,pp.number_of_weeks_cover as "Number Of Weeks Covered"
,pp.date_covered_to as "Date Covered To"
,pp.cover as Cover
,pp.created_by as "Created By"
,pp.creation_date as "Creation Date"
,pp.modified_by as "Modified By"
,pp.modification_date as "Modification Date"
from fct_policy_proposal pp
inner join dim_commodity co on co.id = pp.commodity_id
inner join dim_category ca on ca.id = co.category_id
inner join dim_unit_of_measure um on um.id = co.unit_of_measure_id
inner join dim_period p ON p.id = pp.period_id
where p.year between 2021 and 2024 and pp.active = '1' and co.active = '1' and ca.active = '1' and um.active = '1';
\echo '-- shtPolicySummary - Policy Summary --'
select co.commodity_name as "Commodity Name"
,ca.category_name as Category
,co.commodity_type as "Commodity Type"
,p.period as Period
,pp.summary_amount as "Summary Amount"
,pp.created_by as "Created By"
,pp.creation_date as "Creation Date"
,pp.modified_by as "Modified By"
,pp.modification_date as "Modification Date"
from fct_policy_proposal pp
inner join dim_commodity co on co.id = pp.commodity_id
inner join dim_category ca on ca.id = co.category_id
inner join dim_unit_of_measure um on um.id = co.unit_of_measure_id
inner join dim_period p ON p.id = pp.period_id
where p.year between 2021 and 2024 and pp.active = '1' and co.active = '1' and ca.active = '1' and um.active = '1';
Write, Run & Share PostgreSQL queries online using OneCompiler's PostgreSQL online editor and compiler for free. It's one of the robust, feature-rich online editor and compiler for PostgreSQL. Getting started with the OneCompiler's PostgreSQL editor is really simple and pretty fast. The editor shows sample boilerplate code when you choose database as 'PostgreSQL' and start writing queries to learn and test online without worrying about tedious process of installation.
PostgreSQL is a open source relational database system and is also knows as Postgres.
CREATE command is used to create a table, schema or an index.
CREATE TABLE table_name (
column1 datatype,
column2 datatype,
....);
ALTER command is used to add, modify or delete columns or constraints from the database table.
ALTER TABLE Table_name ADD column_name datatype;
TRUNCATE command is used to delete the data present in the table but this will not delete the table.
TRUNCATE table table_name;
DROP command is used to delete the table along with its data.
DROP TABLE table_name;
RENAME command is used to rename the table name.
ALTER TABLE table_name1 RENAME to new_table_name1;
INSERT Statement is used to insert new records into the database table.
INSERT INTO table_name (column1, column2, column3, ...) VALUES (value1, value2, value3, ...);
Select statement is used to select data from database tables.
SELECT column1, column2, ...
FROM table_name;
UPDATE statement is used to modify the existing values of records present in the database table.
UPDATE table_name
SET column1 = value1, column2 = value2, ...
WHERE condition;
DELETE statement is used to delete the existing records present in the database table.
DELETE FROM table_name where condition;