as result of complicated select I have the_table with the structure like:
pl_id, date, value
I also have another table (let it calls the_source) where I have a list of all vailable pl_id. The idea is to extend the_table with values from the_source which doesn't exist. The problem is that some columns must be defined exactly as expected, another - can be any (let it be 0).
CREATE TABLE the_table (
"pl_id" INTEGER,
"date" DATE,
"value" INTEGER
);
INSERT INTO the_table
VALUES
('1', '2024-05-01', '10'),
('1', '2024-06-01', '11'),
('2', '2024-05-01', '12'),
('2', '2024-06-01', '13');
CREATE TABLE the_source (
"pl_id" INTEGER,
"msg" TEXT
);
INSERT INTO the_source
VALUES
('1', 'aaaaa'),
('2', 'bbbbb'),
('3', 'ccccc'),
('4', 'ddddd');
missed records can be found by select:
select * from the_source
where not exists (
select
*
from the_table
where the_table.pl_id = the_source.pl_id)
I would like to have the_final table with folling information:
('1', '2024-05-01', '10'),
('1', '2024-06-01', '11'),
('2', '2024-05-01', '12'),
('2', '2024-06-01', '13')
('3', '2024-05-01', '0'),
('3', '2024-06-01', '0'),
('4', '2024-05-01', '0'),
('4', '2024-06-01', '0');
Is it possible realize by SQL commands?
Yes, that is possible, the following shows one approach. (see demo)
with the_dates (adate) as
(select distinct date
from the_table
)
, msg_dates (pl_id,msg, adate) as
(select s.pl_id,s.msg, adate
from the_source s
cross join the_dates
)
select m.pl_id, m.adate date, coalesce(t.value,0) value
from msg_dates m
left join the_table t
on (t.pl_id, t.date) = (m.pl_id, m.adate)
order by m.pl_id, m.adate;
What it does:
First it generates a CTE the_dates which extracts each interested data from the_table. It then generate a second CTE msg_dates which combines the interested dates with each entry from the_source. Finally, the main query, joins the second CTE back to the table filling on the missing values.
NOTE: It is generally considered poor using data types and/or reserved words as column names. Date is a Postgres data type and a SQL Standard reserved word (see documentation), Value is also a SQL Standard reserved word. For demo I changed them to t... respectively.